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
andCLOB
columns must have aLOGGED
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
andVARGRAPHIC
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)
throughTIMESTAMP(12)
when the output trail format is 19.1 or higher. Otherwise Extract truncates data fromTIMESTAMP(10)
throughTIMESTAMP(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.
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 andCOMPRESS 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 areVARCHAR
orVARGRAPHIC
data types and are stored out of row in the database, you must fetch these extended rows by specifying these columns using theFETCHCOLS
option in theTABLE
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 andFETCHCOLS
is not specified then Extract will abend to prevent any data loss. If you do not want to use this feature, set theextended_row_size
parameter toDISABLE
.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 areVARCHAR
orVARGRAPHIC
data types and are stored out of row in the database, you must fetch these extended rows by specifying these columns using theFETCHCOLS
option in theTABLE
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 andFETCHCOLS
is not specified then Extract abends to prevent any data loss. If you do not want to use this feature, set theextended_row_size parameter
toDISABLE
. 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 withSHOWTRANS
commandSHOWTRANS [transaction_ID] [COUNT n] [DURATION duration unit] [TABULAR][FILE file_name] |
Options withSKIPTRANS
andFORCETRANS
.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. SettingPURGE_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:
-
Set the
DB2_ATS_ENABLE
registry variable todb2set DB2_ATS_ENABLE=YES
. -
Create the
SYSTOOLSPACE
tablespace if it does not already exist:CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4
-
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, see Supported Database Object Names in Administering Oracle GoldenGate.