Db2 LUW: Supported Data Types, Objects, and Operations

This sections contains information on supported data types, objects, and operations for Oracle GoldenGate on Db2 LUW.

Supported Db2 LUW Data Types

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.

• Due to limitations in the IBM DB2READLOG interface, Oracle GoldenGate does not support coordination of transactions across nodes in a Db2 Database Partitioning Feature (DPF) environment. In DPF, a transaction may span multiple nodes, depending upon how the data is partitioned.

  However, if you need to capture from it, you can do it with certain limitations. Check the Oracle Support note Does Oracle GoldenGate Support DB2 LUW Data Partitioning Feature (DPF)? (DocID 2763006.1).

• 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(12) when the output trail format is 19.1 or higher. Otherwise Extract truncates data from TIMESTAMP(10) through TIMESTAMP(12) to nanoseconds (maximum of nine digits of fractional time) and issues a warning to the report file.

• 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.

• Oracle GoldenGate does not certify capturing changes from the Db2 LUW column-organized tables.

• The DECFLOAT data type supports the NaN, sNaN, Infinity/-Infinity special numeric values.

  For more information on data types by databases that support special values, refer to Special Values Supported by Database Data Types.

Non-Supported Db2 LUW Data Types

The non-supported Db2 LUW data types are:

• XMLType (Capture)

• User-defined types

• Negative dates

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 for more information.

• 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.

  • Replication between system-period temporal tables and application-period temporal tables is not supported.

  • Replication from a non-temporal table to a temporal table is not supported.

  • Replication of temporal tables with the INSERTALLRECORDS parameter is not supported.

  • Bidirectional replication is supported only with the default replication.

  • CDR in bidirectional replication is not supported.

  • CDR in application-period temporal tables is supported.

• Supported options with SEND EXTRACT SHOWTRANS \ SKIPTRANS \ FORCETRANS command are:

  Supported options with SHOWTRANS command

  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

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

     GRANT DBADM ON DATABASE TO instance_owner_name

Non-Supported Objects and Operations for Db2 LUW

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

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

• Generated columns (GENERATE ALWAYS clause)

• DB2 Data Partitioning Feature (DPF) is not supported. DPF doesn’t provide a way to read the log records in a coordinated fashion across all the nodes in a partition. So, there is no way to ensure that even if all nodes are being replicated with separate Extracts, it would be possible to ensure that all records from all transactions are applied in the correct temporal order. This may occur due to a number of factors including caching in the database and the underlying operating system not allowing the Extracts to have visibility into whether there are any cached and not yet visible entries that may affect the ordering of the record operations across all partitions. Due to this uncertainty, it is not possible to ensure that transactions that span partitions, or primary key updates can be replicated in a consistent manner.

System Schemas

The following schemas or objects are not be automatically replicated by Oracle GoldenGate unless they are explicitly specified without a wildcard.

• "SYSIBM"

• "SYSCAT"

• "SYSSTAT"

• "SYSPROC"

• "SYSFUN"

• "SYSIBMADMIN"

• "SYSTOOLS"

• "SYSPUBLIC"

Supported Object Names

For a list of characters that are supported in object names, seeSupported Database Object Names.