MySQL to Db2 Data Replication

NineData Data Replication supports replicating MySQL data to Db2. This guide describes schema and full data replication.

Before you begin

• Add the source and target data sources to NineData. For details, see Add data source.
• Use a MySQL data source as the source and a Db2 data source as the target.
• If you map to an existing Db2 schema, make sure it is visible to the target data source. If you create the schema after opening the wizard, refresh the metadata on the mapping page before selecting the schema.

Example type mappings

The table below shows the listed source column definitions in MySQL 8.0.28 and their target catalog types in Db2 11.5.9.0.

Example column	MySQL source definition	Db2 target catalog type
id	INT	INTEGER
label	VARCHAR(40)	VARCHAR(160)
amount	DECIMAL(12,2)	DECIMAL(12,2)
created_at	DATETIME(3)	TIMESTAMP (scale 3)
is_active	TINYINT(1)	SMALLINT

This table covers only the listed type parameter combinations. It does not define mappings for other lengths, precision, unsigned attributes, or value ranges. After configuring the task, review the generated target DDL and target column definitions.

Restrictions

Before starting full replication, review Target Table Exists Data. If the selected strategy clears existing data in the target tables before writing, use empty target tables or back up any data that must be retained.

Procedure

On the Mapping tab, map the source MySQL database to a target Db2 schema and confirm the target table name. If you created the schema after opening the wizard, click Refresh Metadata to refresh the metadata, then select the schema.

1. Sign in to the NineData Console.

2. In the left navigation pane, click Replication > Data Replication.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, configure the fields in the table, and click Next.

   Parameter	Description
   Name	Enter a name for the data synchronization task. To make the task easier to find and manage later, use a meaningful name. Up to 64 characters are supported.
   Source	The data source that contains the objects to synchronize.
   Target	The data source that receives the synchronized objects.
   Target Object Name	Select the case conversion rule for object names after they are migrated from the source to the target. Convert all to Lowercase: Regardless of the naming rule on the source, all target names are lowercase. Consistent with Source: Keep the naming rule of the source. Convert all to Uppercase: Regardless of the naming rule on the source, all target names are uppercase.
   Type	Select the replication type. Schema: Synchronize only the database and table schemas of the source data source, without synchronizing data. Full: Synchronize all objects and data from the source data source, namely full data replication.
   Spec	The specification of the replication task. A larger specification provides a higher replication rate. Hover over the icon to view the rate and configuration information of each specification. If you configure the data replication task before purchasing resources, you can select the required specification here. If you purchase resources before configuring the task, NineData selects the specification specified during purchase, and you cannot change it in the task configuration.
   If target table already exists (Required when Schema is selected)	Pre-Check Error and Stop Task: Stop the task when a table with the same name is detected during the precheck stage. Skip and Continue Task: When a table with the same name is detected during the precheck stage, display a message and continue the task. During schema replication, ignore the table with the same name. If you also perform data replication, data is appended to the table with the same name and existing data is not overwritten. Delete Objects and Rewrite: When a table with the same name is detected during the precheck stage, display a message and continue the task. During schema replication, delete the table with the same name in the target database and replicate the table schema again based on the source database. If you also perform data replication, data is written after schema replication completes.
   Target Table Exists Data (Required when Full is selected)	Pre-Check Error and Stop Task: Stop the task when data is detected in the target table during the precheck stage. Ignore existing target data and append to it.: When data is detected in the target table during the precheck stage, ignore that data and append other data. Clear target existing data before write: When data is detected in the target table during the precheck stage, delete that data and write it again.

5. On the Objects tab, configure the parameters in the table, and click Next.

   Parameter	Description
   Replication Objects	Select the content to replicate. Select All Objects to replicate all content in the source database, or select Customized Object, select the content to replicate in the Source Object list, and click > to add it to the Target Object list on the right.

   To create multiple replication tasks with the same replication objects, import a configuration file. Click Import Config, click Download Template to download the template, edit the file, and then click Upload to upload it and import the objects in bulk. For field descriptions and examples, see Import Replication Object Templates. The configuration file uses these fields:

6. On the Mapping tab, configure the mapping that matches the selected replication type, then click Save and Pre-Check. If source or target metadata changes while you configure mappings, click Refresh Metadata to refresh the metadata.

   • Includes Schema: Configure the table name after synchronization to the target data source.

   • Does not include Schema: NineData selects the database with the same name in the target data source by default. If no such database exists, select the target database manually. The table names and column names in the target database must match the synchronization objects. If they do not match, map the table names and column names manually.

   Other available actions:

   • Click Mapping & Filtering to customize the column names after synchronization to the target data source.
   • On the Mapping & Filtering page, enter a comparison expression in the text box below Data Filter as the filtering condition. Only data that meets the filtering conditions is synchronized to the target data source. For example, if the filtering condition is set to emp_no>=10005, data whose emp_no column value is less than 10005 is not synchronized to the target data source.
   • Click the icon to the right of "Target Table" to search for a table name and replace it with the target name.
   • Enter a table name in the Search Table text box to quickly locate the target table.
   • Click Batch Configuration to define common rules in batches, such as table name and column name case conversion, prefix or suffix addition, and replacement. Use this option to apply mapping configuration to many tables and columns at the same time.

