DB2 Replication Guide and Reference

Control Tables Used at the Target Server

The following section provides a brief description of the control tables used at the target server, the columns in each control table, and the create table and create index statements for each table. The structure of all target tables is application-dependent.

Base Aggregate Target Table

This section contains programming interface information.

userid.target_table

Base aggregate tables are target tables that contain data aggregated from a source table.

For base aggregate tables:

• A descending non-unique index on the IBMSNAP_LLOGMARKER table is created by the Control Center.

• Before-image user data columns must be nullable.

• If the computation will never generate a null value, then the computed columns should be NOT NULL.

• Null attributes of the after-image user data columns should match null attributes of the source, except for primary key columns, which should always be NOT NULL.

Table 53 provides a brief description of the base aggregate target table columns.

Table 53. Base Aggregate Target Table Columns

Column name	Description
user columns	Columns computed from the base table.
IBMSNAP_LLOGMARKER	The oldest (lowest) IBMSNAP_LOGMARKER or IBMSNAP_LLOGMARKER value in the CD or CCD table rows being aggregated.
IBMSNAP_HLOGMARKER	The most recent (highest) IBMSNAP_LOGMARKER or IBMSNAP_LLOGMARKER value in the CD or CCD table rows being aggregated.

Figure 64 and Figure 65 show the create table and create index statements for the base aggregate target table.

Figure 64. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher Base Aggregate Target Tables

For DB2 for OS/390 Version 4 or Higher CREATE TABLE userid.target_table( IBMSNAP_LLOGMARKER TIMESTAMP, IBMSNAP_HLOGMARKER TIMESTAMP, <user data columns>, <aggregate columns>); CREATE TYPE 2 UNIQUE INDEX userid.IXtimestamp ON userid.target_table (IBMSNAP_LLOGMARKER DESC);

Figure 65. Create Table and Create Index Statements for Base Aggregate Target Tables on All Other Platforms

For All Other Platforms CREATE TABLE userid.target_table( IBMSNAP_LLOGMARKER TIMESTAMP, IBMSNAP_HLOGMARKER TIMESTAMP, <user data columns>, <aggregate columns>); CREATE UNIQUE INDEX userid.IXtimestamp ON userid.target_table (IBMSNAP_LLOGMARKER DESC);

Change Aggregate Target Table

This section contains programming interface information.

userid.target_table

A change aggregate table is a target table that contains data aggregations based on changes from a source table.

For change aggregate tables:

• A non-unique descending index on the IBMSNAP_LLOGMARKER column is created by the Control Center.

• Before-image user data columns must be nullable.

• Computed user data columns should be NOT NULL if your design can determine that the computation will never generate a null value, for example in a concatenation scenario.

• Because CCD tables can contain duplicate rows, the DISTINCT clause should be used when a CCD table is the replication source table of a change aggregate target table.

Table 54 provides a brief description of the change aggregate target table columns.

Table 54. Change Aggregate Target Table Columns

Column name	Description
User columns, including computed columns	These columns are computed from change data related to the base table.
IBMSNAP_LLOGMARKER	The oldest (lowest) IBMSNAP_LOGMARKER or IBMSNAP_LLOGMARKER value in the CD or CCD table rows being aggregated.
IBMSNAP_HLOGMARKER	The most recent (highest) IBMSNAP_LOGMARKER or IBMSNAP_LLOGMARKER value in the CD or CCD table rows being aggregated.

Figure 66 and Figure 67 show the create table and create index statements for the change aggregate target table.

Figure 66. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher Change Aggregate Target Tables

For DB2 for OS/390 Version 4 or Higher CREATE TABLE userid.target_table ( IBMSNAP_LLOGMARKER TIMESTAMP NOT NULL, IBMSNAP_HLOGMARKER TIMESTAMP NOT NULL); <user data columns>, <aggregate columns>); CREATE TYPE 2 INDEX userid.IXtimestamp ON userid.target_table (IBMSNAP_LLOGMARKER DESC);

Figure 67. Create Table and Create Index Statements for Change Aggregate Target Tables on All Other Platforms

