Replication Guide and Reference

Tables used at the target server

The following section provides a brief description of the Apply program target tables used at the target server and the columns in each table.

User copy table

Important: Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

userid.target_table

The user copy 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 table.

Except for subsetting and data enhancement, a user copy table reflects a valid state of the source table, but not necessarily the most current state. References to user copy tables (or any other type of target table) reduce the risk of contention problems that results from a high volume of 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.

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

Table 67. User Copy 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 source 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 source table or view. The columns from the source table do not need to match these columns, but the data types must match.
user computed columns	User-defined columns that are derived from SQL expressions. You can use computed columns with SQL functions to convert source data types to different target data types.

Point-in-time table

Important: Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

userid.target_table

The point-in-time table is similar to the user copy table, but contains an additional 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 a past image of the source table. Point-in-time copies reflect a valid state of the source table, but not necessarily the most current state.

Refer to the IBMSNAP_LOGMARKER column for a commit time, so you will know the point in time that your copy of the source table resembles.

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

Table 68. Point-in-Time 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 source 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 source table or view. The columns from the source table do not need to match these columns, but the data types must match.
user computed columns	User-defined columns that are derived from SQL expressions. You can use computed columns with SQL functions to convert source data types to different target data types.
IBMSNAP_LOGMARKER	The approximate commit time at the source server. This column is NULL following a full refresh.

Consistent-change-data table

This table contains information that you can update by using SQL.

userid.target_table

CCD tables are staging tables that contain committed change data. Maintaining CCD tables requires updating the CCD_OLD_SYNCHPOINT and SYNCHPOINT columns of the register table.

The CCD table can be:

• A staging table maintained by one Apply program

  The result of a join between the CD and UOW tables can be stored here, so that you perform the join step only once for replicating changes to multiple targets. The CCD table can be maintained on a remote system. The advantage of maintaining your CCD table remotely is that you reduce the work load on your source. You replicate a set of changes from the original source to the CCD table only once. The CCD table then acts as the source and maintains all changes. Later, the changes to the CCD table will be updated to the original source.

• External source table for nonrelational and multivendor data

  External programs can create CCD tables to be used by DB2 DataPropagator as replication sources. An example is DataPropagator NonRelational, which captures IMS changes and maintains a CCD table so that the copies of IMS data can be re-created in a relational database.

For CCD tables:

• 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 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.

• For condensed CCD tables (CCD_CONDENSED = 'Y'), a unique index is required for user-data primary-key columns to maintain the CCD table.

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

• 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.

• 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.

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

• 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.

• Views that are defined as replication sources can refer only to CCD tables that are complete and condensed.

The originally captured operation code in the IBMSNAP_OPERATION column and the sequence numbers IBMSNAP_INTENTSEQ and IBMSNAP_COMMITSEQ are included in CCD 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

Special cases for condensed CCD tables:

• Because condensed CCD tables have a unique index constraint, inserts to a row with a key that already exist will fail. The insert becomes an update.

• Updates to rows that do not exist with a key will fail. The update becomes an insert.

• A delete is always handled as an update and the row remains in the CCD table.

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

Table 69. CCD Table Columns

Column name	Description
IBMSNAP_INTENTSEQ	Log or journal record sequence number that uniquely identifies a change. This value is globally ascending.
IBMSNAP_OPERATION	Character value of I, U, or D, indicating an insert, update, or delete record.
IBMSNAP_COMMITSEQ	The log record sequence number of the captured commit statement. This value groups inserts, updates, and deletes by original source transactions.
IBMSNAP_LOGMARKER	The approximate commit time at the source server.
user key columns	The primary key of the target table, although it is not necessarily a component of the primary key of the source 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 source table. The columns from the source table do not need to match these columns, but the data types must match.
user computed columns	User-defined columns that are derived from SQL expressions. You can use computed columns with SQL functions to convert source data types to different target data types.

Replica table

This table contains information that you can update by using SQL.

userid.target_table

The replica must have the same primary key as the source table. Because of these similarities, the replica table can be used as a source table for further subscription sets, making the target server a source server as well. Converting a target table into a source table is done automatically when you define a replica target type and specify the CHANGE DATA CAPTURE attribute. See Defining replication subscriptions for update-anywhere replication for more information.

Table 70 provides a brief description of the replica table columns.

Table 70. Replica 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 source 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 source table. The columns from the source table do not need to match these columns, but the data types must match.

Base aggregate table

Important: Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

userid.target_table

Base aggregate tables are target tables that contain data aggregated from a source table. Functions are performed on data located at the source table, and the result of the function is inserted as a row in the base aggregate table.

