Data Transmission Service:Synchronize data from a Db2 for LUW database to an AnalyticDB for PostgreSQL

Source database requirements

• Tables must have a PRIMARY KEY or UNIQUE constraint, with all fields unique. Tables without these constraints may produce duplicate records in the destination.

• When synchronizing tables with column or object renaming, a single task supports up to 5,000 tables. For more than 5,000 tables, create multiple tasks or synchronize at the database level instead.

• Data logging must be enabled on the source database, or the precheck fails.

  • Incremental-only sync: retain data logs for more than 24 hours.

  • Full + incremental sync: retain data logs for at least 7 days. After full sync completes, you can reduce the retention period to more than 24 hours.

  Warning

  If data logs are not retained for the required duration, DTS may fail to read them, causing task failure or, in exceptional cases, data inconsistency or loss. Insufficient retention means the service reliability or performance stated in the SLA of DTS cannot be guaranteed.

• The server hosting the source database must have enough outbound bandwidth. Limited bandwidth reduces synchronization speed.

Other limitations

• CDC replication is subject to IBM's own restrictions. See General data restrictions for SQL Replication for details.

• Concurrent INSERT operations during full sync cause table fragmentation in the destination, so the destination tablespace will be larger than the source after full sync completes.

• Write to the destination database only through DTS during synchronization. Writing through other tools may cause data inconsistency. After sync completes, use Data Management (DMS) for DDL operations online. See Perform lock-free DDL operations.

• Only tables can be selected as sync objects. AO tables are not supported.

• If you use column mapping for non-full-table sync, or if source and destination table schemas differ, columns present in the source but absent in the destination are dropped.

Foreign key behavior by sync phase

Special cases (self-managed Db2 for LUW)

• If a primary/secondary switchover occurs on the source database while the task is running, the task fails.

• Synchronization latency is calculated based on the timestamp of the latest synced record in the destination and the current timestamp in the source. If no DML operations occur on the source for an extended period, the displayed latency may be inaccurate. Perform a DML operation on the source to refresh the latency.

  When synchronizing an entire database, create a heartbeat table that is updated every second to keep the latency accurate.

Step 1: Create database accounts and grant permissions

Step 2: Enable log archiving on the source database

DTS reads incremental changes from the Db2 for LUW archive logs. Enable log archiving using the logarchmeth1 and logarchmeth2 configuration parameters.

See the IBM documentation for setup instructions:

• logarchmeth1 — Primary log archive method

• logarchmeth2 — Secondary log archive method

Step 1: Open the Data Synchronization Tasks page

1. Log on to the Data Management (DMS) console.

2. In the top navigation bar, click Data + AI.

3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

The navigation path may vary based on the DMS console mode and layout. See Simple mode and Customize the layout and style of the DMS console. Alternatively, go to the Data Synchronization Tasks page in the new DTS console.

Step 2: Select a region

On the Data Synchronization Tasks page, select the region where the data synchronization instance will reside.

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 wizard, configure the following parameters.

Source database

Destination database

Step 4: Test connectivity

Click Test Connectivity and Proceed.

DTS automatically adds its CIDR blocks to the security settings of the source and destination databases:

• Alibaba Cloud database instance (such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB): CIDR blocks are added to the instance whitelist.

• Self-managed database on ECS: CIDR blocks are added to the ECS instance security group rules. Make sure the ECS instance can reach the database.

• On-premises or third-party cloud database: Manually add DTS server CIDR blocks to the database whitelist. See Add the CIDR blocks of DTS servers.

Step 5: Configure objects and advanced settings

Basic settings

Advanced settings

Step 6: Configure table fields for AnalyticDB for PostgreSQL

Click Next: Configure Database and Table Fields. Set the primary key column and distribution key column for each table being synchronized to the destination AnalyticDB for PostgreSQL instance.

Step 7: Save settings and run the precheck

Click Next: Save Task Settings and Precheck.

To view the OpenAPI parameters for this task configuration before saving, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

DTS runs a precheck before starting the task. If the precheck fails:

• Click View Details next to each failed item, resolve the issue, and click Precheck Again.

• If an alert item can be safely ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alert items may lead to data inconsistency.

Step 8: Purchase the synchronization instance

Wait until the Success Rate reaches 100%, then click Next: Purchase Instance.

On the purchase page, configure the following parameters.

Read and select Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start > OK.

The task appears in the task list. Monitor its progress from there.