AnalyticDB:Synchronize data from a Db2 for LUW database to an AnalyticDB for PostgreSQL instance

General limitations

• Tables must have PRIMARY KEY or UNIQUE constraints with all fields unique. Otherwise, duplicate records may appear in the destination.

• Only tables can be selected as synchronization objects — views, triggers, and stored procedures are not synchronized. Tables cannot be append-optimized (AO) tables.

• When synchronizing up to 5,000 tables with destination table modifications (such as renaming tables or columns), a single task is sufficient. For more than 5,000 tables, split the workload across multiple tasks or synchronize the entire database in one task.

• Column mapping for non-full table synchronization, or schema mismatches between source and destination tables, will cause data in unmapped source columns to be lost.

• During initial full data synchronization, concurrent INSERT operations cause fragmentation in destination tables, so the destination tablespace will be larger than the source.

• Run synchronization tasks during off-peak hours to minimize performance impact on source and destination databases.

• Write data to the destination database only through DTS during synchronization to prevent data inconsistency. After synchronization completes, use Data Management (DMS) to run DDL statements online. See Perform lock-free DDL operations.

• The source Db2 for LUW server must have sufficient outbound bandwidth; insufficient bandwidth reduces synchronization speed.

Incremental data synchronization (CDC) limitations

• DTS uses the CDC replication technology of Db2 for LUW for incremental synchronization. For CDC-specific restrictions, see General data restrictions for SQL Replication.

• Data log retention requirements: If logs are not retained long enough, DTS may fail to read them, causing task failure or data loss. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service reliability or performance stated in the SLA of DTS cannot be guaranteed.

  • Incremental synchronization only: retain logs for more than 24 hours.

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

• During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database. However, during full data synchronization and incremental data synchronization, DTS temporarily disables the constraint check and cascade operations on foreign keys at the session level. If you perform cascade update and delete operations on the source database during data synchronization, data inconsistency may occur.

  During schema synchronization and incremental data synchronization, the foreign keys of the source database are not synchronized to the destination database.

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

• DTS calculates synchronization latency based on the timestamp of the latest synchronized record in the destination versus the current source timestamp. If no DML operations run on the source for an extended period, the reported latency may be inaccurate. Run a DML operation on the source to refresh the latency reading. If you selected an entire database as the synchronization object, create a heartbeat table that receives data every second to keep latency accurate.

Step 1: Go to the Data Synchronization Tasks page

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

2. In the top navigation bar, click DTS.

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

The navigation steps may differ based on your DMS console mode and layout. See Simple mode and Customize the layout and style of the DMS console. Alternatively, go 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 synchronization instance resides.

In the new DTS console, select the region in the top navigation bar.

Step 3: Create a task

Click Create Task to open the task configuration page.

If the page shows a New Configuration Page button in the upper-right corner, click it to switch to the new version. Skip this if Back to Previous Version is shown instead.

Step 4: Configure source and destination databases

Step 5: Test connectivity

Click Test Connectivity and Proceed at the bottom of the page.

DTS automatically adds its server CIDR blocks to the whitelist of Alibaba Cloud database instances or the security group rules of ECS-hosted databases. For self-managed databases in data centers or hosted by third-party providers, manually add DTS server CIDR blocks to the database whitelist. See Add the CIDR blocks of DTS servers.

Step 6: Configure objects to synchronize

Configure synchronization types and objects:

Parameter	Description
Synchronization Types	Incremental Data Synchronization is selected by default. Also select Schema Synchronization and Full Data Synchronization to synchronize historical data as the baseline for incremental sync.
DDL and DML Operations to Be Synchronized	The DML operations to synchronize. See SQL operations that can be synchronized. To configure per-table or per-database SQL operations, right-click an object in Selected Objects and select the operations to synchronize.
Processing Mode of Conflicting Tables	Precheck and Report Errors (default): checks for identical table names in source and destination. If matches exist, the precheck fails and the task cannot start. Use object name mapping to rename destination tables if needed. Ignore Errors and Proceed: skips the check. Warning This may cause data inconsistency. During full sync, conflicting records in the destination are retained; during incremental sync, they are overwritten. If schemas differ, initialization may fail.
Storage Engine Type	Automatically set to Beam. Available only for AnalyticDB for PostgreSQL V7.0.6.6 or later when Schema Synchronization is selected.
Capitalization of Object Names in Destination Instance	The capitalization policy for database, table, and column names in the destination. Default: DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select objects from Source Objects and click the icon to move them to Selected Objects. Only tables are supported — views, triggers, and stored procedures are excluded.
Selected Objects	Right-click an object to rename it in the destination instance. See Map the name of a single object. For bulk renaming, click Batch Edit. See Map multiple object names at a time. To filter data with WHERE conditions, right-click an object and specify conditions. See Specify filter conditions.

Configure advanced settings:

Click Next: Advanced Settings to configure the following parameters.

Configure data verification (optional):

Click Next Step: Data Verification. See Configure a data verification task.

Configure database and table fields (optional):

Click Next: Configure Database and Table Fields to set the Type, Primary Key Column, and Distribution Key for destination tables. This step is available only when Schema Synchronization is selected. Set Definition Status to All to view and modify all tables.

The Primary Key Column supports composite primary keys (multiple columns). At least one Primary Key Column must also be specified as a Distribution Key. See Manage tables and Define table distribution.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this task configuration, hover over the button and click Preview OpenAPI parameters before proceeding.

DTS runs a precheck before starting the task. The task starts only after the precheck passes. If the precheck fails, click View Details next to each failed item, resolve the issue, and click Precheck Again. For alert items that can be ignored, click Confirm Alert Details, then click Ignore > OK > Precheck Again. Ignoring alerts may cause data inconsistency.

Step 8: Purchase the synchronization instance

After Success Rate reaches 100%, click Next: Purchase Instance.

On the buy page, configure the instance:

Read and accept 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. Track its progress there.