Creating point-in-time tables for Q Replication

You can create point-in-time target tables or nicknames that contain a copy of the source data and an additional column that holds the timestamp of approximately when the source row was changed.

About this task

The Q Apply program populates the additional column, IBMSNAP_LOGMARKER, with timestamp information each time it inserts or updates a row. During the initial loading of the target table, a null value is entered in the timestamp column.

Restrictions

The Q Apply program must be at Version 9.7 Fix Pack 4 or higher on Linux, UNIX, and Windows or Version 10.1 on z/OS® (ARCH_LEVEL 100Z) with the PTF that corresponds to V9.7 Fix Pack 4.
Point-in-time tables are supported for unidirectional replication only.

Procedure

To create point-in-time tables, use the ASNCLP command-line program. When specifying the target table type, follow these guidelines to determine which ASNCLP keywords to use in the CREATE QSUB command:

USERTABLE WITH LOGMARKER: For both DB2® targets and federated targets if the target nickname does not exist.
NICKNAME WITH LOGMARKER: For federated targets if the target nickname already exists.

If you want replication to create the target table or nickname, the IBMSNAP_LOGMARKER column is automatically added to hold the source timestamp. The column has a data type of TIMESTAMP and is nullable with a default value of NULL.

You can also use an existing target table or nickname but must add the IBMSNAP_LOGMARKER column.

The following ASNCLP example sets the environment and creates a Q subscription to a point-in-time table. A target table named TGTEMPLOYEE is created with the additional IBMSNAP_LOGMARKER column.

ASNCLP SESSION SET TO Q REPLICATION;
SET SERVER CAPTURE TO DB SAMPLE;
SET SERVER TARGET TO DB TARGET;
CREATE QSUB USING REPLQMAP REPQMAP1
(SUBNAME SUB1 EMPLOYEE TYPE USERTABLE WITH LOGMARKER);

Note: If the Q Capture program is running with compatibility=1001 or higher, Q Capture sends the timestamp that is inserted into the IBMSNAP_LOGMARKER column in both Greenwich mean time (GMT) and the Q Capture local time. You can start the Q Apply program with the logmarkertz parameter to specify that the timestamp use either GMT or Q Capture local time.

Point-in-time sources

You can use a Q Replication point-in-time target table as a source table for another Q Replication target database. Typically, this configuration uses three tiers, where the log record timestamp from the source database at Tier 1 is sent to the Q Apply program at Tier 2 and applied to the IBMSNAP_LOGMARKER column in a target point-in-time table. The point-in-time table at Tier 2 is then mapped to a point-in-time table at Tier 3, and the original value from Tier 1 is then propagated to Tier 3.

To set up a three-tier configuration like this, use the ASNCLP program to create a Q subscription from Tier 1 to Tier 2. In the CREATE QSUB command, use the WITH LOGMARKER keywords to specify that the IBMSNAP_LOGMARKER column in the target CCD table at Tier 2 will be populated directly from the recovery log at Tier 1. Then, when you create the Q subscription from Tier 2 to Tier 3, omit the WITH LOGMARKER keywords in the CREATE QSUB command so that the IBMSNAP_LOGMARKER column at Tier 2 is mapped to a matching column at Tier 3.

The following examples of CREATE QSUB commands illustrate this method. The first example uses the WITH LOGMARKER keywords to create a Q subscription from Tier 1 to Tier 2:

CREATE QSUB USING TIER1_ASN_TO_TIER2_ASN
(SUBNAME EMPLOYEE0001 HR.EMPLOYEE
 TARGET NAME HR.TIER2EMPLOYEE
 TYPE USERTABLE WITH LOGMARKER
 KEYS (C1));

The second example omits the WITH LOGMARKER keywords. This syntax prompts the Q Apply program to create a table at Tier 3 that also includes the IBMSNAP_LOGMARKER column and then propagate the value in the Tier 2 CCD table to the matching IBMSNAP_LOGMARKER column at Tier 3:

CREATE QSUB USING TIER2_ASN_TO_TIER3_ASN
(SUBNAME EMPLOYEE0001 HR.TIER2EMPLOYEE
 TARGET NAME HR.TIER3EMPLOYEE
 TYPE USERTABLE
 KEYS (C1));

Using a Q Replication point-in-time table as a source requires Version 9.7 Fix Pack 5 or later on Linux, UNIX, and Windows and Version 10.1 on z/OS with the PTF that corresponds to Fix Pack 5.