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);