For All Other Platforms CREATE TABLE userid.target_table ( IBMSNAP_LLOGMARKER TIMESTAMP NOT NULL, IBMSNAP_HLOGMARKER TIMESTAMP NOT NULL); <user data columns>, <aggregate columns>); CREATE INDEX userid.IXtimestamp ON userid.target_table (IBMSNAP_LLOGMARKER DESC);

Consistent Change Data Table

This section contains programming interface information.

userid.target_table

CCD tables contain committed change data.

The CCD table can be:

• A table of data.

• A staging table maintained by one Apply program.

  The result of a join between the CD table and the UOW table can be stored here, so that you perform the join step only once for fan-out copying.

• A secondary staging table.

• An external source table for nonrelational and multivendor data.

  The external source table allows IBM Replication to act as a "loading dock" and deliver transaction-consistent data from nonrelational sources.

For CCD tables:

• A non-unique ascending index on IBMSNAP_COMMITSEQ is created. This index improves the performance of updating CCD table copies.

• If the CCD table is condensed (CCD_CONDENSED = 'Y'), a unique index is required for user data primary key columns to maintain the CCD table. CCD copies with TARGET_CONDENSED = 'N' can contain duplicates if changes are reapplied following a failure of the Apply program.

• Before-image user data columns must be nullable and therefore cannot be part of a primary key for a condensed CCD table.

• An external CCD table is an alternate source for the original user table. The user table does not include computed columns; therefore, computed columns should not be included in the CCD subscriptions.

• Null attributes of the after-image user data columns should match the null attributes of the source.

• Views of change data tables can be included in view replication sources.

• If an external program, other than the Apply program, maintains the external CCD table, the external program must initialize, maintain, and supply the correct values for the control columns.

• Noncondensed CCD tables can contain duplicate rows if changes are reapplied after a failure during the previous copy operation.

• The Capture program does not insert data into CCD tables and does not prune them. Instead, your application requirements should determine the history retention period for CCD tables (described in Staging Changed Data). Therefore, pruning of CCD tables is not automatic by default, but can be easily automated using an SQL statement to be processed after the subscription cycle.

CCD tables and point-in-time (PIT) tables are changed, rather than appended. The originally captured operation code in the IBMSNAP_OPERATION column and the sequence numbers IBMSNAP_INTENTSEQ and IBMSNAP_COMMITSEQ are copied into and out of CCD staging tables. For condensed CCD tables, only the latest values are kept for each row. The copy operation in IBMSNAP_OPERATION is an insert, update, or delete. The codes are:

I

Insert

U

Update

D

Delete

Exceptions:

• The operation code is I, but a unique index constraint causes the insert to fail because a row with the key already exists. The insert becomes an update.

• The operation code is U, but the update fails because no row exists with the key. The update becomes an insert.

• A delete fails with a "row not found" condition. The condition is ignored by the Apply program.

• For a condensed CCD, a delete is always handled as an update.

Table 55 provides a brief description of the CCD table columns.

Table 55. CCD Table Columns

Column name	Description
IBMSNAP_INTENTSEQ	Unique identifier for this change; it describes the sequence of a change within a transaction.
IBMSNAP_OPERATION	Character value of I, U, or D, indicating an insert, update, or delete record.
IBMSNAP_COMMITSEQ	The transaction commit sequencing value.
IBMSNAP_LOGMARKER	The approximate commit time at the source server.
<user data columns>	Columns from source tables specified while defining replication sources.

Figure 68 and Figure 69 show the create table and create index statements for the CCD table.

Figure 68. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher CCD Tables

For DB2 for OS/390 Version 4 or Higher CREATE TABLE userid.target_table ( IBMSNAP_INTENTSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_OPERATION CHAR(1) NOT NULL, IBMSNAP_COMMITSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_LOGMARKER TIMESTAMP NOT NULL, <user data columns>); CREATE TYPE 2 INDEX userid.IXtimestamp ON userid.target_table(IBMSNAP_COMMITSEQ ASC); For Condensed CCD Tables CREATE TYPE 2 UNIQUE INDEX userid.IXtimestamp ON userid.target_table (<primary key columns>);

