I Tried Out the New DMS Schema Conversion Feature: "AI Agent Automation Using the AWS MCP Server"

Introduction

In addition to its rule-based conversion engine, AWS DMS Schema Conversion has a generative AI feature for additional conversion. Furthermore, by combining Kiro CLI with the AWS MCP Server, you can automate DMS operations themselves using natural language.

In this article, targeting a schema that contains Oracle-specific syntax, I verified how far it can be migrated to PostgreSQL in three stages: "rule-based conversion," "generative AI conversion," and "follow-up code generation with Kiro."

https://docs.aws.amazon.com/dms/latest/userguide/sc-genai-agents.html

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html

Verification Details

Verification Environment

Because Partitioning (PARTITION BY) and VPD (DBMS_RLS) are not available in Oracle SE2, they are excluded from the verification. I included COMPOUND TRIGGER, DBMS_SCHEDULER, SDO_GEOMETRY, materialized views, and so on, which are available in SE2.

Oracle Schema Composition

I created a MIGRATION_TEST schema for verification and loaded the following objects.

I ran the conversion after confirming that all 25 objects (5 tables, 2 packages + 2 bodies, 1 MV, 1 trigger, 6 indexes, 5 LOBs, 3 sequences) were VALID.

Conversion Results by Object

I ran rule-based conversion and generative AI conversion separately with DMS Schema Conversion. The following are the results of switching EnableGenAiConversion between false/true and comparing the SQL exported to S3.

• Direct application of DMS output: 8/13 succeeded, 5/13 failed (COMPOUND TRIGGER is excluded because DMS produced no output for it; one of the failures is a cascading failure)
• Kiro follow-up application: Syntax application succeeded 14/14 (1 iteration)

※ The 13 items for direct application of DMS output are 4 tables (audit_log, orders, products, mv_monthly_sales) + 2 INDEXes + 3 PK constraints + 4 PROC/FUNC (counted per logical object in the DMS output). COMPOUND TRIGGER is not supported and is therefore excluded from direct application. The 14 items for Kiro follow-up are the application units consisting of the DMS output plus objects added or modified by Kiro (the archive_export table, the trigger function, etc.).

Where Generative AI Conversion Produced Differences (for This Verification Schema)

The only place where the output differed between rule-based and generative AI conversion was the DBMS_SCHEDULER.CREATE_JOB call in pkg_order_mgmt$place_order.

Rule-based conversion output:

/*
[5501 - Severity CRITICAL - PostgreSQL doesn't support functionality similar to the
SYS.DBMS_SCHEDULER.CREATE_JOB(...) module.
Revise your converted code to use AWS Lambda with scheduled events.]
DBMS_SCHEDULER.CREATE_JOB(
    job_name   => v_job_name,
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN migration_test.pkg_notification.send(' || p_order_id || '); END;',
    start_date => SYSTIMESTAMP + INTERVAL '5' MINUTE,
    enabled    => TRUE
)
*/

The rule-based engine commented out the relevant code and proposed, as action item 5501 (CRITICAL), to "rewrite it using AWS Lambda with scheduled events."

Generative AI conversion output:

/* [5444 - Severity LOW - This conversion uses machine learning models that generate
predictions based on patterns in data. Machine learning output is probabilistic and
should be reviewed for accuracy, including human evaluation, as needed for your use case.] */
/* vvv ---- Beginning of statement generated using GenAI. ---- vvv */
CALL DBMS_SCHEDULER.CREATE_JOB(
    job_name => v_job_name,
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN migration_test.pkg_notification.send(' || p_order_id || '); END;',
    start_date => aws_oracle_ext.systimestamp() + '5 MINUTE'::INTERVAL,
    enabled => TRUE
);
/* ^^^ ---- End of statement generated using GenAI. ---- ^^^ */

The generative AI produced code with syntax conversion applied rather than commenting it out. However, because the DBMS_SCHEDULER package does not exist in PostgreSQL, this code cannot be executed as is. The original 5501 (CRITICAL) was replaced by 5444 (LOW), which is an action item indicating that the output was generated by generative AI and that human review is required.

Within the scope of this verification, differences from generative AI were limited to the DBMS_SCHEDULER.CREATE_JOB part that the rule-based engine judged not convertible, and no output differences were confirmed in other objects.

Action Items in the Assessment Report

The following action items remained in the assessment report with generative AI conversion enabled.

Directly Applying DMS Conversion Results to PostgreSQL

When I executed the SQL output by DMS rule-based conversion directly on PostgreSQL 16 on EC2, 8 of 13 items succeeded. The DMS Schema Conversion target version was Aurora PostgreSQL 15, and the following are the syntax verification results on PostgreSQL 16.

