# Specifying schema conversion settings for migration projects
<a name="schema-conversion-settings"></a>

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
<a name="schema-conversion-oracle-mysql"></a>

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
<a name="schema-conversion-oracle-postgresql"></a>

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
<a name="schema-conversion-sql-server-mysql"></a>

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
<a name="schema-conversion-sql-server-postgresql"></a>

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
<a name="schema-conversion-postgresql-mysql"></a>

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
<a name="schema-conversion-db2-luw-postgresql"></a>

**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
<a name="schema-conversion-db2-zos-db2"></a>

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
<a name="schema-conversion-db2-zos-postgresql"></a>

**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
<a name="schema-conversion--sybase-ASE"></a>

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.