Figure 69. Create Table and Create Index Statements for CCD Tables on All Other Platforms

For All Other Platforms CREATE TABLE userid.target_table ( IBMSNAP_INTENTSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_OPERATION CHAR(1) NOT NULL, IBMSNAP_COMMITSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_LOGMARKER TIMESTAMP NOT NULL, KEY1 CHAR(1), DATA1 CHAR(1)); CREATE INDEX userid.IXtimestamp ON userid.target_table(IBMSNAP_COMMITSEQ ASC); For Condensed CCD Tables CREATE UNIQUE INDEX userid.IXtimestamp ON userid.target_table (<primary key columns>);

Point-in-Time Target Table

This section contains programming interface information.

userid.target_table

A point-in-time table contains an added system column (IBMSNAP_LOGMARKER) containing the approximate timestamp of when the particular row was inserted or updated at the source system. Otherwise, a point-in-time table is much like an image of the source table, but at some time in the past. Point-in-time copies reflect a valid state of the source table, but not necessarily the most current state.

For point-in-time tables:

• A unique index is automatically created and required for the primary key.

• Before-image user data columns must be nullable.

• Before-image columns should not be included in the primary key.

• Null attributes of the after-image user data columns should match null attributes of the source, except for primary key columns, which should always be NOT NULL.

CCD tables and point-in-time tables are changed, rather than appended.

Table 56 provides a brief description of the point-in-time target table columns.

Table 56. Point-in-Time Target Table Columns

Column name	Description
user key columns	The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a NULL value from being assigned to the key fields of any copies.
user nonkey columns	The nonkey data column from the base table.
IBMSNAP_LOGMARKER	The approximate commit time at the source server. This column is NULL following a full refresh.

Figure 70 and Figure 71 show the create table and create index statements for the point-in-time table.

Figure 70. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher Point-In-Time Tables

For DB2 for OS/390 Version 4 or Higher CREATE TABLE userid.target_table ( <user data columns>, IBMSNAP_LOGMARKER TIMESTAMP NOT NULL); CREATE TYPE 2 UNIQUE INDEX userid.IXtimestamp ON userid.target_table (<primary key columns>);

Figure 71. Create Table and Create Index Statements for Point-In-Time Tables on All Other Platforms

For All Other Platforms CREATE TABLE userid.target_table ( <user data columns>, IBMSNAP_LOGMARKER TIMESTAMP NOT NULL); CREATE UNIQUE INDEX IXtimestamp ON userid.target_table (<primary key columns>);

Note:

The primary key requires a unique index.

Replica Target Table

This section contains programming interface information.

userid.target_table

This table contains a primary key identical to the primary key of the user table. The replica must have the same primary key as the source table.

Table 57 provides a brief description of the replica target table columns.

Table 57. Replica Target Table Columns

Column name	Description
user key columns	The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a NULL value from being assigned to the key fields of any copies.
user nonkey columns	The nonkey data columns from the base table.

Figure 72 and Figure 73 show the create table and create index statements for the replica target table

Figure 72. Create Table and Create Index Statements for DB2 for OS/2 Version 4 or Higher Replica Target Tables

For DB2 for OS/390 Version 4 or Higher CREATE TABLE userid.target_table ( <user data columns>) DATA CAPTURE CHANGES; CREATE TYPE 2 UNIQUE INDEX userid.IXtimestamp ON userid.target_table (<primary key columns>);

Figure 73. Create Table and Create Index Statements for Replica Target Tables on All Other Platforms

For All Other Platforms CREATE TABLE userid.target_table ( <user data columns>) DATA CAPTURE CHANGES; CREATE UNIQUE INDEX userid.IXtimestamp ON userid.target_table (<primary key columns>);

User Copy Target Table

This section contains programming interface information.

userid.target_table

This table is the default target type and indicates a specific commit time from the source server. The user copy target table is identical to the point-in-time target table with the exception of the IBMSNAP_LOGMARKER column, which is not included in the user copy target table.

