1. Microservices Architecture Documentation
  2. Prepare
  3. Prepare Databases
  4. Db2 z/OS
  5. Prepare Tables for Processing

Prepare Tables for Processing

You must perform the following tasks to prepare your tables for use in an Oracle GoldenGate environment for Db2 z/OS.

Disable Triggers and Cascade Constraints

Disable triggers, cascade delete constraints, and cascade update constraints on the target tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger or cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ.

  • A delete gets issued for emp_src.
  • It cascades a delete to salary_src.
  • Oracle GoldenGate sends both deletes to the target.
  • The parent delete arrives first and is applied to emp_targ.
  • The parent delete cascades a delete to salary_targ.
  • The cascaded delete from salary_src is applied to salary_targ.
  • The row gets deleted in step 5 and cannot be located.

Ensure Row Uniqueness for Tables

Oracle GoldenGate requires a unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause exists in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

  1. Primary key

  2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.

  3. If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that Oracle GoldenGate does not supported in a key or those that are excluded from the Oracle GoldenGate configuration.

    Note:

    If there are other non-usable keys on a table or no keys on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a more extensive, less efficient WHERE clause.

  4. If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE and the Replicat MAP parameters. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.

Using KEYCOLS to Specify a Custom Key

If a table does not have one of the preceding types of row identifiers, or if you prefer those identifiers not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE and the Replicat MAP parameters. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. For more information, see Reference for Oracle GoldenGate.

Handle Tables with ROWID Columns

Any attempt to insert into a target table that includes a column with a data type of ROWID GENERATED ALWAYS (the default) will fail with the following ODBC error:

ODBC error: SQLSTATE 428C9 native database error -798. {DB2 FOR OS/390}{ODBC DRIVER}{DSN08015} DSNT408I SQLCODE = -798, ERROR: YOU CANNOT INSERT A VALUE INTO A COLUMN THAT IS DEFINED WITH THE OPTION GENERATED ALWAYS. COLUMN NAME ROWIDCOL.

You can do one of the following to prepare tables with ROWID columns to be processed by Oracle GoldenGate:

  • Ensure that any ROWID columns in target tables are defined as GENERATED BY DEFAULT.

  • If it is not possible to change the table definition, you can work around it with the following procedure.

To workaround ROWID GENERATE ALWAYS:

  1. For the source table, create an Extract TABLE statement, and use a COLSEXCEPT clause in that statement that excludes the ROWID column. For example:

    TABLE tab1, COLSEXCEPT (rowidcol);

    The COLSEXCEPT clause excludes the ROWID column from being captured and replicated to the target table.

  2. For the target table, ensure that Replicat does not attempt to use the ROWID column as the key. This can be done in one of the following ways:

    • Specify a primary key in the target table definition.

    • If a key cannot be created, create a Replicat MAP parameter for the table, and use a KEYCOLS clause in that statement that contains any unique columns except for the ROWID column. Replicat will use those columns as a key. For example:

      MAP tab1, TARGET tab1, KEYCOLS (num, ckey);