• Click the drop-down menu to the right of Object Owner to specify the owner of the object. The available options and default value depend on what the current page displays.

7. On the Pre-check tab, wait for the precheck to finish. After it passes, open the saved task's details from the replication task list and start the task.

   • Select Enable data consistency comparison. Based on the selected Type, the system automatically starts a data consistency comparison based on the source data source at the corresponding time. The comparison checks whether the source and target data are consistent. The start time is as follows:

     • Schema: Starts after schema replication completes.
     • Schema+Full: Starts after full replication completes.
     • Full: Starts after full replication completes.

   • If the precheck fails, click Details in the Actions column for the failed check item, review the cause, fix the issue, and then click Check Again to run the precheck again until it passes.

   • Items with Warning in Result can be fixed or ignored if required.

8. After the replication task starts, you can perform the following actions:

   • Click View Details to view the execution status of each stage of the synchronization task.
   • Click Back to list to return to the Replication task list page.

View replication results

1. Sign in to the NineData Console.

2. In the navigation menu, select Replication > Data Replication.

3. On the Replication page, click the Task ID of the target synchronization task. The task details page shows the following information.

   

   Number	Function	Description
   1	Sync Delay	The synchronization delay between the source and target data sources. 0 seconds means the target has caught up with the source. Switch traffic based on your migration plan.
   2	Configure Alerts	When the task fails, NineData notifies the selected channel through the configured alert.
   3	More	Pause: Pause the task. Only tasks with Running status are selectable. Duplicate: Create a new replication task with the same configuration as the current task. Terminate: Terminate tasks that are incomplete or still listening (that is, in incremental synchronization). After a task is terminated, it cannot be restarted, so proceed with caution. If the replication objects include triggers or events, the dialog displays termination options for the applicable object type: Migrate triggers and terminate or Migrate events and terminate: Migrate the applicable objects to the target data source before terminating the task. Do not migrate triggers; terminate immediately or Do not migrate events; terminate immediately: Terminate the task immediately without migrating the applicable objects. Delete: Delete the task. Once deleted, it cannot be recovered, so proceed with caution.
   4	Structure Replication (displayed in scenarios involving structure replication)	Displays the progress and detailed information of structure replication. Select Log to view the execution logs of structure replication. Select the to view the latest information. Select View DDL in the Actions column for the target object in the list to view SQL replay.
   5	Full Replication (displayed in scenarios involving full replication)	Displays the progress and detailed information of full replication. Select Monitor to view various monitoring indicators during full replication. During full replication, select Flow Control Settings on the monitoring page to limit the rate of writing to the target data source per second. The unit is rows/second. Select Log to view the execution logs of full replication. Select the to view the latest information.
   6	Incremental Replication (displayed in scenarios involving incremental replication)	Displays incremental replication indicators. Select View Threads to view the operations currently being executed by the current replication task, including: Thread ID: Replication tasks are executed in multiple threads, and this shows the current thread number. Execute SQL: Details of the SQL statement currently being executed by the current thread. Response Time: The response time of the current thread. If this value increases, it indicates that the current thread may be stuck for some reason. Event Time: The timestamp when the current thread was started. Status: The status of the current thread. Select Flow Control Settings to limit the rate of writing to the target data source per second. The unit is rows/second. Select Log to view the execution logs of incremental replication. Select the to view the latest information.
   7	Modify Object	Displays the modification records of synchronization objects. Select Modify Objects to configure the synchronization objects. Select the to view the latest information.
   8	Data Comparison	Displays the comparison results between the source and target data sources. If data comparison is not enabled, select Enable Comparison on the page to enable it. Select Re-compare to rerun the comparison for the current source and target data sources. Select Stop to stop the comparison task immediately after it starts. Select Log to view the execution logs of consistency comparison. Select Monitor (displayed only in data comparison) to view the trend chart of RPS (records per second) comparison. Select Details to view earlier records. Select the in the Actions column in the comparison list (displayed under the Data tab only when inconsistencies are found) to view details of the comparison between the source and target data sources. Select the in the Actions column in the comparison list (displayed only when inconsistencies are found): Generate SQL to address the mismatch. The SQL may modify data on the target. Before running it, manually review each statement, target object, and impact scope, and confirm a target backup or an applicable rollback plan. Run the statements in the reviewed order; do not execute generated SQL in bulk without review.
   9	Expand	Displays detailed information of the current replication task. Common actions: Export table configuration: Export the current task's database and table configuration for quick import when creating another replication task with the same objects. Alert Rules: Configure alerts for the current task.