A user copy table reflects a valid state of the source table, except for subsetting and data enhancement, but not necessarily the most current state. Because the tables are physical objects, references to user copy target tables (or any other target table type) reduce the contention that results from too much direct access to the source tables. Accessing local user copy tables is much faster than using the network to access remote source tables for each query.

For user copy target tables:

• A unique index is automatically created and required for the primary key.

• Before-image user data columns must be nullable.

• Before-image columns should not be included in the primary key.

• Null attributes of the after-image user data columns should match NULL attributes of the source, except for primary key columns, which should always be NOT NULL.

Table 58 provides a brief description of the user copy target table columns.

Table 58. User Copy Target Table Columns

Column name	Description
user key columns	The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a NULL value from being assigned to the key fields of any copies.
user nonkey columns	The nonkey data columns from the base table.

Figure 74 and Figure 75 shows the create table and create index statements for the user copy target table.

Figure 74. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher User Copy Target Tables

For DB2 for OS/390 Version 4 or Higher CREATE TABLE userid.target_table ( <user data columns>); CREATE TYPE 2 UNIQUE INDEX userid.IXtimestamp ON userid.target_table (<primary key columns>);

Figure 75. Create Table and Create Index Statements for User Copy Target Tables on All Other Platforms

For All Other Platforms CREATE TABLE userid.target_table ( <user data columns>); CREATE UNIQUE INDEX userid.IXtimestamp ON userid.target_table (<primary key columns>);

The following table is a conflict table for tracking synchronization conflicts and errors. This Microsoft Jet database control table mimics Microsoft's conflict tables.

Conflict Table (Microsoft Jet Specific)

This section contains programming interface information.

IBMSNAP_<target name>_CONFLICT

This table contains the conflict loser's row data. The columns are the same as the corresponding row-replica table. This table can have more than one row. The conflict table is created along with the row-replica table in the Microsoft Jet database and dropped when the row-replica table is dropped.

Table 59 provides a brief description of the conflict table columns.

Table 59. Conflict Table Columns

Column name	Description
target name	The corresponding row-replica's table name.
column names of row-replica	A list of column names found in the corresponding row-replica table.

Error Information Table (Microsoft Jet Specific)

This section contains programming interface information.

IBMSNAP_ERROR_INFO

This table identifies the row-replica table and row that caused the error. This table can have more than one row. The error information table is created along with the Microsoft Jet database and never dropped.

Table 60 provides a brief description of the error information table columns.

Table 60. Error Information Table Columns

Column name	Description
TableName	The name of the row-replica table that is the source of the row that caused the error.
RowGuid	The GUID of the row that caused the error.
Operation	One of the following commands to identify the operation that caused the error: 'INSERT', 'UPDATE', or 'DELETE'.
Reason	The DPROPR error message number.

Figure 76 shows the create table statement for the error information table.

Figure 76. Create Table for the Error Information Table

For Microsoft Jet CREATE TABLE IBMSNAP_ERROR_INFO ( TableName Text 37, RowGuid ReplicationID, Operation Text 6, Reason Long Integer);

Error Messages Table (Microsoft Jet Specific)

This section contains programming interface information.

IBMSNAP_ERROR_MESSAGE

This table identifies the nature of an error. It contains the error code and error message. This table can have more than one row. The error messages table is created along with the Microsoft Jet database and never dropped.

Table 61 provides a brief description of the error messages table columns.

Table 61. Error Messages Table Columns

Column name	Description
Reason	The DPROPR error message number.
ReasonText	The DPROPR error message text.

Figure 77 shows the create table statement for the error messages table.

Figure 77. Create Table Statement for the Error Messages Table

For Microsoft Jet CREATE TABLE IBMSNAP_ERROR_MESSAGE ( Reason Long Integer, ReasonText Memo);

The following table is a conflict table for tracking synchronization conflicts and errors. This Microsoft Jet database control table mimics Microsoft's conflict tables.

Error Side Information Table (Microsoft Jet Specific)

This section contains programming interface information.

IBMSNAP_SIDE_INFO

This table contains the names of the conflict tables created by DataPropagator for Microsoft Jet.

