Project Settings (Migration) (Db2ToSQL)
The Migration page of the Project Settings dialog box contains settings that customize how SQL Server Migration Assistant (SSMA) migrates data from Db2 to SQL Server.
The Migration pane is available in both the Project Settings and Default Project Settings dialog boxes.
To specify settings for all SSMA projects, navigate to Tools > Default Project Settings, select the migration project type for which settings are required to be viewed or changed. From the Migration Target Version dropdown list, select General at the bottom of the left pane, and then select Migration.
To specify settings for the current project, navigate to Tools > Project Settings, select General at the bottom of the left pane, and then select Migration.
Migration engine
| Term | Definition |
|---|---|
| Migration Engine | Specifies database engine used during Data Migration. Client side data migration refers to SSMA client retrieving the data from the source and bulk inserting that data into SQL Server. Server side data migration refers to SSMA data migration engine (bulk copy program) running on the SQL Server box as a SQL Agent job retrieving data from the source and inserting directly into SQL Server thus avoiding an extra client-hop (better performance). Default Mode: Client Side Data Migration Engine Optimistic Mode: Client Side Data Migration Engine Full Mode: Client Side Data Migration Engine |
Important
When the Migration Engine option is set to Server Side Data Migration Engine, a new Project setting option Use 32-Bit Server Side Data Migration Engine is displayed. It specifies whether 32 bit or 64 bit Bulk Copy Program (BCP) utility is used to migrate data.
Miscellaneous options
| Term | Definition |
|---|---|
| Batch Size | Specifies the batch size used during data migration. Default Mode: 10000 Optimistic Mode: 10000 Full Mode: 10000 |
| Check constraints | Specifies whether SSMA should check constraints when it inserts data into SQL Server tables. Default Mode: False Optimistic Mode: False Full Mode: False |
| Data Migration Timeout | Specifies the timeout used during data migration Default Mode: 15 Optimistic Mode: 15 Full Mode: 15 |
| Extended Data Migration Options | Shows extra data migration options for each table in separate detail tab. Default Mode: Hide Optimistic Mode: Hide Full Mode: Hide |
| Fire triggers | Specifies whether SSMA should fire insertion triggers when it adds data to SQL Server tables. Default Mode: False Optimistic Mode: False Full Mode: False |
| Keep identity | Specifies whether SSMA preserves null values in the source data when it adds data to SQL Server, regardless of the default values that are specified in SQL Server. Default Mode: True Optimistic Mode: True Full Mode: False |
| Keep nulls | Specifies whether SSMA preserves null values in the source data when it adds data to SQL Server, regardless of the default values that are specified in SQL Server. Default Mode: True Optimistic Mode: True Full Mode: True |
| Mark string Trim operation with error | If the target column size is less than the source string length, the value is trimmed and marked as an error. Default Mode: Yes Optimistic Mode: Yes Full Mode: Yes |
| On Error | Stops Data migration when an error occurs. It has three options: Stop migration: Stops data migration operation Proceed to next table: Stops data migration to the current table and proceeds to the next one Proceed to next batch: Stops data migration to the current batch and proceeds to the next one Default Mode: Proceed to the next batch Optimistic Mode: Proceed to the next batch Full Mode: Proceed to the next batch |
| Replace unsupported dates | Specifies whether SSMA should correct dates that are earlier than the earliest SQL Server datetime date (1 January 1753). To keep the current date values, select Do nothing. SQL Server doesn't accept dates before 1 January 1753 in a datetime column. If you use older dates, you must convert the datetime values to character values. To convert dates before 1 January 1753 to NULL, select Replace with NULL.To replace dates before 1 January 1753 with a supported date, select Replace with nearest supported date. Default Mode: Do nothing Optimistic Mode: Do nothing Full Mode: Replace with nearest supported date |
| Table lock | Specifies whether SSMA locks tables when it adds data to tables during data migration. Obtains a bulk update lock during the bulk copy operation. If the value is False, a lock is set at the row level. Default Mode: True Optimistic Mode: True Full Mode: True |
Parallel data migration
| Term | Definition |
|---|---|
| Parallel Data Migration Mode | Specifies the mode used to fork threads to enable parallel data migration. In Auto mode, SSMA chooses the number of threads (10 by default) forked to migrate data. In Custom mode, user can specify the number of threads forked to migrate data (minimum is 1 and maximum is 100). Currently, only client side data migration engine supports parallel data migration. Default Mode: Auto Optimistic Mode: Auto Full Mode: Auto |
Important
When the Parallel Data Migration Mode option is set to Custom, a new Project setting option Thread Count is displayed. It specifies number of threads used for data migration.