Converting database objects with generative AI - Amazon Database Migration Service
Scope of Generative AI conversion
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Converting database objects with generative AI

The DMS Schema Conversion with generative AI feature streamlines the database migration process by offering recommendations to help you convert previously unconverted code objects that typically require complex manual conversion. This feature is available for Oracle to PostgreSQL/Aurora PostgreSQL and SQL Server to PostgreSQL/Aurora PostgreSQL conversions. You can convert an entire database schema or individual database schema objects.

To convert your source database objects with generative AI, follow steps 1 to 6 in Converting database objects then continue with one of these two methods:

  • Method 1: From the Actions menu, select Convert. In the conversion dialog box that appears, enable the Convert schema with Generative AI option and click Convert.

  • Method 2: Click Convert schema with Generative AI in the top right corner. In the conversion dialog box, ensure the option is enabled and click Convert.

To manually adjust this setting at any time in DMS Schema Conversion console:

  • Navitage to the Settings tab.

  • In the Conversion settings section, enable the Generative AI option to approve the use of generative AI.

Note

Supported regions:

  • US East (N. Virginia)

  • US West (Oregon)

  • Europe (Frankfurt)

Supported conversion paths:

  • Oracle to Amazon RDS for PostgreSQL

  • Oracle to Amazon Aurora PostgreSQL

  • Microsoft SQL Server to Amazon RDS for PostgreSQL

  • Microsoft SQL Server to Amazon Aurora PostgreSQL

Scope of Generative AI conversion

Generative AI-assisted schema conversion focuses on specific SQL elements with designated action items. All other SQL elements are converted using default rule-based approaches. The SQL elements within the extended scope of Generative AI conversion include:

