Consistent-change-data (CCD) tables provide committed transactional data that can be read and used by other applications, for example InfoSphere® DataStage® or the Apply program for SQL replication.
By using a CCD table as your target type, you can also keep a history of source changes. For example, you can track before and after comparisons of the data, when changes occurred, and which user ID updated the source table.
You can specify CCD tables as both sources and targets in unidirectional Q Replication. You can also specify a CCD table that is populated by the Q Replication programs as a source for SQL replication. For more details on using Q Replication CCD tables with SQL replication, see Data distribution configurations (Q Replication to SQL replication).
The following sections provide more detail about CCD tables in Q Replication:
Two attributes define a CCD table: condensed and complete. The following list summarizes these attributes:
All target table loading options are valid for complete CCDs (automatic, manual, or no load).
The only valid load option for noncomplete CCD tables is no load.
For condensed CCD tables, a primary key is required to ensure there are no duplicate rows. In case of an update conflict, all the source columns will be forced into the row. The required settings for conflict rule and conflict action are CONFLICT_RULE=K and CONFLICT_ACTION=F.
When added to the CCD table, all of the rows become INSERT operations. Noncondensed CCD tables cannot have a unique index or primary key.
A CCD table that is used for keeping a history of changes to the source table is complete (COMPLETE=Y) and noncondensed (CONDENSED=N).
The following load options apply to CCD target tables:
The following options are available for handling unexpected conditions in CCD target tables:
For all CCD table types, the only valid conflict option is to check only key columns (CONFLICT_RULE=K).
By definition, a CCD table always includes the following columns in addition to the replicated columns from the source table. These columns contain information from the recovery log at the source database that provide more details about each row change:
Column | Description |
---|---|
IBMSNAP_INTENTSEQ | Data type: VARCHAR(16)
FOR BIT DATA; Nullable: No A sequence number that uniquely identifies a change. This value is ascending in a transaction. The log sequence number (LRSN or RBA) of each update, delete, and insert. |
IBMSNAP_OPERATION | Data type: CHAR(1); Nullable: No A flag that indicates the type of operation: I (INSERT), U (UPDATE), or D (DELETE). |
IBMSNAP_COMMITSEQ | Data type: VARCHAR(16)
FOR BIT DATA; Nullable: No A sequence number for each row within a transaction. The log sequence number (LRSN or RBA) of the source commit record. |
IBMSNAP_LOGMARKER | Data type: TIMESTAMP; Nullable: No The approximate time at the source server that the data was committed, measured in Greenwich Mean Time (GMT). 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 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. |
When you create a noncomplete (COMPLETE=N) CCD table with the ASNCLP command-line program or Replication Center, you can specify additional auditing columns. The following table describes these columns:
Column | Description |
---|---|
IBMSNAP_AUTHID | Data type: VARCHAR(30); Nullable: Yes The user ID that updated the source table. This column is the primary authorization ID. |
IBMSNAP_AUTHTKN | Data type: VARCHAR(30); Nullable: Yes The authorization token that is associated with the transaction. The correlation ID (normally a job name) that ran the source update. |
IBMSNAP_PLANID | Data type: VARCHAR(8); Nullable: Yes The plan name that is associated with the transaction. This column will be null for DB2 for Linux, UNIX, and Windows. |
IBMSNAP_UOWID | Data type: VARCHAR(12)
FOR BIT DATA; Nullable: Yes The unit-of-work (UOW) identifier from the log record for a row. The unit-of-work identifier, sometimes called the unit-of-recovery ID (URID) of the transaction. |
Note the following rules for CCD tables in Version 10.2.1:
You can use the ASNCLP command-line program or Replication Center to create CCD tables as part of the process of creating a Q subscription for the source table. The following ASNCLP sample sets the environment and then creates a Q subscription for the EMPLOYEE table in the SAMPLE database, also creating a CCD target table:
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 CCD);
You can use a CCD target table that is populated by the Q Apply program as a source table for another Q Replication target database. Typically, this configuration uses three tiers, where the log record values from the source database at Tier 1 are sent to the Q Apply program at Tier 2 and applied to a target CCD table. The CCD table at Tier 2 is then mapped to a CCD table at Tier 3, and the original values from Tier 1 are 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 TYPE CCD keywords to specify that the target CCD table at Tier 2 will be populated with values taken directly from the recovery log at Tier 1. Then, when you create the Q subscription from Tier 2 to Tier 3, replace the TYPE CCD keywords in the CREATE QSUB command with TYPE USERTABLE so that the CCD columns at Tier 2 are mapped to the matching columns at Tier 3.
The following examples of CREATE QSUB commands illustrate this method. The first example uses the TYPE CCD 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 CCD
KEYS (C1));
The second example omits the TYPE CCD keywords and instead uses TYPE USERTABLE. This syntax prompts the Q Apply program to create a table with identical columns at Tier 3 and then propagate the values in the Tier 2 CCD table to matching columns 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 CCD 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.