Using an IBM Db2 for Linux, Unix, and Windows Database (Db2 LUW) as a Source for AWS DMS
You can migrate data from an IBM Db2 for Linux, Unix, and Windows (Db2 LUW) database to any supported target database using AWS DMS (AWS DMS). AWS DMS supports as a migration source the following Db2 LUW versions:
-
Version 9.7, all Fix Packs are supported.
-
Version 10.1, all Fix Packs are supported.
-
Version 10.5, all Fix Packs except for Fix Pack 5 are supported.
You can use SSL to encrypt connections between your Db2 LUW endpoint and the replication instance. You must be using AWS DMS engine version 2.4.2 or higher to use SSL. For more information on using SSL with a Db2 LUW endpoint, see Using SSL With AWS Database Migration Service.
Prerequisites When Using Db2 LUW as a Source for AWS DMS
The following prerequisites are required before you can use an Db2 LUW database as a source.
To enable ongoing replication, also called change data capture (CDC), you must do the following
-
The database must be set to be recoverable. To capture changes, AWS DMS requires that the database is configured to be recoverable. A database is recoverable if either or both of the database configuration parameters LOGARCHMETH1 and LOGARCHMETH2 are set to ON.
-
The user account must be granted the following permissions:
SYSADM or DBADM
DATAACCESS
Limitations When Using Db2 LUW as a Source for AWS DMS
Clustered databases are not supported. Note, however, that you can define a separate Db2 LUW for each of the endpoints of a cluster. See the IBM Db2 LUW documentation for more information.
When using ongoing replication (CDC), the following limitations apply:
-
When truncating a table with multiple partitions, the number of DDL events shown in the AWS DMS console will be equal to the number of partitions. This is because Db2 LUW records a separate DDL for each partition.
-
The following DDL actions are not supported on partitioned tables:
-
ALTER TABLE ADD PARTITION
-
ALTER TABLE DETACH PARTITION
-
ALTER TABLE ATTACH PARTITION
-
-
The DECFLOAT data type is not supported. Consequently, changes to DECFLOAT columns are ignored during ongoing replication.
-
The RENAME COLUMN statement is not supported.
-
When performing updates to MDC (Multi-Dimensional Clustering) tables, each update is shown in the AWS DMS console as INSERT + DELETE.
-
When the task setting Include LOB columns in replication is disabled, any table that has LOB columns is suspended during ongoing replication.
-
When the Audit table option is enabled, the first timestamp record in the audit table will be NULL.
-
When the Change table option is enabled, the first timestamp record in the table will be zero (i.e. 1970-01-01 00:00:00.000000).
-
For Db2 LUW versions 10.5 and higher: Variable-length string columns with data that is stored out-of-row is ignored. Note that this limitation is only applicable to tables created with extended row size.
Extra Connection Attributes When Using Db2 LUW as a Source for AWS DMS
You can use extra connection attributes to configure your Db2 LUW source. You specify these settings when you create the source endpoint. If you have multiple connection attribute settings, separate them from each other by semicolons with no additional white space.
The following table shows the extra connection attributes you can use with Db2 LUW as a source:
| Name | Description |
|---|---|
|
|
Maximum number of bytes per read, as a NUMBER value. The default is 64 KB. |
|
|
Enables ongoing replication (change data capture), as a BOOLEAN value. The default is true. |
Source Data Types for IBM Db2 LUW
Data migration that uses Db2 LUW as a source for AWS DMS supports most Db2 LUW data types. The following table shows the Db2 LUW source data types that are supported when using AWS DMS and the default mapping from AWS DMS data types. For more information about Db2 LUW data types, see the Db2 LUW documentation.
For information on how to view the data type that is mapped in the target, see the section for the target endpoint you are using.
For additional information about AWS DMS data types, see Data Types for AWS Database Migration Service.
|
Db2 LUW Data Types |
AWS DMS Data Types |
|---|---|
|
INTEGER |
INT4 |
|
SMALLINT |
INT2 |
|
BIGINT |
INT8 |
|
DECIMAL (p,s) |
NUMERIC (p,s) |
|
FLOAT |
REAL8 |
|
DOUBLE |
REAL8 |
|
REAL |
REAL4 |
|
DECFLOAT (p) |
If precision = 16, then: REAL8 If precision is = 34, then: STRING |
|
GRAPHIC |
WSTRING n<=127 |
|
VARGRAPHIC |
WSTRING n<=16k double byte chars |
|
LONG VARGRAPHIC |
CLOB |
|
CHAR (n) |
STRING n<=255 |
|
VARCHAR (n) |
STRING n<=32k |
|
LONG VARCHAR (n) |
CLOB n<=32k |
|
CHAR (n) FOR BIT DATA |
BYTES |
|
VARCHAR (n) FOR BIT DATA |
BYTES |
|
LONG VARCHAR FOR BIT DATA |
BYTES |
|
DATE |
DATE |
|
TIME |
TIME |
|
TIMESTAMP |
DATETIME |
|
BLOB |
BLOB |
|
CLOB |
CLOB Maximum size: 2 GB |
|
DBCLOB |
CLOB Maximum size: 1 G double byte chars |
|
XML |
CLOB |
