I tried the new feature of DMS Schema Conversion "Automation of AI agents using AWS MCP server"

Introduction

AWS DMS Schema Conversion includes an additional AI-powered conversion feature on top of its rule-based conversion engine. Furthermore, by combining Kiro CLI with AWS MCP Server, DMS operations themselves can be automated using natural language.

In this article, we tested how far a schema containing Oracle-specific syntax could be migrated to PostgreSQL using three stages: "rule-based conversion," "generative AI conversion," and "Kiro code generation follow-up."

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

Since Oracle SE2 does not support Partitioning (PARTITION BY) and VPD (DBMS_RLS), these are excluded from the verification scope. COMPOUND TRIGGER, DBMS_SCHEDULER, SDO_GEOMETRY, materialized views, and other features available in SE2 were included in the verification scope.

Oracle Schema Configuration

A MIGRATION_TEST schema was created for testing, with the following objects loaded.

Conversion was executed 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

Rule-based conversion and generative AI conversion were each executed in DMS Schema Conversion. The following shows the results of comparing SQL exported to S3 by switching EnableGenAiConversion between false/true.

• Direct DMS application: 8/13 succeeded, 5/13 failed (COMPOUND TRIGGER excluded as no DMS output, including 1 cascading failure)
• Kiro follow-up application: 14/14 syntax application succeeded (1 iteration)

※ The 13 objects for direct DMS application consist of 4 tables (audit_log, orders, products, mv_monthly_sales) + 2 INDEXes + 3 PK constraints + 4 PROC/FUNCTIONs (logical object units from DMS output). COMPOUND TRIGGER is excluded from direct application due to non-support. The 14 objects for Kiro follow-up include the DMS output combined with objects added/modified by Kiro (such as the archive_export table and trigger functions) as application units.

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

The only difference between rule-based and generative AI output was in the DBMS_SCHEDULER.CREATE_JOB call within 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 comments out the relevant code and proposes it as action item 5501 (CRITICAL), suggesting to "rewrite 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 generates code with syntax conversion applied without commenting out. However, since 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 this is generative AI output and requires human review.

Within the scope of this verification, generative AI differences were limited to the DBMS_SCHEDULER.CREATE_JOB part that was determined unconvertible by rule-based conversion, and no output differences were confirmed for other objects.

Assessment Report Action Items

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

Applying DMS Conversion Results Directly to PostgreSQL

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

Success (8 cases): Table creation for audit_log, orders, mv_monthly_sales; idx_orders_date; 2 PK constraints; pkg_notification$send; place_order (no syntax error since DBMS_SCHEDULER portion is commented out; full functionality reproduction addressed in Kiro follow-up below)

Failure (5 cases):

Some of the DMS conversion results in this verification contained SQL referencing the aws_oracle_ext and aws_oracle_data schemas. Since these schemas do not exist in plain PostgreSQL, DBMS_LOB and UTL_FILE wrapper calls result in errors.

Kiro Follow-up for Unconvertible Parts

We requested Kiro to generate code for the parts that could not be converted by DMS, as well as parts that depended on aws_oracle_ext / aws_oracle_data schemas and would not work on plain PostgreSQL.

DBMS_SCHEDULER → pg_cron (self-deletion pattern)

Oracle's DBMS_SCHEDULER.CREATE_JOB creates a one-time delayed job (to execute 5 minutes later). Since pg_cron is fundamentally based on cron expression periodic execution, it was reproduced using the pattern of "check every minute → process on the first execution opportunity after the specified time → self-deletion." This means that unlike Oracle's strict 5-minute delay, a delay of up to approximately 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 writing to a table using INSERT ... SELECT and externalizing to CSV using COPY TO. Since Aurora PostgreSQL cannot write directly to the host filesystem, this serves as a common implementation for Aurora environments.

-- Archive output destination table as UTL_FILE alternative
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 to WITH RECURSIVE, but in a form that depends on aws_oracle_ext.ADD_MONTHS and aws_oracle_data.TCursorAttributes. Kiro 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 contains only the skeleton of monthly series generation using generate_series, and does not include customer-specific aggregation logic using p_customer_id (the purpose is to confirm syntax conversion).

COMPOUND TRIGGER → Row trigger

Oracle's COMPOUND TRIGGER accumulates changed rows in an array with AFTER EACH ROW and performs a bulk INSERT with AFTER STATEMENT. Since DMS Schema Conversion does not support COMPOUND TRIGGER (action item 5242), it was left unconverted and reimplemented as 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

All 14 objects including the above code were applied to PostgreSQL 16 on EC2, with 14/14 syntax applications succeeding (1 iteration). "Success" here means that CREATE passed on PostgreSQL 16, and does not guarantee functional equivalence with the original Oracle code. As functional testing, INSERT into the orders table → trigger firing → audit_log recording was confirmed. Actual firing by pg_cron after 5 minutes was not confirmed in this verification.

Operations with Kiro + AWS MCP Server

AWS MCP Server was connected to Kiro CLI, and DMS Schema Conversion operations were executed using natural language. Below are representative operation flows.

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 sequence.

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

A total of 19 API calls were executed across 4 prompts (schema confirmation → rule-based conversion → generative AI conversion → assessment generation).

During the process, an InvalidParameterValueException occurred due to using an incorrect parameter (schema-conversion-operation-id) as the waiter's filter name. Kiro inferred the correct filter name (request-id) from the error message and self-corrected. The same type of error did not recur afterward.

MCP is an automation layer for API calls, and the conversion engine itself is the same. Within the scope of this verification, no differences in conversion results were confirmed between MCP-based and manual operations when executed with the same conversion settings and input schema.

Summary

In this verification, we used DMS Schema Conversion's rule-based conversion and generative AI conversion to convert a schema containing Oracle-specific syntax to PostgreSQL. The main remaining challenges after DMS + generative AI conversion fell into: those requiring additional extensions (PostGIS, pg_cron), those requiring design changes (UTL_FILE → table output + COPY), and those where performance characteristics cannot be simply reproduced (COMPOUND TRIGGER bulk processing).

In the Kiro follow-up, all 14 application units—combining DMS conversion results with code generated and corrected by Kiro—passed PostgreSQL 16 syntax validation in 1 iteration. However, success here refers to CREATE statements passing, and does not guarantee functional equivalence or performance characteristics with the original Oracle code. Actual design decisions and functional verification remain as areas for human responsibility.

In this verification, generative AI conversion differences were confirmed in only one location: DBMS_SCHEDULER.CREATE_JOB. This is partly because the verification schema contained many elements excluded from generative AI conversion targets, such as COMPOUND TRIGGER and INDEXes. For schemas with more applicable elements, there may be more locations where generative AI intervenes.

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

Orchestration via Kiro + AWS MCP Server does not change the conversion engine itself, as long as DMS Schema Conversion is executed with the same settings. In this verification, 19 API calls were completed with 4 natural language prompts, and intermediate API parameter errors were also self-corrected, confirming its effectiveness in terms of DMS operation efficiency.

Reference Links

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