Use Data Transmission Service (DTS) to set up a live data synchronization pipeline from an IBM Db2 for Linux, UNIX, and Windows (LUW) self-managed database to an ApsaraDB RDS for MySQL instance. DTS runs full data synchronization followed by ongoing incremental synchronization using Db2's change data capture (CDC) replication technology.
Prerequisites
Before you begin, make sure you have:
-
An ApsaraDB RDS for MySQL instance. See Create an ApsaraDB RDS for MySQL instance
-
Enough free storage on the destination instance to hold all data from the source Db2 for LUW database
-
Database accounts with the required permissions (see Prepare database accounts)
-
Log archiving enabled on the Db2 for LUW database (see Enable log archiving)
How synchronization works
The synchronization runs in up to three phases:
| Phase | Description | Billing |
|---|---|---|
| Schema synchronization | Copies table schemas, including foreign keys, from source to destination | Free |
| Full data synchronization | Reads all existing data from the source and writes it to the destination | Free |
| Incremental data synchronization | Tails Db2's transaction logs using CDC to replicate INSERT, UPDATE, and DELETE operations in near real time | Charged. See Billing overview |
Supported synchronization topologies:
-
One-way one-to-one synchronization
-
One-way one-to-many synchronization
-
One-way cascade synchronization
-
One-way many-to-one synchronization
For the full list, see Synchronization topologies.
Limitations
General limits (all synchronization types)
| Limit | Details |
|---|---|
| Primary or unique key required | Tables must have a PRIMARY KEY or UNIQUE constraint, with all fields unique. Without this, the destination may contain duplicate records. |
| Outbound bandwidth | The server hosting Db2 for LUW must have enough outbound bandwidth. Insufficient bandwidth reduces synchronization speed. |
| Table rename limit | When synchronizing tables as objects and renaming tables or columns in the destination, a single task supports up to 5,000 tables. Tasks exceeding this limit return a request error. Split the work across multiple tasks, or synchronize at the database level instead. |
| Off-peak scheduling | Full data synchronization reads from the source and writes to the destination concurrently, increasing server load. Schedule the initial sync during off-peak hours. |
| Destination tablespace size | Concurrent INSERT operations during full data synchronization cause fragmentation in destination tables. The destination tablespace will be larger than the source after full sync. |
| Write through DTS only | Writing to the destination from other sources during synchronization can cause data inconsistency. After synchronization completes, use Data Management (DMS) for online DDL operations. See Perform lock-free DDL operations. |
| MySQL column name case sensitivity | MySQL column names are case-insensitive. If the source database has columns whose names differ only in capitalization, those columns map to the same destination column, producing unexpected results. |
| Foreign key behavior | DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level during full and incremental sync. Cascade UPDATE or DELETE operations on the source during synchronization may cause data inconsistency. |
Log retention requirements
Log retention requirements differ based on the synchronization types you select:
| Synchronization types selected | Minimum log retention |
|---|---|
| Incremental data synchronization only | More than 24 hours |
| Full data synchronization + incremental data synchronization | At least 7 days |
After full data synchronization completes, you can reduce the retention period to more than 24 hours. Insufficient log retention may prevent DTS from retrieving logs, causing task failure or data loss, and is not covered by the DTS SLA.
Log archiving must be enabled before you start. If log archiving is off, the precheck fails and the task cannot start.
Incremental synchronization (CDC) limits
These limits apply only when incremental data synchronization is running:
| Limit | Details |
|---|---|
| CDC technology constraints | DTS uses Db2's CDC replication technology for incremental sync, which has its own restrictions. See General data restrictions for SQL Replication. |
| Primary/secondary switchover | If the source database performs a primary/secondary switchover while the task is running, the task fails. |
| Synchronization latency accuracy | DTS calculates latency based on the timestamp of the latest synchronized record versus the current source timestamp. If no DML operations run on the source for an extended period, the displayed latency may be inaccurate. Run a DML operation on the source to refresh the latency value. If you select an entire database as the synchronization object, create a heartbeat table that receives updates every second. |
| Only DML operations are synchronized | DTS synchronizes INSERT, UPDATE, and DELETE. DDL operation failures do not stop the task. Check failed DDL statements in task logs. |
Preparations
Prepare database accounts
Create accounts with the following permissions before configuring the DTS task:
| Database | Required permission | References |
|---|---|---|
| Db2 for LUW | Database administrator (DBADM authority) | Creating group and user IDs for a Db2 database installation (Linux and UNIX) |
| ApsaraDB RDS for MySQL | Read and write permissions on the destination database | Create an account and Modify account permissions |
Enable log archiving
Log archiving is required for incremental data synchronization. Configure the LOGARCHMETH1 or LOGARCHMETH2 parameter on the Db2 for LUW database:
Create a synchronization task
Step 1: Go to the Data Synchronization Tasks page
-
Log on to the Data Management (DMS) console.
-
In the top navigation bar, click Data + AI.
-
In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.
Console layout may vary. See Simple mode and Customize the layout and style of the DMS console for details. You can also navigate directly to the Data Synchronization Tasks page of the new DTS console.
Step 2: Select the region
On the right side of Data Synchronization Tasks, select the region where the data synchronization instance resides.
In the new DTS console, select the region in the top navigation bar.
Step 3: Configure source and destination databases
Click Create Task. In the Create Data Synchronization Task wizard, configure the following parameters:
| Parameter | Description |
|---|---|
| Task Name | A name for the DTS task. DTS generates a name automatically. Specify a descriptive name for easy identification. Task names do not need to be unique. |
Source database
| Parameter | Description |
|---|---|
| Select a DMS database instance | Select an existing DMS database instance to auto-fill the connection parameters, or leave blank and configure the parameters manually. |
| Database Type | Select DB2 for LUW. |
| Connection Type | Select the access method based on where the source database is deployed. This example uses Self-managed Database on ECS. For network environment setup for self-managed databases, see Preparation overview. |
| Instance Region | The region where the Db2 for LUW database resides. |
| Replicate Data Across Alibaba Cloud Accounts | This example uses No. |
| ECS Instance ID | The ID of the Elastic Compute Service (ECS) instance that hosts the source Db2 for LUW database. |
| Port Number | The service port of the source Db2 for LUW database. Default: 50000. |
| Database Name | The name of the source Db2 for LUW database. |
| Database Account | The Db2 for LUW account. See Prepare database accounts for required permissions. |
| Database Password | The password for the database account. |
Destination database
| Parameter | Description |
|---|---|
| Select a DMS database instance | Select an existing DMS database instance to auto-fill the connection parameters, or leave blank and configure the parameters manually. |
| Database Type | Select MySQL. |
| Connection Type | Select Alibaba Cloud Instance. |
| Instance Region | The region where the destination ApsaraDB RDS for MySQL instance resides. |
| RDS Instance ID | The ID of the destination ApsaraDB RDS for MySQL instance. |
| Database Account | The ApsaraDB RDS for MySQL account. See Prepare database accounts for required permissions. |
| Database Password | The password for the database account. |
| Encryption | Select Non-encrypted or SSL-encrypted. To use SSL encryption, enable SSL on the RDS instance first. See Use a cloud certificate to enable SSL encryption. |
Step 4: Test connectivity
Click Test Connectivity and Proceed.
DTS automatically adds its server CIDR blocks to the whitelist or security group rules of Alibaba Cloud database instances and ECS-hosted databases. For self-managed databases in data centers or third-party clouds, manually add the DTS CIDR blocks to the database whitelist. See Add the CIDR blocks of DTS servers.
Adding DTS CIDR blocks to whitelists or security groups introduces security risks. Before proceeding, take measures to protect your environment — such as strengthening credentials, restricting exposed ports, auditing API calls, and regularly reviewing whitelist and security group rules. For higher security, connect through Express Connect, VPN Gateway, or Smart Access Gateway.
Step 5: Configure objects and advanced settings
Basic settings
| Parameter | Description |
|---|---|
| Synchronization Types | Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. By default, only Incremental Data Synchronization is selected. Full data synchronization must complete before incremental synchronization begins. |
| Processing Mode of Conflicting Tables | Precheck and Report Errors: checks for tables with identical names in the source and destination. The task fails the precheck if duplicates exist. To rename conflicting tables, use object name mapping before starting. Ignore Errors and Proceed: skips the duplicate-name check. During full sync, existing destination records with matching primary or unique key values are retained; during incremental sync, they are overwritten. If schemas differ, initialization may fail or only some columns sync. Use with caution. |
| Source Objects | Select objects from Source Objects and click  to add them to Selected Objects. Select columns, tables, or databases. If you select tables or columns, DTS does not synchronize views, triggers, or stored procedures. |
| Selected Objects | To rename a single object in the destination, right-click it in Selected Objects. See Map the name of a single object. To rename multiple objects at once, click Batch Edit. See Map multiple object names at a time. To filter rows using a WHERE clause, right-click an object and specify conditions. See Specify filter conditions. To select specific SQL operations for a table, right-click the object and choose the operations. |
Advanced settings
| Parameter | Description |
|---|---|
| Monitoring and Alerting | Configure alerts for task failures or latency threshold breaches. Select No to skip, or Yes to configure the alert threshold and notification contacts. See Configure monitoring and alerting when you create a DTS task. |
| Retry Time for Failed Connections | The retry window after a connection failure. Valid values: 10–1440 minutes. Default: 720 minutes. Set this to more than 30 minutes. If DTS reconnects within the window, the task resumes automatically; otherwise, the task fails. When multiple tasks share the same source or destination, the shortest retry window applies. Note that you are charged for the DTS instance during retries. |
| Configure ETL | Select Yes to define extract, transform, and load (ETL) rules in the code editor. See Configure ETL in a data migration or data synchronization task. Select No to skip ETL. |
Step 6: Run the precheck
Click Next: Save Task Settings and Precheck.
To preview the API parameters for this task configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters before proceeding.
If the precheck fails:
-
Click View Details next to a failed item, fix the issue, then click Precheck Again.
-
If an alert item can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may result in data inconsistency.
Step 7: Purchase the synchronization instance
Wait until Success Rate reaches 100%, then click Next: Purchase Instance.
On the buy page, configure the following parameters:
| Parameter | Description |
|---|---|
| Billing Method | Subscription: pay upfront for a set period. More cost-effective for long-running tasks. Pay-as-you-go: billed hourly. Suitable for short-term tasks. Release the instance when no longer needed to stop charges. |
| Resource Group Settings | The resource group for the synchronization instance. Default: default resource group. See What is Resource Management? |
| Instance Class | The instance class determines synchronization speed. See Instance classes of data synchronization instances. |
| Subscription Duration | Available when Billing Method is Subscription. Specify the subscription duration and the number of data synchronization instances to create. Select 1–9 months, or 1, 2, 3, or 5 years. |
Step 8: Start the task
Read and select Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start. In the confirmation dialog, click OK.
The task appears in the task list. Monitor its progress there.
Verify the synchronization
After the task Status changes to Completed, run the following command on the destination database to confirm data was written correctly:
ANALYZE TABLE <table_name>;This check is especially important after events such as a high-availability (HA) switchover in the source database, where data may have been written only to memory and not persisted.
What's next
-
Map object names — rename objects in the destination database
-
Specify filter conditions — filter rows with WHERE conditions
-
Configure monitoring and alerting — set up latency and failure alerts
-
View task logs — inspect failed DDL statements and task events
-
Perform lock-free DDL operations — run DDL on the destination after sync completes