Conversion direction Action item Message Syntax element
Oracle to Amazon RDS for PostgreSQL and Oracle to Amazon Aurora PostgreSQL 5578 Amazon DMS Schema Conversion cannot convert the SELECT statement All occurrences except limitations
30415 Your MERGE statement contains a filtering condition in the WHERE clause that is based on a value in a target table column All occurrences except limitations
5591 Amazon DMS Schema Conversion cannot convert system objects All occurrences except limitations
5029 Amazon DMS Schema Conversion cannot convert the usage of objects with unsupported data types Usage of objects with the unsupported STANDARD.BFILE data type in function and procedure arguments.
5031 Amazon DMS Schema Conversion cannot convert CURSOR expressions All occurrences except limitations
5043 Amazon DMS Schema Conversion cannot convert hierarchical queries with an asterisk in the SELECT clause All occurrences except limitations
5073 PostgreSQL does not support hierarchical queries with pseudocolumns All occurrences except limitations
5102 PostgreSQL does not support MERGE statements All occurrences except limitations
5585 Amazon DMS Schema Conversion cannot convert outer joins into correlated subqueries All occurrences except limitations
5608 Amazon DMS Schema Conversion cannot convert UPDATE statements that have a subquery that returns multiple columns in the SET clause All occurrences except limitations
5619 Amazon DMS Schema Conversion cannot convert system objects All occurrences except limitations
5852 PostgreSQL supports only tables as a target in the MERGE statement. All occurrences except limitations
5853 Amazon DMS Schema Conversion supports only tables, views or sub-queries as a source in the USING clause of the MERGE statement All occurrences except limitations
5855 Your MERGE statement contains a filtering condition in the WHERE clause that is based on a value in a target table column All occurrences except limitations
9996 Internal Converter error occurred All occurrences except limitations
9993 Unable to transform statement due to references to unresolved object All occurrences except limitations
5598 PostgreSQL does not support ROWID All occurrences except limitations
5340 Amazon DMS Schema Conversion cannot convert functions All occurrences except limitations
5071 PostgreSQL does not support the INSERT statement for subqueries All occurrences except limitations
5068 PostgreSQL does not support the DELETE statement for subqueries All occurrences except limitations
5065 PostgreSQL does not support the UPDATE statement for subqueries All occurrences except limitations
5586 Amazon DMS Schema Conversion cannot convert queries with the NOCYCLE clause All occurrences except limitations
5351 Amazon DMS Schema Conversion cannot convert objects All occurrences except limitations
5077 PostgreSQL does not support the PIVOT clause for SELECT statements All occurrences except limitations
5126 PostgreSQL does not support MODEL statements All occurrences except limitations
5121 PostgreSQL does not support FORALL statements All occurrences except limitations
5141 Amazon DMS Schema Conversion does not support this type of conversion All occurrences except limitations
5142 Amazon DMS Schema Conversion cannot convert nested calls of the same method All occurrences except limitations
5245 PostgreSQL does not support views with nested table columns All occurrences except limitations
5500 Amazon DMS Schema Conversion cannot convert database mail sending All occurrences except limitations
5501 Amazon DMS Schema Conversion cannot convert scheduled jobs All occurrences except limitations
5645 PostgreSQL does not support BULK COLLECT INTO clauses for several object table targets. All occurrences except limitations
5665 PostgreSQL does not support the collection data type defined with PRAGMA AUTONOMOUS_TRANSACTION All occurrences except limitations
5637 PostgreSQL does not support bulk collect into VARRAY of VARRAY All occurrences except limitations
5594 Amazon DMS Schema Conversion cannot convert date time expressions All occurrences except limitations
5622 Amazon DMS Schema Conversion converts the dbms_transaction.local_transaction_id function with the parameter set to true All occurrences except limitations
5643 PostgreSQL does not support BULK COLLECT INTO clauses for multilevel collection types in SELECT statements. All occurrences except limitations
5649 PostgreSQL does not support multiset operators for multilevel collection types. All occurrences except limitations
5651 Amazon DMS Schema Conversion cannot convert pipelined table functions All occurrences except limitations
5793 Amazon DMS Schema Conversion creates the queue with the GRANT ALL option All occurrences except limitations
5794 PostgreSQL sets the queue mode to ENABLE by default All occurrences except limitations
5795 Amazon Simple Queue Service does not support queues in the DISABLE mode All occurrences except limitations
Microsoft SQL Server to Amazon RDS for PostgreSQL and Microsoft SQL Server to Amazon Aurora PostgreSQL 7610 Amazon DMS Schema Conversion cannot convert unsupported DDL statements All occurrences except limitations
7622 Amazon DMS Schema Conversion cannot convert the DELETE statement using complex inline functions All occurrences except limitations
7624 Amazon DMS Schema Conversion cannot convert the DELETE statement from an inline function for tables without primary keys All occurrences except limitations
7626 Amazon DMS Schema Conversion cannot convert the UPDATE statement using complex inline functions All occurrences except limitations
7627 Amazon DMS Schema Conversion cannot convert this syntax element All occurrences except limitations
7628 PostgreSQL does not support GOTO statements All occurrences except limitations
7637 PostgreSQL does not support global cursors All occurrences except limitations
7639 PostgreSQL does not support dynamic cursors All occurrences except limitations
7644 PostgreSQL does not support the %s clause All occurrences except limitations
7645 PostgreSQL does not support running pass-through commands on linked servers All occurrences except limitations
7653 PostgreSQL does not support GROUP BY ROLLUP clauses All occurrences except limitations
7654 PostgreSQL does not support GROUP BY CUBE clauses All occurrences except limitations
7655 PostgreSQL does not support GROUP BY GROUPING SETS clauses All occurrences except limitations
7672 PostgreSQL does not support EXECUTE statements that run a character string All occurrences except limitations
7683 MERGE is not supported if the target is a view, a materialized view, or an external table All occurrences except limitations
7687 PostgreSQL does not support CONTAINS predicates All occurrences except limitations
7688 PostgreSQL does not support FREETEXT predicates All occurrences except limitations
7691 PostgreSQL does not support the WAITFOR TIME feature All occurrences except limitations
7695 PostgreSQL does not support the call of a procedure as a variable All occurrences except limitations
7696 Amazon DMS Schema Conversion cannot convert the object because the %s object is not created All occurrences except limitations
7708 Amazon DMS Schema Conversion cannot convert the usage of the unsupported %s data type All occurrences except limitations
7709 Amazon DMS Schema Conversion cannot convert the usage of a symmetric key All occurrences except limitations
7773 Amazon DMS Schema Conversion cannot convert arithmetic operations with dates All occurrences except limitations
7774 Amazon DMS Schema Conversion cannot convert arithmetic operations with mixed types of operands All occurrences except limitations
7794 PostgreSQL does not support user-defined data types All occurrences except limitations
7796 PostgreSQL does not support TOP clauses in UPDATE statements All occurrences except limitations
7797 PostgreSQL does not support the DELETED column prefix for OUTPUT clauses in UPDATE statements All occurrences except limitations
7798 PostgreSQL does not support TOP clauses in DELETE statements All occurrences except limitations
7799 PostgreSQL does not support TOP clauses in INSERT operators All occurrences except limitations
7804 PostgreSQL does not support the bitwise exclusive OR operator All occurrences except limitations
7805 PostgreSQL does not support the !< All occurrences except limitations
7806 PostgreSQL does not support the !> (not greater than) operator All occurrences except limitations
7811 PostgreSQL does not support the %s function. Amazon DMS Schema Conversion skips this unsupported function in the converted code All occurrences except limitations, excluding DDL
7816 PostgreSQL does not support methods for the XML data type All occurrences except limitations
7817 PostgreSQL does not support the FOR XML PATH option in SQL queries All occurrences except limitations
7818 PostgreSQL does not support arithmetic operations with binary data types All occurrences except limitations
7819 PostgreSQL does not support INSERT...EXECUTE statements All occurrences except limitations
7820 PostgreSQL does not support the VALUE() method All occurrences except limitations
7824 RECURSIVE CTE is not supported for MERGE statement All occurrences except limitations
7829 Amazon DMS Schema Conversion cannot convert variable assignments with UPDATE statements All occurrences except limitations
7830 Amazon DMS Schema Conversion cannot convert arithmetic operations with the CASE operand All occurrences except limitations
7832 Amazon DMS Schema Conversion cannot convert INSTEAD OF triggers on views All occurrences except limitations
7833 Amazon DMS Schema Conversion cannot convert the @@rowcount function in the current context All occurrences except limitations
7836 PostgreSQL does not support write operations for binary data All occurrences except limitations
7840 Amazon DMS Schema Conversion cannot convert Database Console Command statements All occurrences except limitations
7904 Amazon DMS Schema Conversion cannot convert the %s system object All occurrences except limitations
7905 PostgreSQL does not support PIVOT clauses for SELECT statements All occurrences except limitations
7906 PostgreSQL does not support UNPIVOT clauses for SELECT statements All occurrences except limitations
7909 Amazon DMS Schema Conversion cannot convert UPDATE(column) OR COLUMNS_UPDATED statements All occurrences except limitations
7916 Amazon DMS Schema Conversion cannot emulate the MERGE statement using the INSERT ON CONFLICT statement All occurrences except limitations
7917 PostgreSQL does not support the %s function All occurrences except limitations
7918 PostgreSQL does not support table-valued functions All occurrences except limitations
7919 PostgreSQL does not support FOR XML with the %s directive All occurrences except limitations
7920 PostgreSQL does not support EXPLICIT mode with FOR XML All occurrences except limitations
7925 PostgreSQL does not support the percent character for OPENXML flags All occurrences except limitations
7927 PostgreSQL does not support OUTER joins for self-referenced tables without a primary key All occurrences except limitations
7929 Amazon DMS Schema Conversion cannot convert INSERT from EXEC statements All occurrences except limitations
7939 Amazon DMS Schema Conversion cannot convert the %s JSON system function All occurrences except limitations
7940 Amazon DMS Schema Conversion cannot convert OPENJSON system table-valued functions All occurrences except limitations
7941 Amazon DMS Schema Conversion cannot convert all open datasets because you have multiple open datasets All occurrences except limitations
9996 Internal Converter error occurred All occurrences except limitations