For base aggregate tables:

• 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 71 provides a brief description of the base aggregate table columns.

Table 71. Base Aggregate Table Columns

Column name	Description
user columns	Columns computed from the source table.
IBMSNAP_LLOGMARKER	The current source server timestamp at the time of refresh.
IBMSNAP_HLOGMARKER	The current source server timestamp at the time of refresh.

Change aggregate table

Important: Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

userid.target_table

A change aggregate table is a target table that contains data aggregations based on changes from a source table. This table is similar to the base aggregate table, except that the functions being performed at the source table are done only for changes that occur during a specific time interval. The results of these functions are inserted as rows into the change aggregate table. Before-image user data columns must be nullable in change aggregate tables.

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

Table 72. Change Aggregate 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 source 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 source table. The columns from the source table do not need to match these columns, but the data types must match.
user computed columns	User-defined columns that are derived from SQL expressions. You can use computed columns with SQL functions to covert source data types to different target data types.
IBMSNAP_LLOGMARKER	The oldest IBMSNAP_LOGMARKER or IBMSNAP_LLOGMARKER value present in the (CD+UOW) or CCD table rows being aggregated.
IBMSNAP_HLOGMARKER	The youngest IBMSNAP_LOGMARKER or IBMSNAP_HLOGMARKER value present in the (CD+UOW) or CCD table rows being aggregated.

Row-replica table (Microsoft Jet specific)

This table contains information that you can update by using SQL.

userid.target_table

This table is an update-anywhere replica table maintained by DataPropagator for Microsoft Jet. Conflicts are detected row by row, not transaction by transaction, as they are for replica tables. Row-replica is the only type of target table supported by DataPropagator for Microsoft Jet. The source table can be a DB2, Oracle, Sybase, Informix, or Microsoft SQL Server user table, or a DB2 replica. The source can also be a view of a DB2 user table or replica table, including a join view.

Table 73 provides a brief description of the row-replica table columns.

Table 73. Row-Replica 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 source 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 source table. The columns from the source table do not need to match these columns, but the data types must match.

Conflict table (Microsoft Jet specific)

Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

IBMSNAP_<target name>_CONFLICT

This table is a conflict table for tracking synchronization conflicts and errors. This Microsoft Jet database control table mimics Microsoft's conflict tables. 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 74 provides a brief description of the conflict table columns.

Table 74. Conflict Table Columns

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

Error information table (Microsoft Jet specific)

Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

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 75 provides a brief description of the error information table columns.

Table 75. 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 DB2 DataPropagator error message number.

Error messages table (Microsoft Jet specific)

Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

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 76 provides a brief description of the error messages table columns.

Table 76. Error Messages Table Columns

Column name	Description
Reason	The DB2 DataPropagator error message number.
ReasonText	The DB2 DataPropagator error message text.

Error-side-information table (Microsoft Jet specific)

Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

IBMSNAP_SIDE_INFO

This table is a conflict table for tracking synchronization conflicts and errors. This Microsoft Jet database control table mimics Microsoft's conflict tables. This table contains the names of the conflict tables created by DataPropagator for Microsoft Jet.

Table 77 provides a brief description of the error-side-information table column.

Table 77. Error-Side-Information Table Column

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

Key string table (Microsoft Jet specific)

Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

IBMSNAP_GUID_KEY

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

• Rows are deleted 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 replicate Microsoft Jet database deletes to an RDBMS.

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

Table 78. 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 ' '. The column names are taken from the row-replica definition and can contain uppercase letters, lowercase letters or both. The constant values are taken from the rows themselves and the string values can contain uppercase letters, lowercase letters, numeric characters, or any combination of the three. Microsoft Jet database supports ASCII, so the string constants can contain single- or double-byte characters. For example: COL1=(character) AND COL2=(character)

Synchronization generations table (Microsoft Jet specific)

Do not use SQL to update this table. Altering this table inappropriately can cause unexpected results and loss of data.

IBMSNAP_S_GENERATION

This table is used to prevent cyclic updates from replicating 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 critical section table, which results in a posting to the APPLY_QUAL column of the UOW table by the Capture program.

The s_GENERATION column is 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.

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, multiple RDBMS-to-Jet generations can be posted before any Microsoft Jet database changes replicate to the RDBMS. 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_GENERATION of changes needs to be replicated to the RDBMS.

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

Table 79. Synchronization Generations Table

Column name	Description
Update_Type	A value that indicates whether a generation of changes is: '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.

[ DB2 List of Books | Search the DB2 Books ]