Succeeded (8 items): Table creation for audit_log, orders, and mv_monthly_sales; idx_orders_date; 2 PK constraints; pkg_notification$send; place_order (the DBMS_SCHEDULER part is commented out, so there is no syntax error; full functional reproduction is handled in the Kiro follow-up described later)

Failed (5 items):

Some of the DMS conversion results this time contained SQL that references the aws_oracle_ext and aws_oracle_data schemas. These schemas do not exist in plain PostgreSQL, so the wrapper calls for DBMS_LOB and UTL_FILE result in errors.

Kiro Follow-up for Non-Convertible Parts

I asked Kiro to generate code for the parts that DMS could not convert, as well as the parts that depend on the aws_oracle_ext / aws_oracle_data schemas and therefore do not work on plain PostgreSQL.

DBMS_SCHEDULER → pg_cron (self-deleting pattern)

Oracle's DBMS_SCHEDULER.CREATE_JOB creates a one-time delayed job (executed 5 minutes later). Since pg_cron is fundamentally based on periodic execution with cron expressions, I reproduced it with the pattern "check every minute → process at the first trigger opportunity after the specified time → delete itself." As a result, unlike Oracle's exact 5 minutes later, a delay of up to about 1 minute may occur.

CREATE OR REPLACE PROCEDURE migration_test.pkg_order_mgmt$place_order(
    IN p_customer_id DOUBLE PRECISION,
    IN p_total DOUBLE PRECISION,
    OUT p_order_id DOUBLE PRECISION
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_job_name TEXT;
    v_fire_at TIMESTAMP;
BEGIN
    INSERT INTO migration_test.orders (customer_id, order_date, total_amount)
    VALUES (p_customer_id, CURRENT_TIMESTAMP::TIMESTAMP(0), p_total)
    RETURNING order_id INTO p_order_id;

    v_job_name := 'notify_' || p_order_id::BIGINT;
    v_fire_at := CURRENT_TIMESTAMP + INTERVAL '5 minutes';

    PERFORM cron.schedule(
        v_job_name,
        '* * * * *',
        format(
            'DO $j$ BEGIN IF CURRENT_TIMESTAMP >= %L::TIMESTAMP THEN '
            || 'CALL migration_test.pkg_notification$send(%s); '
            || 'PERFORM cron.unschedule(%L); END IF; END $j$;',
            v_fire_at,
            p_order_id,
            v_job_name
        )
    );
END;
$$;

DBMS_LOB + UTL_FILE → Table output + COPY

Oracle's CSV string assembly using DBMS_LOB.CREATETEMPORARY / WRITEAPPEND and file output using UTL_FILE were replaced, in PostgreSQL, with a method that writes to a table using INSERT ... SELECT and then converts it to CSV externally with COPY TO. Because Aurora PostgreSQL cannot write directly to the host file system, this is an implementation common to Aurora environments.

-- Archive output destination table as a UTL_FILE replacement
CREATE TABLE migration_test.archive_export(
    order_id BIGINT,
    total_amount DOUBLE PRECISION,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE PROCEDURE migration_test.pkg_order_mgmt$archive_old_orders(
    IN p_before_date TIMESTAMP WITHOUT TIME ZONE
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO migration_test.archive_export (order_id, total_amount)
    SELECT order_id, total_amount
    FROM migration_test.orders
    WHERE order_date < p_before_date;

    DELETE FROM migration_test.orders
    WHERE order_date < p_before_date;
END;
$$;

CONNECT BY LEVEL → generate_series

DMS converted CONNECT BY LEVEL into WITH RECURSIVE. However, the result depends on aws_oracle_ext.ADD_MONTHS and aws_oracle_data.TCursorAttributes. With Kiro, I reimplemented it using PostgreSQL-native generate_series + INTERVAL arithmetic.

CREATE OR REPLACE FUNCTION migration_test.pkg_order_mgmt$get_order_summary(
    IN p_customer_id DOUBLE PRECISION
)
RETURNS REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE
    v_cursor REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT
            gs AS month_offset,
            date_trunc('month', CURRENT_DATE) - (gs - 1) * INTERVAL '1 month' AS target_month
        FROM generate_series(1, 12) AS gs;
    RETURN v_cursor;
END;
$$;

This code is only the skeleton of monthly series generation using generate_series and does not include per-customer aggregation logic using p_customer_id (the purpose is to confirm syntax conversion).

COMPOUND TRIGGER → Row trigger

Oracle's COMPOUND TRIGGER has a structure that accumulates changed rows into an array in AFTER EACH ROW and bulk-INSERTs them in AFTER STATEMENT. DMS Schema Conversion does not support COMPOUND TRIGGER (action item 5242), so it was left unconverted, and I reimplemented it with a PostgreSQL row trigger.

CREATE OR REPLACE FUNCTION migration_test.trg_orders_audit_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO migration_test.audit_log (operation, order_id, event_ts)
        VALUES ('DELETE', OLD.order_id, CURRENT_TIMESTAMP);
        RETURN OLD;
    ELSE
        INSERT INTO migration_test.audit_log (operation, order_id, event_ts)
        VALUES (TG_OP, NEW.order_id, CURRENT_TIMESTAMP);
        RETURN NEW;
    END IF;
END;
$$;

CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON migration_test.orders
FOR EACH ROW
EXECUTE FUNCTION migration_test.trg_orders_audit_fn();

Kiro Follow-up Application Results

I applied all 14 objects, including the code above, to PostgreSQL 16 on EC2, and syntax application succeeded for 14/14 (1 iteration). Here, "success" means that CREATE passed on PostgreSQL 16, and it does not guarantee functional equivalence with the original Oracle code. As a functional test, I confirmed INSERT into the orders table → trigger firing → audit_log recording. The actual firing 5 minutes later by pg_cron was not confirmed in this verification.

Operating via Kiro + AWS MCP Server

I connected the AWS MCP Server to Kiro CLI and performed DMS Schema Conversion operations using natural language. The following is a representative operation flow.

https://dev.classmethod.jp/articles/aws-mcp-server-oauth-kiro-cli/

Example input prompt:

Convert the MIGRATION_TEST schema to Aurora PostgreSQL using rule-based conversion only (GenAI disabled). The migration project is oracle-to-pg-schema-conversion in us-west-2. Export the converted SQL scripts to S3.

In response to this prompt, Kiro called the following APIs in order.

1. describe-conversion-configuration — Check the current settings
2. modify-conversion-configuration — Change to EnableGenAiConversion=false
3. start-metadata-model-conversion — Run the conversion
4. wait metadata-model-converted — Wait for completion
5. start-metadata-model-export-as-script — Export to S3
6. wait metadata-model-exported-as-script — Wait for export completion
7. describe-metadata-model-exports-as-script — Check the S3 key

Across all 4 prompts (schema check → rule-based conversion → generative AI conversion → assessment generation), a total of 19 API calls were executed.

Partway through, an incorrect parameter (schema-conversion-operation-id) was used as the waiter's filter name, causing an InvalidParameterValueException. Kiro inferred the correct filter name (request-id) from the error message and self-corrected. The same kind of error did not recur afterward.

MCP is an automation layer for API calls, and the conversion engine itself is the same. In this verification, within the range executed with the same conversion settings and input schema, no differences in conversion results were confirmed between operation via MCP and manual operation.

Summary

In this verification, I used rule-based conversion and generative AI conversion of DMS Schema Conversion to convert a schema containing Oracle-specific syntax for PostgreSQL. The main issues remaining after conversion with DMS + generative AI fell into those requiring the addition of extensions (PostGIS, pg_cron), those requiring design changes (UTL_FILE → table output + COPY), and those whose performance characteristics cannot be simply reproduced (bulk processing of COMPOUND TRIGGER).

In the Kiro follow-up, all 14 application units—the DMS conversion results plus the code generated and corrected by Kiro—were able to pass PostgreSQL 16 syntax verification in one iteration. However, the success here means that the CREATE statements passed, and it does not guarantee functional equivalence with the original Oracle code or its performance characteristics. Actual design decisions and functional verification remain the domain of humans.

The only place where a generative AI conversion difference was confirmed in this verification was a single location, DBMS_SCHEDULER.CREATE_JOB. One likely reason is that this verification schema contained many elements, such as COMPOUND TRIGGER and INDEX, that are outside the scope of generative AI conversion. In schemas with many target elements, the number of places where generative AI intervenes may increase.

Where generative AI intervened, rather than leaving the code commented out as in rule-based conversion, it output code that attempted syntax conversion for PostgreSQL. On the other hand, the output CALL DBMS_SCHEDULER.CREATE_JOB(...) does not work on PostgreSQL as is, and as indicated by action item 5444, human review and redesign are required.

Orchestration with Kiro + AWS MCP Server does not change the conversion engine itself as long as DMS Schema Conversion is run with the same settings. In this verification, 19 API calls were completed with 4 natural-language prompts, and an API parameter error along the way was self-corrected, so I was able to confirm its effectiveness in making DMS operations more efficient.

References

https://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-oracle-postgresql.html

https://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-convert.databaseobjects.html