Limitations

The Converting database objects with generative AI feature has the following limitations:

  • Database endpoints supporting generative AI conversion are not visible in the Amazon Console. You can view them only by exporting the assessment report as a PDF or CSV file.

  • As a probabilistic system, generative AI-assisted Schema Conversion may not achieve 100 percent accuracy in all conversions. It can also produce different results for the same SQL statements over a period of time. You must review and validate all conversion outputs.

  • Generative AI conversion is not supported for:

    • DEFAULT constraint in a table

    • DEFAULT value for a function or procedure parameter

    • TRIGGER

    • COLUMN DATA TYPE

    • Dynamic SQL

    • INDEX

    • CONSTRAINT

  • If the source statement is converted with multiple action items and at least one action item is processed using generative AI, then all Action Items are replaced by one action item 5444 on a target for Oracle and 7744 for Microsoft SQL Server. The action item 9997 is an exception that is saved after processed using generative AI.

Warning

Conversion using generative AI takes longer than basic conversion.

Every Amazon account have a per-minute quota limiting the number of SQL statements that can be converted using generative AI. Statements exceeding this limit are queued for processing in subsequent minutes. The quota is as follows:

Region SQL Statements per Amazon account per minute
US East (N. Virginia) Up to 20 statements
US West (Oregon) Up to 100 statements
Europe (Frankfurt) Up to 10 statements