4 Understanding What's Supported for IBM DB2 for i

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

Oracle GoldenGate does not support the following DB2 for i data types:

• XML

• DATALINK

• DECFLOAT

• User-defined types

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 no WHERE clause SQL statements and Clear 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

• Limitations on Automatic Heartbeat Table support are as follows:

  • The ADD HEARTBEATTABLE command creates a new file called ogghbfreq¿ in the Oracle GoldenGate build folder. Do not delete this file because the pase 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 the ADD HEARTBEATTABLE command is given and runs until you disable the heartbeat with the DELETE HEARTBEATTABLE command. This process automatically restarts even if it is killed. To remove this process from the system, use the DELETE HEARTBEATTABLE command.

  • When using the ALTER HEARTBEATTABLE command to change the heartbeat frequency with the PURGE_FREQUENCY or RETENTION_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.

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.

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 and EBCDICTOASCII
• BINARYCHARS
• LOBMEMORY
• TRAILCHARSETEBCDIC
• Any encryption options that use AES encryption

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.

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

When COLCHARSET PASSTHRU is provided for a particular column, that column is not converted into Unicode, and its source character set is provided in the trail metadata, so that Replicat can correctly process the trail data in the native character set of the column improving performance on the source side. To enable the trail to contain multiple character sets, the columns should be configured in PASSTRHU mode. For example:

TABLE GGSCHEMA.TABLE1, CHARSET(PASSTHRU);  
TABLE GGSCHEMA.TABLE2, COLCHARSET(PASSTHRU, COL1, COL2, COL3);

Where COL1, COL2, COL3 are CHAR or VARCHAR fields with a valid CCSID.

TABLE GGSCHEMA.TABLE3, COLCHARSET(IBM037, COL1, COL2);

Where COL1 and COL2 are CHAR or VARCHAR fields with CCSID 65535.

You can set a column character override for any CHAR, CLOB, or VARCHAR column with a valid character set (not CCSID 655350 when the data contained in the column has a different character set than what you intended. For example, if there is a CHAR column with CCSID set as 1047, and the data contained in it is actually in CCSID 37, you can set a column override CCSID for the column in the Extract or DEFGEN parameter file so that when it processed, the processes recognize that the data is actually in CCSID 37 and not in CCSID 1047. The data is treated as CCSID 37, but converted to UTF8.

You cannot override the CHARSET and use PASSTHRU together. For example:

TABLE GGSCHEMA.TABLE4, COLCHARSET(IBM037, COL1);

Where COL1 is a CHAR or VARCHAR field defined with CCSID 1047 and the data is actually in CCSID 37.

If the column character set of a source column is not a valid character set supported by Oracle GoldenGate ICU and you specify COLCHAR SETPASSTHRU in the Extract or DEFGEN parameter file, then the PASSTHRU behavior is ignored, and the column data is converted to Unicode. This ensures that the data is convertible on the Replicat. Since the data has no ICU representation, there is no way to indicate what character set the data is really in.