Prepare Tables for Processing

Disabling Triggers and Cascade Constraints

Disable triggers, cascade delete constraints, and cascade update constraints on target Db2 LUW tables. 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 is 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 cannot be located because it was already deleted in step 5.

Ensuring Row Uniqueness for Tables

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

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

Primary key
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
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 are not supported by Oracle GoldenGate 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 if there are no keys at all 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 larger, less efficient WHERE clause.
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 parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Parameters and Functions 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 parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds.

Preventing Key Changes

Do not add columns to a key after Oracle GoldenGate starts extracting data from the table. This rule applies to a primary key, a unique key, a KEYCOLS key, or an all-column key. Db2 LUW does not supply a before image for columns that are added to a table. If any columns in a key are updated on the source, Oracle GoldenGate needs a before image to compare with the current values in the target table when it replicates the update.

Maintaining Materialized Query Tables

To maintain parity between source and target materialized query tables (MQT), you replicate the base tables, but not the MQTs. The target database maintains the MQTs based on the changes that Replicat applies to the base tables.

The following are the rules for configuring these tables:

Include the base tables in your TABLE and MAP statements.
Do not include MQTs in the TABLE and MAP statements.
Wildcards can be used in TABLE and MAP statements, even though they might resolve MQT names along with regular table names. Oracle GoldenGate automatically excludes MQTs from wildcarded table lists. However, any MQT that is explicitly listed in an Extract TABLE statement by name will cause Extract to abend.

Creating a Temporal Table

A temporal table is a table that maintains the history of its data and the time period when its data are valid. Temporal tables are used in Oracle GoldenGate to keep track of all the old rows that are deleted or updated in the table. Temporal tables are also used to maintain the business validity of its rows and data. For example, Oracle GoldenGate keeps track of the time period during which a row is valid. There are three types of temporal tables, system-period, application-period, and bitemporal table.

Convert to a Temporal Table

You can convert an already existing table into a temporal table, which changes the structure of the table. This section describes how the structure of the tables changes. The following sample existing table is converted into all three temporal tables types in the examples in this section:.

Table policy_info
(
Policy_id char[4] not null primary key,
Coverage int not null
              )
And the tables contains the following initial rows
                   POLICY_ID       COVERAGE
                   -------------        -----------
                     ABC                12000
                     DEF                 13000
                     ERT                 14000

Example 1 Converting an existing table into System-period temporal table.

You convert the sample existing table into a system-period temporal table by adding SYSTEM_PERIOD, transaction id columns, and SYSTEM_TIME period as in the following:

ALTER TABLE policy_info
   ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN;
ALTER TABLE policy_info 
   ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END;
ALTER TABLE policy_info
   ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID;
ALTER TABLE policy_info ADD PERIOD SYSTEM_TIME(sys_start, sys_end);

Then you create a history table for the new temporal table using one of the following two methods:

	CREATE TABLE hist_policy_info
(
 policy_id     CHAR(4) NOT NULL,
coverage     INT NOT NULL,
sys_start    TIMESTAMP(12) NOT NULL ,
sys_end      TIMESTAMP(12) NOT NULL,
ts_id            TIMESTAMP(12) NOT NULL
 	       );
 ALTER TABLE hist_policy_info ADD RESTRICT ON DROP;

```
CREATE TABLE hist_policy_info LIKE policy_info with RESTRICT ON DROP;
```
The RESTRICT ON DROP clause will not allow the history table to get dropped while dropping system-period temporal table. Otherwise the history table gets implicitly dropped while dropping its associated temporal table. You can create a history table without RESTRICT ON DROP. A history table cannot be explicitly dropped.

You should not use the GENERATED ALWAYS clause while creating a history table. The primary key of the system-period temporal table also does not apply here as there could be many updates for a particular row in the base table, which triggers many inserts into the history table for the same set of primary keys. Apart from these, the structure of a history table should be exactly same as its associated system-period temporal table. The history table must have the same number and order of columns as system-period temporal table. History table columns cannot explicitly be added, dropped, or changed. You must associate a system-period temporal table with its history table with the following statement:
```
 ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info.
```
The GENERATED ALWAYS columns of the table are the ones that are always populated by the database manager so you do not have any control over these columns. The database manager populates these columns based on the system time.