Table 62 provides a brief description of the error side information table columns.

Table 62. Error Side Information Table Columns

Column name	Description
ConflictTableName	The conflict table name created by DataPropagator for Microsoft Jet.

Figure 78 shows the create table statement for the error side information table.

Figure 78. Create Table Statement for the Error Side Information Table

For Microsoft Jet CREATE TABLE IBMSNAP_SIDE_INFO ( ConflictTableName Text 37);

Key String Table (Microsoft Jet Specific)

This section contains programming interface information.

IBMSNAP_GUID_KEY

This table maps the Microsoft Jet table names and row identifiers to primary key values when the following actions occur:

• Deletes rows from Microsoft Jet database tables.

• Deletes are recorded in MSysTombstone with s_Generation, TableGUID and s_GUID (row) identifiers, but without primary key details.

• The primary key values are needed to propagate Microsoft Jet database deletes to an RDBMS.

Table 63 provides a brief description of the key string table columns.

Table 63. Key String Table Columns

Column name	Description
RowReplicaname	Identifies the row-replica table where the row was inserted.
s_GUID	Identifies the row in the specific row-replica table.
key_string	The string of "and-ed" DB2 SQL predicates identifying the key columns and their row values, with character constants delimited by single quotes. The column names are taken from the row-replica definition and can contain upper case letters, lower case letters or both. The constant values are taken from the rows themselves and the string values can contain upper case letters, lower case letters, numeric characters, or any combination of the three. Microsoft Jet database supports both ASCII and UNICODE, so the string constants can contain single or double byte characters. For example: COL1=(character) AND COL2=(character)

Figure 79 shows the create table and create index statements for the key string table.

Figure 79. Create Table and Create Index Statements for Key String Tables

For Microsoft Jet CREATE TABLE IBMSNAP_GUID_KEY ( RowReplicaname Text 37 required, s_GUID ReplicationID required, key_string Memo required); CREATE UNIQUE INDEX IBMSNAP_GUID_KEYIX ON IBMSNAP_GUID_KEY (RowReplicaname, s_GUID);

Synchronization Generations Table (Microsoft Jet Specific)

This section contains programming interface information.

IBMSNAP_S_GENERATION

This table is used to prevent cyclic updates from propagating back to the RDBMS from a Microsoft Jet database. When DB2 is the target, this function is accomplished in a different way, using the APPLY_QUAL column of the ASN.IBMSNAP_CRITSEC table, which results in a posting to ASN.IBMSNAP_UOW.APPLY_QUAL by the Capture program.

The s_GENERATION column will be maintained by Microsoft Jet and set to the same generation number as any other updates made since the last synchronization. If synchronization is successful the synchronization generations table will contain one row whose Update_Type value is 'F'.

Multiple RDBMS-to-Jet generations can be posted before any Microsoft Jet database changes propagate to the RDBMS, due to the risk of partial failures during a DataPropagator for Microsoft Jet synchronization cycle, and because the WHOS_ON_FIRST = 'S' flow is handled before the WHOS_ON_FIRST = 'F' flow. In such a case, there is the possibility that a list of s_GENERATION values will need to be skipped over when determining which s_GENERTION of changes need to be propagated to the RDBMS.

Table 64 provides a brief description of the synchronization generations table columns.

Table 64. Synchronization Generations Table

Column name	Description
Update_Type	A value that indicates whether a generation of changes are: 'L' Local to the Microsoft Jet database 'F' Foreign
JetSynchtime	This is a dummy column, set to the time of a forced Microsoft Jet database synchronization.

Figure 80 shows the create table and create index statements for the synchronization generations table.

Figure 80. Create Table and Create Index Statements for Synchronization Generations Tables

For Microsoft Jet CREATE TABLE IBMSNAP_S_GENERATION ( Update_Type Text 1, JetSynchtime DateTime ); CREATE UNIQUE INDEX IBMSNAP_S_GENERATION_IX ON IBMSNAP_S_GENERATION (JetSynchtime);

[ DB2 List of Books | Search the DB2 Books ]