4 Understanding What's Supported for DB2 for i
This chapter contains information on database and table features supported by Oracle GoldenGate for DB2 for i.
Oracle GoldenGate on DB2 for i supports the filtering, mapping, and transformation of data unless otherwise noted in this documentation.
Oracle GoldenGate for DB2 for i runs directly on a DB2 for i source system to capture data from the transaction journals for replication to a target system. To apply data to a target DB2 for i database, Oracle GoldenGate can run directly on the DB2 for i target system or on a remote Windows or Linux system. If installed on a remote system, Replicat delivers the data by means of an ODBC connection, and no Oracle GoldenGate software is installed on the DB2 for i target.
Note:
The DB2 for i platform uses one or more journals to keep a record of transaction change data. For consistency of terminology in the supporting administrative and reference Oracle GoldenGate documentation, the terms "log" or "transaction log" may be used interchangeably with the term "journal" where the use of the term "journal" is not explicitly required.
Topics:
- Supported DB2 for i Data Types
- Non-Supported DB2 for i Data Types
- Supported Objects and Operations for DB2 for i
- Non-Supported Objects and Operations for DB2 for i
- Oracle GoldenGate Parameters Not Supported for DB2 for i
- Supported Object Naming Conventions
- System Schemas for DB2 for i
- Supported Character Sets
Parent topic: Using Oracle GoldenGate for DB2 for i
Supported DB2 for i Data Types
Oracle GoldenGate supports all DB2 for i data types, except those listed in Non-Supported DB2 for i Data Types.
Limitations of support
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.000000 to 9999/12/31:23:59:59.999999. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.
Parent topic: Understanding What's Supported for DB2 for i
Non-Supported DB2 for i Data Types
Oracle GoldenGate does not support the following DB2 for i data types:
-
XML
-
DATALINK
-
User-defined types
Parent topic: Understanding What's Supported for DB2 for i
Supported Objects and Operations for DB2 for i
Oracle GoldenGate supports the following DB2 for i objects and operations.
-
Extraction and replication of DML operations .
-
Tables with the maximum row length supported by the database.
-
Tables that contain up to the maximum number of columns that is supported by the database, up to the maximum supported column size.
-
DELETE FROM
with noWHERE
clause SQL statements andClear Physical File Member
(CLRPFM
) -
Base tables underlying Materialized Query Tables, but not the MQTs themselves. The target database automatically maintains the content of the MQT based on the changes that are applied to the base tables by Replicat.
-
Both Library (Native) names including members, and SQL names are allowed.
-
Partitioned tables
-
Supported options with
SHOWTRANS
:SHOWTRANS [transaction_ID] [COUNT n] [DURATION duration unit] [TABULAR] [FILE file_name] |
-
Options for
SKIPTRANS
andFORCETRANS
:SKIPTRANS transaction_ID [FORCE] FORCETRANS transaction_ID [FORCE]
-
Limitations on Automatic Heartbeat Table support are as follows:
-
The
ADD HEARTBEATTABLE
command creates a new file calledogghbfreq¿
in the Oracle GoldenGate build folder. Do not delete this file because thepase
heartbeat program reads the frequency values from it. -
There is an extra executable in the Oracle GoldenGate build folder named
ogghb
. -
An extra process named
ogghb
starts running from the time theADD HEARTBEATTABLE
command is given and runs until you disable the heartbeat with theDELETE HEARTBEATTABLE
command. This process automatically restarts even if it is killed. To remove this process from the system, use theDELETE HEARTBEATTABLE
command. -
When using the
ALTER HEARTBEATTABLE
command to change the heartbeat frequency with thePURGE_FREQUENCY
orRETENTION_TIME
options, it takes approximately 60 + older 'frequency') seconds to be implemented. -
There is an initial delay of 30 seconds between
ADD HEARTBEATTABLE
and the first record is updated in the heartbeat seed table. -
[THREAD n]
and[DETAIL]
is not supported.
-
Parent topic: Understanding What's Supported for DB2 for i
Non-Supported Objects and Operations for DB2 for i
Oracle GoldenGate does not support the following objects or operations for DB2 for i.
-
DDL operations
-
Schema, table or column names that have trailing spaces.
-
Multiple instances of a database
-
The Multi-Journal feature does not support multi-journal sync of a transaction across multiple journals.
Parent topic: Understanding What's Supported for DB2 for i
Oracle GoldenGate Parameters Not Supported for DB2 for i
This section lists some of the Oracle GoldenGate configuration parameters that are not supported for the DB2 for i platform. For full descriptions of Oracle GoldenGate parameters and the databases they support, see Oracle GoldenGate Parameters.
BATCHSQL
(not supported on V5R4 and i6.1 only)BR
ASCIITOEBCDIC
andEBCDICTOASCII
BINARYCHARS
LOBMEMORY
TRAILCHARSETEBCDIC
- Any encryption options that use AES encryption
Parent topic: Understanding What's Supported for DB2 for i
Supported Object Naming Conventions
Oracle GoldenGate supports SQL naming conventions and also supports native file system names in the format of library/file(member)
.
For native (system) names, Oracle GoldenGate supports the normal DB2 for i naming rules for wildcarding, which allows *ALL
or a partial name with a trailing asterisk (*) wildcard. For example:
-
library
/*all(*all)
-
library
/a*(a*)
-
library
/abcde*
Oracle GoldenGate does not allow wildcarding for library names.
The member name is optional and may be left off. In that case, data for all of the members will be extracted, but only the library and file names will be captured and included in the records that are written to the trail. The result is that the data will appear to have come from only one member on the source, and you should be aware that this could cause integrity conflicts on the target if there are duplicate keys across members. To include the member name in the trail records, include the member explicitly or though a wildcarded member specification.
For SQL names, only the first member in the underlying native file is extracted in accordance with the normal operation of SQL on an DB2 for i system. For SQL names, Oracle GoldenGate supports the wildcarding of table names, but not schema names. For instructions on wildcarding SQL names, see Specifying Object Names in Oracle GoldenGate Input in Administering Oracle GoldenGate.
Parent topic: Understanding What's Supported for DB2 for i
System Schemas for DB2 for i
The following schemas or objects are not automatically replicated by Oracle GoldenGate unless they are explicitly specified without a wildcard..
-
"Q*"
-
"SYSIBM"
-
"SYSIBMADM"
"SYSPROC"
-
"SYSTOOLS"
-
"#LIBRARY"
-
"#RPGLIB"
Parent topic: Understanding What's Supported for DB2 for i
Supported Character Sets
The default behavior of a DB2 for i Extract is to convert all character data to
Unicode. The overhead of the performance of the conversion to UTF-8 for the text data
has been substantially reduced. However, if you want to send data in its native
character set you can use the CHARSET
and COLCHARSET
parameters to override the default behavior as follows. See CHARSET and COLCHARSET in Reference for Oracle GoldenGate.
Parent topic: Understanding What's Supported for DB2 for i