The extra added SYSTEM_PERIOD and transaction id columns will have default values for already existing rows as in the following:
```
POLICY_ID                                   COVERAGE    SYS_START                                         SYS_END                                                TS_ID
--------- ----------- -------------------------------- -------------------------------- -------------------------------------------------------------------------------
ABC             12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
DEF             13000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
ERT             14000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
```
The associated history table is populated with the before images once you start updating the temporal table.

Example 2 Converting an existing table into application-period temporal table.

You can convert the sample existing table into application-period temporal table by adding time columns and a BUSINESS_TIME period as in the following:

ALTER TABLE policy_info ADD COLUMN bus_start DATE NOT NULL DEFAULT '10/10/2001'"
ALTER TABLE policy_info ADD COLUMN bus_end DATE NOT NULL DEFAULT '10/10/2002'
ALTER TABLE policy_info ADD PERIOD BUSINESS_TIME(bus_start, bus_end)

While adding time columns, you need to make sure that while entering business validity time values of the existing time columns, the bus_start column always has value lesser than bus_end because these columns specify the business validity of the rows.

The new application-period temporal table will look similar to:

POLICY_ID   COVERAGE    BUS_START  BUS_END
--------- ----------- ---------- -------------------------------
ERT             14000              10/10/2001  10/10/2002
DEF             13000             10/10/2001   10/10/2002
ABC             12000             10/10/2001   10/10/2002

Example 3 Converting an existing table into bitemporal table.

You can convert the sample existing table into bitemporal table by adding SYSTEM_PERIOD, time columns along with the SYSTEM_TIME and BUSINESS_TIME period as in the following:

ALTER TABLE policy_info
   ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN;
ALTER TABLE policy_info 
   ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END;
ALTER TABLE policy_info
   ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID;
ALTER TABLE policy_info ADD PERIOD SYSTEM_TIME(sys_start, sys_end);
 
ALTER TABLE policy_info ADD COLUMN bus_start DATE NOT NULL DEFAULT '10/10/2001'"
ALTER TABLE policy_info ADD COLUMN bus_end DATE NOT NULL DEFAULT '10/10/2002'
ALTER TABLE policy_info ADD PERIOD BUSINESS_TIME(bus_start, bus_end)

While adding the time columns, you must make sure that while entering business validity time values of already existing time columns, the bus_start column always has value lesser than bus_end because these columns specify the business validity of the rows.

Then you create a history table for the new temporal table using one of the following two methods:

	CREATE TABLE hist_policy_info
(
 policy_id     CHAR(4) NOT NULL,
coverage     INT NOT NULL,
sys_start    TIMESTAMP(12) NOT NULL ,
sys_end      TIMESTAMP(12) NOT NULL,
ts_id            TIMESTAMP(12) NOT NULL
 	       );
 ALTER TABLE hist_policy_info ADD RESTRICT ON DROP;
CREATE TABLE hist_policy_info LIKE policy_info with RESTRICT ON DROP;

The RESTRICT ON DROP clause will not allow the history table to get dropped while dropping system-period temporal table. Otherwise the history table gets implicitly dropped while dropping its associated temporal table. You can create a history table without RESTRICT ON DROP. A history table cannot be explicitly dropped.

You should not use the GENERATED ALWAYS clause while creating a history table. The primary key of the system-period temporal table also does not apply here as there could be many updates for a particular row in the base table, which triggers many inserts into the history table for the same set of primary keys. Apart from these, the structure of a history table should be exactly same as its associated system-period temporal table. The history table must have the same number and order of columns as system-period temporal table. History table columns cannot explicitly be added, dropped, or changed. You must associate a system-period temporal table with its history table with the following statement:
```
 ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info.
```
The GENERATED ALWAYS columns of the table are the ones that are always populated by the database manager so you do not have any control over these columns. The database manager populates these columns based on the system time.

