1 Understanding What's Supported for DB2 LUW

Oracle GoldenGate supports all DB2 LUW data types, except those listed in Non-Supported DB2 LUW Data Types.

Limitations of Support

Oracle GoldenGate has the following limitations for supporting DB2 LUW data types:

• Oracle GoldenGate supports multi-byte character data types and multi-byte data stored in character columns. Multi-byte data is only supported in a like-to-like configuration. Transformation, filtering, and other types of manipulation are not supported for multi-byte character data.

• BLOB and CLOB columns must have a LOGGED clause in their definitions.

• GRAPHIC and VARGRAPHIC columns must be in a database, where the character set is UTF16. Any other character set causes the Oracle GoldenGate to abend.

• The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.

• Extract fully supports the capture and apply of TIMESTAMP(0) through TIMESTAMP(9). Extract also captures TIMESTAMP(10) through TIMESTAMP(12), but it truncates the data to nanoseconds (maximum of nine digits of fractional time) and issues a warning to the error log. Replicat truncates timestamp data from other sources to nanoseconds when applying it to TIMESTAMP(10) through TIMESTAMP(12) in a DB2 LUW target.

• Oracle GoldenGate supports timestamp data from 0001/01/03:00:00:00 to 9999/12/31:23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the timezone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.

• Oracle GoldenGate does not support the filtering, column mapping, or manipulation of large objects that are larger than 4K. You can use the full Oracle GoldenGate functionality for objects that are 4K or smaller.

• Replication of XML columns between source and target databases with the same character set is supported. If the source and target database character sets are different, then XML replication may fail with a database error because some characters may not be recognized (or valid) in the target database character set.

The non-supported DB2 LUW data types are:

• XMLType

• DECFLOAT

• User-defined types

• Negative dates

The supported objects and operations for DB2 LUW are:

• Oracle GoldenGate supports the maximum number of columns and column size per table that is supported by the database.

• TRUNCATE TABLE for DB2 LUW version 9.7 and later.

• Multi Dimensional Clustered Tables (MDC) for DB2 LUW 9.5 and later.

• Materialized Query Tables. Oracle GoldenGate does not replicate the MQT itself, but only the base tables. The target database automatically maintains the content of the MQT based on the changes that are applied to the base tables by Replicat.

• Tables with ROW COMPRESSION. In DB2 LUW version 10.1 and later, the optionsCOMPRESS YES STATIC and COMPRESS YES ADAPTIVE are supported. To support the use of COMPRESS YES in DB2 LUW versions 9.7 and earlier, you must use the TRANLOGOPTIONS parameter with the ALLOWTABLECOMPRESSION option, and the compressed table must not contain LOBs.

• The extended row size feature is enabled by default. It is supported with a workaround by using the FETCHCOLS option. For any column values that are VARCHAR or VARGRAPHIC data types and are stored out of row in the database, you must fetch these extended rows by specifying these columns using the FETCHCOLS option in the TABLE parameter in the Extract parameter file. With this option set, when the column values are out of row then Oracle GoldenGate will fetch the column value. If the value is out of row and FETCHCOLS is not specified then the Extract process abends to prevent any data loss. If you do not want to use this feature, set the extended_row_size parameter to DISABLE.

• Temporal tables with DB2 LUW 10.1 FixPack 2 and greater are supported. This is the default for the Replicat process.

• Limitations on Automatic Heartbeat Table support are as follows:

  • Oracle GoldenGate heartbeat parameters frequency and purge frequency are accepted in seconds and days. However, the DB2 LUW task scheduler accepts its schedule only in the cron format so the Oracle GoldenGate input value to the cron format may result in some loss of accuracy. For example:

    ADD HEARTBEATTABLE, FREQUENCY 150, PURGE_FREQUENCY 20
    

    This example sets the FREQUENCY to 150 seconds, which is converted to the closest minute value of 2 minutes, so the heartbeat table is updated every 120 seconds instead of every 150 seconds. Setting PURGE_FREQUENCY to 20 means that the history table is purged at midnight on every 20th day.

  • The following are steps are necessary for the heartbeat scheduled tasks to run:

    1. Set the DB2_ATS_ENABLE registry variable to db2set DB2_ATS_ENABLE=YES.

    2. Create the SYSTOOLSPACE tablespace if it does not already exist:

       CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE 
       EXTENTSIZE 4 
       

    3. Ensure instance owner has Database administration authority (DBADM):

       GRANT DBADM ON DATABASE TO instance_owner_name

Objects and operations for DB2 LUW that are not supported by Oracle GoldenGate are:

• Schema, table or column names that have trailing spaces

• Multiple instances of a database

• Datalinks

• Extraction or replication of DDL (data definition language) operations

• Generated columns (GENERATE ALWAYS clause)

  Note:

For a list of characters that are supported in object names, see Supported Database Object Names in Administering Oracle GoldenGate.