Using IBM Db2 for Linux, Unix, Windows, and Amazon RDS database (Db2 LUW) as a source for Amazon DMS

Set the database to be recoverable, which Amazon DMS requires to capture changes. A database is recoverable if either or both of the database configuration parameters LOGARCHMETH1 and LOGARCHMETH2 are set to ON.

If your database is recoverable, then Amazon DMS can access the Db2 ARCHIVE LOG if needed.

Ensure that the DB2 transaction logs are available, with a sufficient retention period to be processed by Amazon DMS.

DB2 requires SYSADM or DBADM authorization to extract transaction log records. Grant the user account the following permissions:

When using IBM DB2 for LUW version 9.7 as a source, set the extra connection attribute (ECA), CurrentLSN as follows:

CurrentLSN=LSN where LSN specifies a log sequence number (LSN) where you want the replication to start. Or, CurrentLSN=scan.

When using Amazon RDS for Db2 LUW as a source, ensure that the archive logs are available to Amazon DMS. Because Amazon-managed Db2 databases purge the archive logs as soon as possible, you should increase the length of time that the logs remain available. For example, to increase log retention to 24 hours, run the following command:

db2 "call rdsadmin.set_archive_log_retention( ?, 'TESTDB', '24')"

For more information about Amazon RDS for Db2 LUW procedures, see the Amazon RDS for Db2 stored procedure reference in the Amazon Relational Database Service User Guide.

Grant the following privileges if you use DB2 specific premigration assessments:

GRANT CONNECT ON DATABASE TO USER <DMS_USER>;
GRANT SELECT ON SYSIBM.SYSDUMMY1 TO USER <DMS_USER>;
GRANT SELECT ON SYSIBMADM.ENV_INST_INFO TO USER <DMS_USER>;
GRANT SELECT ON SYSIBMADM.DBCFG TO USER <DMS_USER>;
GRANT SELECT ON SYSCAT.SCHEMATA TO USER <DMS_USER>;
GRANT SELECT ON SYSCAT.COLUMNS TO USER <DMS_USER>;
GRANT SELECT ON SYSCAT.TABLES TO USER <DMS_USER>;
GRANT EXECUTE ON FUNCTION SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID TO <DMS_USER>;
GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER <DMS_USER>;

When a table with multiple partitions is truncated, the number of DDL events shown in the Amazon DMS console is equal to the number of partitions. This is because Db2 LUW records a separate DDL for each partition.

The following DDL actions aren't supported on partitioned tables:

Amazon DMS doesn't support an ongoing replication migration from a DB2 high availability disaster recovery (HADR) standby instance. The standby is inaccessible.

The DECFLOAT data type isn't supported. Consequently, changes to DECFLOAT columns are ignored during ongoing replication.

The RENAME COLUMN statement isn't supported.

When performing updates to Multi-Dimensional Clustering (MDC) tables, each update is shown in the Amazon DMS console as INSERT + DELETE.

When the task setting Include LOB columns in replication isn't enabled, any table that has LOB columns is suspended during ongoing replication.

For Db2 LUW versions 10.5 and higher, variable-length string columns with data that is stored out-of-row are ignored. This limitation only applies to tables created with extended row size for columns with data types like VARCHAR and VARGRAPHIC. To work around this limitation, move the table to a table space with a higher page size. For more information, see What can I do if I want to change the pagesize of DB2 tablespaces.

For ongoing replication, DMS doesn't support migrating data loaded at the page level by the DB2 LOAD utility. Instead, use the IMPORT utility which uses SQL inserts. For more information, see differences between the import and load utilities.

While a replication task is running, DMS captures CREATE TABLE DDLs only if the tables were created with the DATA CAPTURE CHANGE attribute.

DMS has the following limitations when using the Db2 Database Partition Feature (DPF):

For ongoing replication (CDC), if you plan to start replication from a specific timestamp, you must set the StartFromContext connection attribute to the required timestamp.

Currently, DMS doesn't support the Db2 pureScale Feature, an extension of DB2 LUW that you can use to scale your database solution.

The DATA CAPTURE CHANGES table option is a crucial prerequisite for DB2 data replication processes. Neglecting to enable this option when creating tables can cause missing data, especially for CDC (Change Data Capture) only replication tasks initiated from an earlier starting point. Amazon DMS will enable this attribute by default when restarting a CDC or FULL+CDC task. However, any changes made in the source database before the task restart may be missed.

ALTER TABLE TABLE_SCHEMA.TABLE_NAME DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;