Supported Objects and Operations for DB2 LUW

Object and operations that are supported for DB2 LUW are:

  • Oracle GoldenGate Extract supports cross-endian capture where the database and Oracle GoldenGate are running on different byte order servers. The byte order is detected automatically for DB2 LUW version 10.5 or higher. If the DB2 database auto-detection on the DB2 LUW 10.5 database is not required then you can override it by specifying the TRANLOGOPTIONS MIXEDENDIAN [ON|OFF] parameter. For DB2 LUW version 10.1, this parameter must be used in the Extract parameter file for cross-endian capture. See TRANLOGOPTIONS in Parameters and Functions Reference for Oracle GoldenGate.

  • DB2 pureScale environment is supported.

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

  • TRUNCATE TABLE.

  • Multi-Dimensional Clustered Tables (MDC).

  • 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, COMPRESS YES STATIC is supported and COMPRESS YES ADAPTIVE are supported.

  • Extended row size feature is enabled by default. It is supported with a workaround using FETCHCOLS. 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 its value. If the value is out of and FETCHCOLS is not specified then Extract will abend to prevent any data loss. If you do not want to use this feature, set the extended_row_size parameter to DISABLE.

    Extended row size feature is enabled, by default. It is supported with a workaround using FETCHCOLS for DB2 LUW 10.1. 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 fetches its value. If the value is out of and FETCHCOLS is not specified then Extract abends to prevent any data loss. If you do not want to use this feature, set the extended_row_size parameter to DISABLE. For DB2 LUW 10.5 and higher out of row values are captured seamlessly by Extract. FETCHCOLS is no more needed to capture out of row columns from these database versions.

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

  • Supported options with SHOWTRANS
    SHOWTRANS [transaction_ID]  [COUNT n] 
[DURATION duration unit] 
[TABULAR][FILE file_name] |
  • Options with SKIPTRANS and FORCETRANS.
    SKIPTRANS transaction_ID  
[FORCE] FORCETRANS transaction_ID  [FORCE]

  • Limitations on Automatic Heartbeat Table support are as follows:

    • [THREAD n] [DETAIL] is not supported.

    • 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 cron format so the Oracle GoldenGate input value to 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