CCD tables in Q Replication
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:
- Condensed and complete attributes
- Load options for CCD tables
- Options for errors or conflicts
- Default columns in CCD tables
- Optional auditing columns
- ASNCLP sample for creating CCD target table
- Using CCD tables as Q Replication sources
Condensed and complete attributes
Two attributes define a CCD table: condensed and complete. The following list summarizes these attributes:
- Complete (COMPLETE=Y)
- A complete CCD table contains every row of interest from the source
table and is initialized with a full set of source data.
All target table loading options are valid for complete CCDs (automatic, manual, or no load).
- Noncomplete (COMPLETE=N)
- A noncomplete CCD table contains only changes to the source table
and starts with no data.
The only valid load option for noncomplete CCD tables is no load.
- Condensed (CONDENSED=Y)
- A condensed CCD table contains one row for every key value in
the source table and contains only the latest value for the row.
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.
- Noncondensed (CONDENSED=N)
- A noncondensed CCD table contains multiple rows with the same
key value, one row for every UPDATE, INSERT, or DELETE operation at
the source table.
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).
Load options for CCD tables
The following load options apply to CCD target tables:
- Complete
- You can specify an automatic load by the Q Apply program, a manual load, or no load.
- Noncomplete
- You must specify no load.
Options for errors or conflicts
The following options are available for handling unexpected conditions in CCD target tables:
- Condensed and complete
- Two choices are valid for conflict detection:
- Force the source change into the target table (CONFLICT_ACTION=F).
- Ignore the condition and continue (CONFLICT_ACTION=I).
For all CCD table types, the only valid conflict option is to check only key columns (CONFLICT_RULE=K).
- For noncomplete CCD targets, the IBMQREP_EXCEPTIONS table is not updated after "row not found" or duplicate row conflicts because rows from the source table might not exist in a noncomplete CCD target and rework might occur frequently.
- For complete CCD targets, the IBMQREP_EXCEPTIONS table is updated after conflicts with one exception: duplicate row violations are not logged because rows from delete operations remain in CCD targets and rework might occur frequently.
- For condensed CCD tables, a key update at the source table might result in the removal of a row at the CCD target. The removed row is typically a "delete" row (one with a value of D in the IBMSNAP_OPERATION column). This type of row is removed only if it has the same key value as the new value of the key update.
- For condensed CCD tables, CONFLICT_ACTION=I implies that conflicts at the target are ignored, with one exception: If a key update at the source table results in a rework at the CCD target, a rework is necessary to keep the CCD data consistent.
Default columns in CCD tables
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 for DB2 targets; RAW (16) for
native Oracle targets; Nullable: No z/OS: The log sequence number (LRSN or RBA) of each update, delete, and insert. Linux, UNIX, Windows: A sequence number that uniquely identifies a change. This value is ascending in a transaction. |
| 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 for DB2 targets; RAW (16) for
native Oracle targets; Nullable: No z/OS: The log sequence number (LRSN or RBA) of the source commit record. Linux, UNIX, Windows: A sequence number for each row within a transaction. |
| 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. |
Optional auditing columns
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) for DB2 targets; VARCHAR2(30) for native Oracle
targets;
Nullable: Yes z/OS: This column is the primary authorization ID. Linux, UNIX, Windows: The user ID that updated the source table. |
| IBMSNAP_AUTHTKN | Data type:
VARCHAR(30) for DB2 targets; VARCHAR2(30) for native Oracle
targets;
Nullable: Yes z/OS: The correlation ID (normally a job name) that ran the source update. Linux, UNIX, Windows: The authorization token that is associated with the transaction. |
| IBMSNAP_PLANID | Data type:
VARCHAR(8) for DB2 targets; VARCHAR2(30) for native Oracle
targets;
Nullable: Yes z/OS: 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 for DB2 targets; RAW(16) for
native Oracle targets; Nullable: Yes z/OS: The unit-of-work identifier, sometimes called the unit-of-recovery ID (URID) of the transaction. Linux, UNIX, Windows: The unit-of-work (UOW) identifier from the log record for a row. |
Note the following rules for CCD tables in Version 10.2.1:
- For CCD targets, the COMPATIBILITY column in the IBMQREP_CAPPARMS table must be set to 1021 or 1001
- If the Q Capture program is at ARCH_LEVEL 1021:
- It always sends 16-byte log sequence numbers.
- At compatibility 1001 it always sends 10-byte UOWIDs
- At compatibility 1021 it sends 12 byte UOWIDs if the Q Capture server is DB2 11 for z/OS® and 10-byte UOWIDs if the Q Capture server is DB2 10 for z/OS
ASNCLP sample for creating CCD target table
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);Using CCD tables as Q Replication sources
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.