The extra added SYSTEM_PERIOD and transaction id columns will have default values for already existing rows as in the following:
```
POLICY_ID                                   COVERAGE    SYS_START                                         SYS_END                                                TS_ID
--------- ----------- -------------------------------- -------------------------------- -------------------------------------------------------------------------------
ABC             12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
DEF             13000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
ERT             14000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000
```
The associated history table is populated with the before images once you start updating the temporal table.

The extra added SYSTEM_TIME period, transaction id and time columns will have default values for already existing rows as in the following:

POLICY_ID COVERAGE    SYS_START                        SYS_END                          TS_ID                            BUS_START         BUS_END
--------- ----------- -------------------------------- -------------------------------- -------------------------------- ---------- -------------------------------------
ABC   12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000 10/10/2001 10/10/2002
DEF             13000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000 10/10/2001 10/10/2002
ERT             14000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000 10/10/2001 10/10/2002

The history table is populated with the before images once user starts updating the temporal table.

Example 4 Replication in Heterogeneous Environment.

In heterogeneous configuration in which you do not have temporal tables at the apply side, you can only replicate the system-period and bitemporal tables though not the associated history tables. While performing replication in this situation, you must take care of the SYSTEM_PERIOD and transaction id columns value. These columns will have some values that the target database might not support. You should first use the map conversion functions to convert these values into the format that the target database supports, and then map the columns accordingly.

For example, MySQL has a DATETIME range from 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999. You cannot replicate a timestamp value of 0001-01-01-00.00.00.000000000000 to MySQL. To replicate such values, you must convert this value into the MySQL DATETIME value 1000-01-01 00:00:00.000000, and then map the columns. If you have the following row in the policy_info system-period table:

POLICY_ID                                   COVERAGE    SYS_START                                         SYS_END                                                TS_ID
--------- ----------- -------------------------------- -------------------------------- -------------------------------------------------------------------------------
ABC             12000 0001-01-01-00.00.00.000000000000 9999-12-30-00.00.00.000000000000 0001-01-01-00.00.00.000000000000

To replicate the row into MySQL, you would use the colmap() function:

map source_schema.policy_info, target target_schema.policy_info colmap
(policy_id=policy_id, coverage=coverage, sys_start= @IF( ( @NUMSTR( @STREXT(sys_
start,1,4))) > 1000, sys_start, '1000-01-01 00.00.00.000000'), sys_end=sys_end,
 ts_id= @IF( ( @NUMSTR( @STREXT(ts_id,1,4))) > 1000, ts_id, '1000-01-01
 00.00.00.000000'));

Replicating with Temporal Tables

You can choose one of the following methods to replicate a system-period or a bitemporal temporal table as follows:

You can replicate a temporal table to another temporal table only; this is the default behavior. Oracle GoldenGate will not replicate the SYSTEM_TIME period and transaction id columns because these are automatically generated columns at the apply side. The database manager populates the columns in the target temporal table using the system clock time and with the default values. You can preserve the original values these columns then use any of the following:
- Add extra timestamp columns in the target temporal table and map the columns accordingly. The extra columns are automatically added to the associated history table.
- Use a non-temporal table at the apply side and map the columns appropriately. In this scenario, you will not be able to maintain the history table.
- In a heterogeneous configuration where the source is Db2 LUW and the target is a different database, you can either ignore the automatically generated columns or use an appropriate column conversion function to convert the columns value in the format that target database supports and map them to target columns accordingly.
Or
You can replicate a temporal table, with the associated history table, to a temporal and history table respectively then you must specify the replicate parameter, DBOPTIONS SUPPRESSTEMPORALUPDATES. You must specify both the temporal table and history table to be captured in the Extract parameter file. Oracle GoldenGate replicates the SYSTEM_TIME period and transactions id columns value. You must ensure that the database instance has the execute permission to run the stored procedure at the apply side.

Oracle GoldenGate cannot detect and resolve conflicts while using default replication as SYSTEM_TIME period and transactionstart id columns remains auto generated. These columns cannot be specified in set and where clause. If you use the SUPPRESSTEMPORALUPDATES parameter, then Oracle GoldenGate supports CDR.