# Using DMS Schema Conversion
DMS Schema Conversion converts your existing database schemas and a majority of the database code objects to a format compatible with the target database.
DMS Schema Conversion automates much of the process of converting your online transaction processing (OLTP) database schemas to Amazon RDS for MySQL or RDS for PostgreSQL. The source and target database engines contain many different features and capabilities, and DMS Schema Conversion attempts to create an equivalent schema wherever possible. For database objects where direct conversion isn't possible, DMS Schema Conversion provides a list of actions for you to take.
To convert your database schema, use the following process:
+ Before you convert your database schemas, set up transformation rules that change the names of your database objects during conversion.
+ Create a database migration assessment report to estimate the complexity of the migration. This report provides details about the schema elements that DMS Schema Conversion can't convert automatically.
+ Convert your source database storage and code objects. DMS Schema Conversion creates a local version of the converted database objects. You can access these converted objects in your migration project.
+ Save the converted code to SQL files to review, edit, or address conversion action items. Optionally, apply the converted code directly to your target database.
**Topics**
+ [Setting up transformation rules in DMS Schema Conversion](schema-conversion-transformation-rules.md)
+ [Converting database schemas in DMS Schema Conversion: step-by-step guide](schema-conversion-convert.md)
+ [Converting database objects with generative AI](schema-conversion-convert.databaseobjects.md)
+ [Specifying schema conversion settings for migration projects](schema-conversion-settings.md)
+ [Refreshing your database schemas in DMS Schema Conversion](schema-conversion-refresh.md)
+ [Saving and applying your converted code in DMS Schema Conversion](schema-conversion-save-apply.md)
+ [Converting embedded SQL in Java applications](schema-conversion-embedded-sql.md)
# Setting up transformation rules in DMS Schema Conversion
Before you convert your database schema with DMS Schema Conversion, you can set up transformation rules. *Transformation rules* can do such things as change an object name to lowercase or uppercase, add or remove a prefix or suffix, and rename objects. For example, suppose that you have a set of tables in your source schema named `test_TABLE_NAME`. You can set up a rule that changes the prefix `test_` to the prefix `demo_` in the target schema.
You can create transformation rules that perform the following tasks:
+ Add, remove, or replace a prefix
+ Add, remove, or replace a suffix
+ Change the data type of a column
+ Change the object name to lowercase or uppercase
+ Rename objects
You can create transformation rules for the following objects:
+ Schema
+ Table
+ Column
## Creating transformation rules
DMS Schema Conversion stores transformation rules as part of your migration project. You can set up transformation rules when you create your migration project, or edit them later.
You can add multiple transformation rules in your project. DMS Schema Conversion applies transformation rules during conversion in the same order as you added them.
**To create transformation rules**
1. On the **Create migration project** page, choose **Add transformation rule**. For more information, see [ Creating migration projects](migration-projects-create.md).
1. For **Rule target**, choose the type of database objects to which this rule applies.
1. For **Source schema**, choose **Enter a schema**. Then, enter the names of your source schemas, tables, and columns to which this rule applies. You can enter an exact name to select one object, or you can enter a pattern to select multiple objects. Use the percent (%) as a wildcard to replace any number of any symbols in the database object name.
1. For **Action**, choose the task to perform.
1. Depending on the rule type, enter one or two additional values. For example, to rename an object, enter the new name of the object. To replace a prefix, enter the old prefix and the new prefix.
1. Choose **Add transformation rule** to add another transformation rule.
After you are done adding rules, choose **Create migration project**.
To duplicate an existing transformation rule, choose **Duplicate**. To edit an existing transformation rule, choose the rule from the list. To delete an existing transformation rule, choose **Remove**.
## Editing transformation rules
You can add new, remove, or edit existing transformation rules in your migration project. Because DMS Schema Conversion applies the transformation rules during the launch of schema conversion, make sure that you close schema conversion and launch it again after you edit your rules.
**To edit transformation rules**
1. Sign in to the AWS Management Console, and open the AWS DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.aws.amazon.com/dms/v2/).
1. Choose **Migration projects**, and then choose your migration project.
1. Choose **Schema conversion**, and then choose **Close schema conversion**.
1. After AWS DMS closes schema conversion, choose **Modify** to edit your migration project settings.
1. For **Transformation rules**, choose one of the following actions:
+ Choose **Duplicate** to duplicate an existing transformation rule and add it in the end of the list.
+ Choose **Remove** to remove an existing transformation rule.
+ Choose the existing transformation rule to edit it.
1. After you are done editing rules, choose **Save changes**.
1. On the **Migration projects** page, choose your project from the list. Choose **Schema conversion**, then choose **Launch schema conversion**.
# Converting database schemas in DMS Schema Conversion: step-by-step guide
After you create the migration project and connect to your source and target databases, you can convert your source database objects to a format compatible with your target database. DMS Schema Conversion displays your source database schema in the left panel in a tree-view format.
Each node of the database tree is *lazy loaded*. When you choose a node in the tree view, DMS Schema Conversion requests the schema information from your source database at that time. To load the schema information faster, choose your schema, and then choose **Load metadata** from the **Actions** menu. DMS Schema Conversion then reads the database metadata and stores the information on an Amazon S3 bucket. You can now browse the database objects faster.
You can convert the whole database schema, or you can choose any schema item from your source database to convert. If the schema item that you choose depends on a parent item, then DMS Schema Conversion also generates the schema for the parent item. For example, when you choose a table to convert, DMS Schema Conversion creates the converted table and the database schema that the table is in.
## Converting database objects
You can use DMS Schema Conversion to convert an entire database schema or separate database schema objects.
**To convert an entire database schema**
1. Sign in to the AWS Management Console and open the AWS DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.aws.amazon.com/dms/v2/).
1. Choose **Migration projects**. The **Migration projects** page opens.
1. Choose your migration project, and then choose **Schema conversion**.
1. Choose **Launch schema conversion**. The **Schema conversion** page opens.
1. In the source database pane, select the check box for the schema name.
1. Choose this schema in the left pane of the migration project. DMS Schema Conversion highlights the schema name in blue and activates the **Actions** menu.
1. For **Actions**, choose **Convert**. The conversion dialog box appears.
1. Choose **Convert** in the dialog box to confirm your choice.
**To convert your source database objects**
1. Sign in to the AWS Management Console, and open the AWS DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.aws.amazon.com/dms/v2/).
1. Choose **Migration projects**. The **Migration projects** page opens.
1. Choose your migration project, and then choose **Schema conversion**.
1. Choose **Launch schema conversion**. The **Schema conversion** page opens.
1. In the source database pane, select your source database objects.
1. After you select all check boxes for the objects that you want to convert, choose the parent node for all selected objects in your left panel.
DMS Schema Conversion highlights the parent node in blue and activates the **Actions** menu.
1. For **Actions**, choose **Convert**. The conversion dialog box appears.
1. Choose **Convert** in the dialog box to confirm your choice.
For example, to convert two out of 10 tables, select the check boxes for the two tables that you want to convert. Notice that the **Actions** menu is inactive. After you choose the **Tables** node, DMS Schema Conversion highlights its name in blue and activates the **Actions** menu. Then you can choose **Convert** from this menu.
Likewise, to convert two tables and three procedures, select the check boxes for the object names. Then, choose the schema node to activate the **Actions** menu, and choose **Convert schema**.
## Editing and saving your converted SQL code
The **Schema conversion** page allows you to edit converted SQL code in your database objects. Use the following procedure to edit your converted SQL code, apply the changes, and then save them.
**To edit, apply changes to, and save your converted SQL code**
1. In the **Schema conversion** page, open the tree view in the **Source data providers** pane to display a code object.
![\[Schema conversion: Source data providers tree view\]](http://docs.aws.amazon.com/dms/latest/userguide/images/datarep-sc-editsql-1.png)
1. From the **Source data providers** pane, choose **Actions**, **Convert**. Confirm the action.
1. When the conversion completes, to view the converted SQL, expand the center pane if needed. To edit the converted SQL, choose the edit icon in the **Target SQL** pane.
![\[Schema conversion: Edit target SQL\]](http://docs.aws.amazon.com/dms/latest/userguide/images/datarep-sc-editsql-2.png)
1. After you edit the target SQL, confirm your changes by choosing the check icon at the top of the page. Confirm the action.
1. In the **Target data providers** pane, choose **Actions**, **Apply changes**. Confirm the action.
1. DMS writes the edited procedure to the target data store.
## Reviewing converted database objects
After you have converted your source database objects, you can choose an object in the left pane of your project. You can then view the source and converted code for that object. DMS Schema Conversion automatically loads the converted code for the object that you selected in the left pane. You can also see the properties or parameters of the object that you selected.
DMS Schema Conversion automatically stores the converted code as part of your migration project. It doesn't apply these code changes to your target database. For more information about applying converted code to your target database, see [Applying your converted code](schema-conversion-save-apply.md#schema-conversion-apply). To remove the converted code from your migration project, select your target schema in the right pane, and then choose **Refresh from database** from **Actions**.
After you have converted your source database objects, you can see the conversion summary and action items in the lower-center pane. You can see the same information when you create an assessment report. The assessment report is useful for identifying and resolving schema items that DMS Schema Conversion can't convert. You can save the assessment report summary and the list of conversion action items in CSV files. For more information, see [Database migration assessment reports](assessment-reports.md).
# 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, SQL Server to PostgreSQL/Aurora PostgreSQL and SAP ASE (Sybase ASE) to PostgreSQL/Aurora PostgreSQL conversions. You can convert an entire database schema or individual database schema objects.
**Warning**
Generative AI features in DMS Schema Conversion use cross-region inference. For more information, see [Cross-region inference in DMS Schema Conversion](CHAP_Security.DataProtection.CrossRegionInference.md#CHAP_Security.DataProtection.CrossRegionInference.SchemaConversion).
To convert your source database objects with generative AI, follow steps 1 to 6 in [Converting database objects ](schema-conversion-convert.md#schema-conversion-convert-steps) 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:
+ Navigate to the **Settings** tab.
+ In the **Conversion settings section**, enable the **Generative AI** option to approve the use of generative AI.
**Note**
Supported regions:
Asia Pacific (Tokyo) (ap-northeast-1)
Asia Pacific (Osaka) (ap-northeast-3)
Asia Pacific (Sydney) (ap-southeast-2)
Canada (Central) (ca-central-1)
Europe (Frankfurt) (eu-central-1)
Europe (Stockholm) (eu-north-1)
Europe (Ireland) (eu-west-1)
Europe (London) (eu-west-2)
Europe (Paris) (eu-west-3)
US East (N. Virginia) (us-east-1)
US East (Ohio) (us-east-2)
US West (Oregon) (us-west-2)
**Note**
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
SAP ASE (Sybase ASE) to Amazon RDS for PostgreSQL
SAP ASE (Sybase ASE) to Amazon Aurora PostgreSQL
IBM Db2 for Linux, UNIX and Windows (LUW) to Amazon RDS for PostgreSQL
IBM Db2 for Linux, UNIX and Windows (LUW) to Amazon Aurora PostgreSQL
IBM Db2 for z/OS to Amazon RDS for PostgreSQL
IBM Db2 for z/OS 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:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-convert.databaseobjects.html)
### Limitations
The Converting database objects with generative AI feature has the following limitations:
+ Database endpoints supporting generative AI conversion are not visible in the AWS 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
+ COMPUTE COLUMN in a table
+ 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 AWS 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 AWS account per minute |
| --- | --- |
| Asia Pacific (Tokyo) (ap-northeast-1) Asia Pacific (Osaka) (ap-northeast-3) Asia Pacific (Sydney) (ap-southeast-2) Canada (Central) (ca-central-1) Europe (Stockholm) (eu-north-1) Europe (Ireland) (eu-west-1) Europe (London) (eu-west-2) Europe (Paris) (eu-west-3) US East (Ohio) (us-east-2) | Up to 24 statements |
| Europe (Frankfurt) (eu-central-1) US East (N. Virginia) (us-east-1) US West (Oregon) (us-west-2) | Up to 40 statements |
# Specifying schema conversion settings for migration projects
After you create a migration project, you can specify conversion settings in DMS Schema Conversion. Configuring your schema conversion settings improves the performance of the converted code.
**To edit conversion settings**
1. Sign in to the AWS Management Console and open the AWS DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.aws.amazon.com/dms/v2/).
1. Choose **Migration projects**. The **Migration projects** page opens.
1. Choose your migration project. Choose **Schema conversion**, then **Launch schema conversion**.
1. Choose **Settings**. The **Settings** page opens.
1. In the **Conversion** section, change the settings.
1. Choose **Apply**, and then choose **Schema conversion**.
For all conversion pairs, you can limit the number of comments with action items in the converted code. To limit the number of comments in the converted code, open the conversion settings in your migration project.
For the **Comments in converted SQL code**, choose the severity level of action items. DMS Schema Conversion adds comments in the converted code for action items of the selected severity and higher. For example, to minimize the number of comments in your converted code, choose **Errors only**.
To include comments for all action items in your converted code, choose **All messages**.
Other conversion settings are different for each pair of source and target databases.
**Topics**
+ [Understanding Oracle to MySQL conversion settings](schema-conversion-oracle-mysql.md)
+ [Understanding Oracle to PostgreSQL conversion settings](schema-conversion-oracle-postgresql.md)
+ [Understanding SQL Server to MySQL conversion settings](schema-conversion-sql-server-mysql.md)
+ [Understanding SQL Server to PostgreSQL conversion settings](schema-conversion-sql-server-postgresql.md)
+ [Understanding PostgreSQL to MySQL conversion settings](schema-conversion-postgresql-mysql.md)
+ [Understanding IBM Db2 for LUW to Amazon RDS for PostgreSQL conversion settings](schema-conversion-db2-luw-postgresql.md)
+ [Understanding IBM Db2 for z/OS to Amazon RDS for Db2 conversion settings](schema-conversion-db2-zos-db2.md)
+ [Understanding IBM Db2 for z/OS to Amazon RDS for PostgreSQL conversion settings](schema-conversion-db2-zos-postgresql.md)
+ [Understanding SAP ASE (Sybase ASE) to PostgreSQL conversion settings](schema-conversion--sybase-ASE.md)
# Understanding Oracle to MySQL conversion settings
Oracle to MySQL conversion settings in DMS Schema Conversion include the following:
+ Your source Oracle database can use the `ROWID` pseudocolumn. MySQL doesn't support similar functionality. DMS Schema Conversion can emulate the `ROWID` pseudocolumn in the converted code. To do so, turn on the **Generate row ID** option.
If your source Oracle code doesn't use the `ROWID` pseudocolumn, turn off the **Generate row ID** option. In this case, the converted code works faster.
+ Your source Oracle code can include the `TO_CHAR`, `TO_DATE`, and `TO_NUMBER` functions with parameters that MySQL doesn't support. By default, DMS Schema Conversion emulates the usage of these parameters in the converted code.
You can use native MySQL `TO_CHAR`, `TO_DATE`, and `TO_NUMBER` functions when your source Oracle code lacks parameters that are unsupported by MySQL. In this case, the converted code works faster. To do so, select the following values:
+ **Use a native MySQL TO\$1CHAR function**
+ **Use a native MySQL TO\$1DATE function**
+ **Use a native MySQL TO\$1NUMBER function**
+ Your database and applications can run in different time zones. By default, DMS Schema Conversion emulates time zones in the converted code. However, you don't need this emulation when your database and applications use the same time zone. In this case, select **Improve the performance of the converted code where the database and applications use the same time zone**.
# Understanding Oracle to PostgreSQL conversion settings
Oracle to PostgreSQL conversion settings in DMS Schema Conversion include the following:
+ To convert applicable database objects using generative AI, enable the **Generative AI** setting. Objects successfully converted using generative AI will be clearly identified with **Action Item 5444**, which states: "This conversion uses machine learning models that generate predictions based on patterns in data. For more information, see [Converting database objects with generative AI](schema-conversion-convert.databaseobjects.md).
+ You can convert schemas for different supported versions of your target database, not just the latest version. To specify a version, use the **Target engine version** setting. Available conversion options will vary depending on the engine version you select. Currently, PostgreSQL versions 14 and 15 are supported (they have identical settings). Note that using a target engine version during schema conversion that differs from the version specified in this setting may lead to compatibility issues and errors when performing the Apply to the target operation.
+ AWS DMS can convert Oracle materialized views to tables or materialized views on PostgreSQL. For **Materialized views**, choose how to convert your source materialized views.
+ Your source Oracle database can use the `ROWID` pseudocolumn. PostgreSQL doesn't support similar functionality. DMS Schema Conversion can emulate the `ROWID` pseudocolumn in the converted code using the `bigint` or `character varying` data type. To do so, choose **Use the bigint data type to emulate the ROWID pseudocolumn** or **Use the character varying data type to emulate the ROWID pseudocolumn** for **Row ID**.
+ To convert NUMBER data type to the best fitting numeric data type, depending on the precision and scale you can select Use the optimized data type mapping for columns of the NUMBER data type.
+ If your source Oracle code doesn't use the `ROWID` pseudocolumn, choose **Don't generate**. In this case, the converted code works faster.
+ Your source Oracle code can include the `TO_CHAR`, `TO_DATE`, and `TO_NUMBER` functions with parameters that PostgreSQL doesn't support. By default, DMS Schema Conversion emulates the usage of these parameters in the converted code.
You can use native PostgreSQL `TO_CHAR`, `TO_DATE`, and `TO_NUMBER` functions when your source Oracle code lacks parameters that are unsupported by PostgreSQL. In this case, the converted code works faster. To do so, select the following values:
+ **Use a native PostgreSQL TO\$1CHAR function**
+ **Use a native PostgreSQL TO\$1DATE function**
+ **Use a native PostgreSQL TO\$1NUMBER function**
+ Your database and applications can run in different time zones. By default, DMS Schema Conversion emulates time zones in the converted code. However, you don't need this emulation when your database and applications use the same time zone. In this case, select **Improve the performance of the converted code where the database and applications use the same time zone**.
+ To continue using sequences in your converted code, select **Populate converted sequences with the last value generated on the source side**.
+ In some cases, your source Oracle database might store only integer values in the primary or foreign key columns of the `NUMBER` data type. In these cases, AWS DMS can convert these columns to the `BIGINT` data type. This approach improves the performance of your converted code. To do so, select **Convert primary and foreign key columns of the NUMBER data type to the BIGINT data type**. Make sure that your source doesn't include floating point values in these columns to avoid data loss.
+ To skip deactivated triggers and constraints in your source code, choose **Convert only active triggers and constraints**.
+ You can use DMS Schema Conversion to convert string variables that are called as dynamic SQL. Your database code can change the values of these string variables. To make sure that AWS DMS always converts the latest value of this string variable, select **Convert the dynamic SQL code that is created in called routines**.
+ PostgreSQL versions 10 and earlier don't support procedures. If you aren't familiar with using procedures in PostgreSQL, AWS DMS can convert Oracle procedures to PostgreSQL functions. To do so, select **Convert procedures to functions**.
+ To see additional information about the occurred action items, you can add specific functions to the extension pack. To do so, select **Add extension pack functions that raise user-defined exceptions**. Then choose severity levels to raise user-defined exceptions. Make sure that you apply the extension pack schema after you convert your source database objects. For more information about extension packs, see [Using extension packs](extension-pack.md).
+ Your source Oracle database can include constraints with the automatically generated names. If your source code uses these names, make sure that you select **Keep the names of system generated constraints**. If your source code uses these constraints, but doesn't use their names, clear this option to increase the conversion speed.
+ If your source and target databases run in different time zones, the function that emulates the `SYSDATE` built-in Oracle function returns different values compared to the source function. To make sure that your source and target functions return the same values, choose **Set the time zone of your source database**.
+ You can use the functions from the orafce extension in your converted code. To do so, for **Orafce built-in routines**, select the functions to use. For more information about orafce, see [orafce](https://github.com/orafce/orafce) on GitHub.
# Understanding SQL Server to MySQL conversion settings
SQL Server to MySQL conversion settings in DMS Schema Conversion include the following:
+ Your source SQL Server database can store the output of `EXEC` in a table. DMS Schema Conversion creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines to handle open datasets**.
# Understanding SQL Server to PostgreSQL conversion settings
SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include the following:
+ To convert applicable database objects using generative AI enable the **Generative AI** setting. Objects successfully converted using generative AI will be clearly identified with **Action Item 7744**, which states: "This conversion uses machine learning models that generate predictions based on patterns in data." For more information, see [Converting database objects with generative AI](schema-conversion-convert.databaseobjects.md).
+ In SQL Server, you can use indexes with the same name in different tables. However, in PostgreSQL, all index names that you use in the schema must be unique. To make sure that DMS Schema Conversion generates unique names for all your indexes, select **Generate unique names for indexes**.
+ PostgreSQL versions 10 and earlier don't support procedures. If you aren't familiar with using procedures in PostgreSQL, AWS DMS can convert SQL Server procedures to PostgreSQL functions. To do so, select **Convert procedures to functions**.
+ Your source SQL Server database can store the output of `EXEC` in a table. DMS Schema Conversion creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines to handle open datasets**.
+ You can define the template to use for the schema names in the converted code. For **Schema names**, choose one of the following options:
+ **DB** – Uses the SQL Server database name as a schema name in PostgreSQL.
+ **SCHEMA** – Uses the SQL Server schema name as a schema name in PostgreSQL.
+ **DB\$1SCHEMA** – Uses a combination of the SQL Server database and schema names as a schema name in PostgreSQL.
+ You can keep the letter case in the names of source operands. To avoid conversion of operand names to lowercase, select **Avoid casting to lowercase for case-sensitive operations**. This option is applicable only if the case-sensitivity feature is enabled in the source database.
+ You can keep the parameter names from your source database. DMS Schema Conversion can add double quotation marks to the names of parameters in the converted code. To do so, select **Keep original parameter names**.
+ You can keep a length of routine parameters from your source database. DMS Schema Conversion creates domains and uses them to specify a length of routine parameters. To do so, select **Preserve parameter lengths**.
+ To convert unsupported built-in objects to stub objects, enable the **Convert unsupported built-in objects to stub objects** setting:
+ When enabled, DMS SC replaces unsupported built-in objects with corresponding stub objects in the target database. This feature converts code sections that would normally be enclosed by migration issue 7811 or 7904. It creates stub objects based on the type of the source built-in objects `PROCEDURE` for procedures, `VIEW` for views or tables.
Conversion of a source database object with a call of an unsupported object results in a call of a stub object and migration issue 7822.
You can choose to create stub objects in a separate schema by enabling the **Create stub objects in a separate schema** option. When selected, stub objects are created in a special schema named `aws_sqlserver_stub` in the target database. If not selected, they are created in the same schema as the calling objects.
+ Stub routines are named based on the fully qualified name of the original built-in. For stub views, the naming convention includes the system schema name `system_schema_name$builtin_view_name`.
During re-conversion, DMS SC checks for existing stub routines in the target database. If a routine with the same name and input parameters already exists, it is not overwritten.
After conversion, review and implement custom code for stub routines as needed.
+ `CITEXT` datatype for all string datatypes setting in DMS Schema Conversion include the following:
+ To use the `CITEXT` datatype for case-insensitive string operations when converting from SQL Server to PostgreSQL, enable the **Use CITEXT for all string datatypes** setting. This option helps maintain consistent behavior when migrating from a case-insensitive SQL Server to a case-sensitive PostgreSQL environment.
+ When enabled, DMS SC converts all relevant string datatypes from the source SQL Server database to `CITEXT` in PostgreSQL. This eliminates the need for explicit LOWER () function calls in conditions and automatically casts string expressions in conditional operations to `CITEXT`.
+ To determine if your SQL Server instance is case-sensitive, run the following query:
```
SELECT SERVERPROPERTY('COLLATION');
```
A result containing 'CI' indicates case-insensitive, while 'CS' indicates case-sensitive.
+ The `CITEXT` conversion may not apply in scenarios where explicit case-sensitive collate settings are used at the server, database, or column level.
To use this feature, ensure that the `CITEXT` module is installed and available in your target PostgreSQL database.
+ When using the `CITEXT` datatype conversion, consider the following best practices:
+ Enable this feature when migrating from a case-insensitive SQL Server to maintain consistent behavior in PostgreSQL.
+ Review your application code to ensure it doesn't rely on case-sensitive string operations.
+ Thoroughly test your application after migration to verify that case-insensitive behavior is maintained as expected.
# Understanding PostgreSQL to MySQL conversion settings
PostgreSQL to MySQL conversion settings in DMS Schema Conversion include the following:
+ **Comments in converted SQL code**: This setting includes comments in the converted code for the action items of the selected severity and higher. This setting supports the following values:
+ Errors only
+ Errors and warnings
+ All messages
# Understanding IBM Db2 for LUW to Amazon RDS for PostgreSQL conversion settings
**Note**
The AWS Management Console does not support creating migration projects in DMS Schema Conversion that use IBM Db2 for LUW as a source data provider with Amazon RDS for PostgreSQL as a target data provider. Use the AWS CLI or DMS Schema Conversion API instead.
IBM Db2 for Linux, UNIX and Windows to Amazon RDS for PostgreSQL conversion settings in DMS Schema Conversion include the following:
+ **Comments in converted SQL code**: This setting includes comments in the converted code for the action items of the selected severity and higher. This setting supports the following values:
+ Errors only
+ Errors and warnings
+ All messages
# Understanding IBM Db2 for z/OS to Amazon RDS for Db2 conversion settings
IBM for z/OS to Amazon RDS for DB2 conversion settings in DMS Schema Conversion include the following:
+ **Comments in converted SQL code**: This setting includes comments in the converted code for the action items of the selected severity and higher. This setting supports the following values:
+ Errors only
+ Errors and warnings
+ All messages
# Understanding IBM Db2 for z/OS to Amazon RDS for PostgreSQL conversion settings
**Note**
The AWS Management Console does not support creating migration projects in DMS Schema Conversion that use IBM Db2 for z/OS as a source data provider with Amazon RDS for PostgreSQL as a target data provider. Use the AWS CLI or DMS Schema Conversion API instead.
IBM Db2 for z/OS to Amazon RDS for PostgreSQL conversion settings in DMS Schema Conversion include the following:
+ **Comments in converted SQL code**: This setting includes comments in the converted code for the action items of the selected severity and higher. This setting supports the following values:
+ Errors only
+ Errors and warnings
+ All messages
# Understanding SAP ASE (Sybase ASE) to PostgreSQL conversion settings
SAP ASE (Sybase ASE) to PostgreSQL conversion settings in DMS Schema Conversion include the following:
+ To convert applicable database objects using generative AI enable the **Generative AI** setting. Objects successfully converted using generative AI will be clearly identified with **Action Item 3077**, which states: "This conversion uses machine learning models that generate predictions based on patterns in data." For more information, see [Converting database objects with generative AI](schema-conversion-convert.databaseobjects.md).
+ **Comments in converted SQL code:** This setting includes comments in the converted code for the action items of the selected severity and higher. This setting supports the following values:
+ Errors only
+ Errors and warnings
+ All messages
+ You can define the template to use for the schema names in the converted code. For **Schema names**, choose one of the following options:
+ **DB** – Uses the SAP ASE (Sybase ASE) database name as a schema name in PostgreSQL.
+ **SCHEMA** – Uses the SAP ASE (Sybase ASE) schema name as a schema name in PostgreSQL.
+ **DB\$1SCHEMA** – Uses a combination of the SAP ASE (Sybase ASE) database and schema names as a schema name in.
+ You can keep the exact case of object names from your source database. To do so, select Treat source database object names as case sensitive. When this option is turned off, all database object names are converted to lowercase.
+ You can avoid casting operands to lowercase during case-insensitive operations. DMS Schema Conversion will not apply the LOWER function to operands in the converted code. To do so, select **Avoid casting operands to lowercase for case-insensitive operations**. When this option is not selected (default), DMS Schema Conversion automatically applies the LOWER function to convert operands to lowercase before performing case-insensitive comparisons.
+ In SAP ASE (Sybase ASE), you can create indexes with identical names across different tables. PostgreSQL requires all index names within a schema to be unique. To ensure AWS Schema Conversion creates unique index names during migration, select **Generate unique names for indexes**. This option automatically adds prefixes to index names to prevent naming conflicts in your target PostgreSQL database.
# Refreshing your database schemas in DMS Schema Conversion
After you create a migration project, DMS Schema Conversion stores the information about your source and target schemas in this project. DMS Schema Conversion uses *lazy loading* to load metadata only as it is needed, such as when you choose a node in your database tree. You can use *eager loading* to load the schema information faster. To do so, choose your schema, and then choose **Load metadata** from **Actions**.
After you automatically or manually load the object to your migration project, DMS Schema Conversion doesn't use lazy loading again. So when you change objects, such as tables and procedures in your database, make sure to refresh them in your migration project.
To refresh schemas from the database, select the objects that you want to refresh, and choose **Refresh from database** from **Actions**. You can refresh database objects in your source and target database schemas:
+ **Source** – If you update your source database schema, choose **Refresh from database** to replace the schema in your project with the latest schema from your source database.
+ **Target** – If you update the schema for your target database, DMS Schema Conversion replaces the schema in your project with the latest schema from your target database. DMS Schema Conversion replaces your converted code with the code from your target database. Make sure that you applied the converted code to your target database before you choose **Refresh from database**. Otherwise, convert your source database schema again.
# Saving and applying your converted code in DMS Schema Conversion
After DMS Schema Conversion converts your source database objects, it doesn't immediately apply the converted code to your target database. Instead, DMS Schema Conversion stores the converted code in your project until you are ready to apply it to your target database.
Before you apply the converted code, you can update your source database code and convert the updated objects again to address the existing action items. For more information about items that DMS Schema Conversion can't convert automatically, see [Creating database migration assessment reports with DMS Schema Conversion](assessment-reports.md). For more information about refreshing your source database objects in migration project for DMS Schema Conversion, see [Refreshing your database schemas](schema-conversion-refresh.md).
Instead of applying the converted code directly to your database in DMS Schema Conversion, you can save the code to a file as a SQL script. You can review these SQL scripts, edit them where necessary, and then manually apply these SQL scripts to your target database.
## Saving your converted code to a SQL file
You can save your converted schema as SQL scripts in a text file. You can modify the converted code to address action items that DMS Schema Conversion can't convert automatically. You can then run your updated SQL scripts on your target database to apply the converted code to your target database.
**To save your converted schema as SQL scripts**
1. Sign in to the AWS Management Console and open the AWS DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.aws.amazon.com/dms/v2/).
1. Choose **Migration projects**. The **Migration projects** page opens.
1. Choose your migration project, and then choose **Schema conversion**.
1. Choose **Launch schema conversion**. The **Schema conversion** page opens.
1. In the right pane, choose the target database schema or select the converted objects that you want to save. Make sure that DMS Schema Conversion highlights the parent node name in blue and activates the **Actions** menu for the target database.
1. Choose **Save as SQL** for **Actions**. The **Save** dialog box appears.
1. Choose **Save as SQL** to confirm your choice.
DMS Schema Conversion creates an archive with SQL files and stores this archive in your Amazon S3 bucket.
1. (Optional) Change the S3 bucket for the archive by editing the schema conversion settings in your instance profile.
1. Open the SQL scripts from your S3 bucket.
## Applying your converted code
When you are ready to apply your converted code to your target database, choose the database objects in the right pane of your project. You can apply changes to an entire database schema or selected database schema objects.
After you select the database objects, DMS Schema Conversion highlights the name of the selected node or the parent node in blue. It then activates the **Actions** menu. Choose **Apply changes** for **Actions**. In the dialog box that appears, choose **Apply** to confirm your choice and apply the converted code to your target database.
## Applying the extension pack schema
When you apply your converted schema to your target database for the first time, DMS Schema Conversion might also apply the extension pack schema. The extension pack schema emulates system functions of the source database that are required to run your converted code for your target database. If your converted code uses the functions of the extension pack, make sure that you apply the extension pack schema.
To apply the extension pack to your target database manually, choose **Apply changes** for **Actions**. In the dialog box that appears, choose **confirm** to apply the extension pack to your target database.
We recommend that you don't modify the extension pack schema to avoid unexpected results in the converted code.
For more information, see [Using extension packs in DMS Schema Conversion](extension-pack.md).
# Converting embedded SQL in Java applications
When you use AWS DMS and DMS Schema Conversion to migrate a database, you might need to convert the embedded SQL in your application to be compatible with your target database. Rather than converting it manually, you can use Amazon Q in the IDE to automate the conversion. Amazon Q uses metadata from a DMS Schema Conversion to convert embedded SQL in your application to a version that is compatible with your target database. Amazon Q will detect Oracle SQL code in your application and convert them into PostgreSQL syntax. For more information, see [Converting embedded SQL in Java applications with Amazon Q Developer](https://docs.aws.amazon.com/amazonq/latest/qdeveloper-ug/transform-sql.html).
**Downloading metadata file for embedded SQL conversion in a Java Application**
1. Once you complete the conversion, close the project and go to the S3 bucket where your project is stored.
1. Open the folder and find the project folder (“sct-project”).
1. Download the object inside the project folder. This will be a zip file.
1. Use the downloaded zip file as an input to your transformation with Amazon Q: [Converting embedded SQL in Java applications with Amazon Q Developer](https://docs.aws.amazon.com/amazonq/latest/qdeveloper-ug/transform-sql.html).