Replication Guide and Reference

Tables used at the source server

The following section provides a brief description of the tables used at the source server and the columns in each table. These tables are created automatically the first time that you define a replication source if you use the Control Center and they do not already exist on the source server.

The administration tools use the information in the register, register extension, and pruning control tables to define your source and target tables for replication. After you define your replication sources, the Capture program uses the tuning parameters, capture enqueue, warm start, critical section, trace, and Apply-qualifier-cross-reference tables to control and audit your data. In addition to all the Capture program control and audit tables mentioned, the Capture triggers also use the register synchronization table to control data. The UOW and CD tables track data that has not been replicated.

Register table

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

ASN.IBMSNAP_REGISTER

The register table contains information about replication sources, such as the names of the replication source tables, their attributes, and their staging table names. A row is automatically inserted into this table every time a new replication source is defined at this server. You must update this table to maintain an external CCD table.

The register table is the place to look if you need to know how you defined your replication sources.

Table 43 provides a brief description of the register table columns.

Table 43. Register Table Columns

Column name Description
SOURCE_OWNER The owner of the source table or view.
SOURCE_TABLE The source from which data is being captured.
SOURCE_VIEW_QUAL This value is set to 0 for physical tables that are defined as sources and is greater than 0 for views that are defined as sources. This column is used to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.
GLOBAL_RECORD A flag that indicates whether this row is the global record. In the global record, only the SYNCHPOINT and SYNCHTIME columns are set by the Capture program to reflect its progress. If the Capture program has not been running, then there is no global record.

Y
This row is the global record.
N
This row is not the global record.

SOURCE_STRUCTURE A value that identifies the structure of the source table or view:

1
User table
3
CCD table
4
Point-in-time table
5
Base aggregate table
6
Change aggregate table
7
Replica table
8
User copy table
9
Row-replica table

SOURCE_CONDENSED A flag that indicates:

Y
For any given primary key, the CCD, replica, and user tables show only one row.
N
All changes must remain, retaining a complete update history.
A
Valid only for base aggregate or change aggregate tables.

SOURCE_COMPLETE
A flag that indicates:

Y
The source table contains a row for every primary key value of interest.
N
The source table contains some subset of rows of primary key values.

CD_OWNER The owner of the change data table or a view.
CD_TABLE The name of the change data table or view for captured updates to the source table (set when you define the replication source). This value is used by the Apply program and can be the name of a table or a view. The Capture program inserts one row into the CD table for every committed and uncommitted change to this replication source. The Apply program then joins this table with the UOW table so that only committed changes are replicated.
PHYS_CHANGE_OWNER The owner of the PHYS_CHANGE_TABLE. For a view defined as a source, this value equals the value of a CD or CCD table referenced in the change data view definition. For non-view replication sources, the value equals the CD_OWNER or the CCD_OWNER column. The Capture program uses this value to properly maintain CD_OLD_SYNCHPOINT and CD_NEW_SYNCHPOINT for view replication sources. The Apply program uses this value to properly maintain CCD_OLD_SYNCHPOINT and SYNCHPOINT for view replication sources that are based on CCD tables that the Apply program maintains.
PHYS_CHANGE_TABLE The name of the physical CD or CCD table. For a view replication source, this value equals the value of the CD or CCD table replication source definition referenced in the change data view definition. For non-view replication sources, the value equals the CD_TABLE or the CCD_TABLE column. The Capture program uses this value to properly maintain CD_OLD_SYNCHPOINT and CD_NEW_SYNCHPOINT for view replication sources. The Apply program uses this value to properly maintain CCD_OLD_SYNCHPOINT and SYNCHPOINT for view replication sources that are based on the CCD tables that the Apply program maintains.
CD_OLD_SYNCHPOINT The approximate SYNCHPOINT value when the Capture program begins to capture changes from the source table. The Capture program sets this value to NULL during a cold start. The Apply program sets this value to NULL for a target replica when cascading a gap condition. If the value is null when the synchpoint column of the pruning control table is set to x'00000000000000000000', the Capture program sets an initial value, and the same sequence number is reflected back into the SYNCHPOINT column of the pruning control table; this is the sequence number associated with the pruning control table update. Subsequent values are set by the Capture program when old rows are pruned from the table.
CD_NEW_SYNCHPOINT The Capture program advances this column as it inserts new rows into the CD table. If the Capture program did not insert into the change data table recently, then the value does not advance. The Apply program uses this column to see if there are new changes to be replicated.
DISABLE_REFRESH When this column is created, it contains the '0' flag. If you set the flag to '1', the Apply program is not allowed to perform a full refresh of the source server until the flag is set back to '0'. This column is used to defer, not eliminate, a full refresh for a subscription. For example, you might want to defer a full refresh when the Capture program starts up cold or a gap in the log is detected. The Apply program will not process subscriptions to this replication source until control table values have been updated. This flag prevents full refresh activity from overloading the source database during peak periods. This column is initialized to '0'. You can use a program at the source database site to set this flag.

0
Full refreshes are allowed.
1
Full refreshes are prevented.

CCD_OWNER The owner of the local consistent-change-data table.
CCD_TABLE The name of the staging table that contains committed-only captured updates.
CCD_OLD_SYNCHPOINT The SYNCHPOINT value of the oldest row in the external CCD table. This value can be much older than any row remaining in the CCD table. This value is set in one of the following ways:

By the administration tool when the consistent-change-data table is automatically defined as a source. CCD_OLD_SYNCHPOINT is set to NULL.
By the Control Center when a consistent-change-data table is defined as an external replication source table. CCD_OLD_SYNCHPOINT is set to MIN(IBMSNAP_COMMITSEQ) of the consistent-change-data table.
By the Apply program or another external application.
Manually, for CCD replication sources that are not created and maintained by the Apply program. This is the case for CCD tables that contain IMS changes generated by DataPropagator NonRelational.

SYNCHPOINT In the global row, where the GLOBAL_RECORD column = 'Y', this is the log or journal identifier (synchpoint) of the last log or journal record processed by the Capture program. The Apply program compares this value to the last synchpoint that it processed to see if there are new changes available for replication.
For CCD source definitions, this is the equivalent to CD_NEW_SYNCHPOINT and is updated by the Apply program that maintains the CCD table. This column must be set manually for a CCD replication source that is not created and maintained by the Apply program. An example is a CCD table of IMS changes generated by DataPropagator NonRelational.
SYNCHTIME A source server timestamp. The Capture program or an external program, such as DataPropagator NonRelational, updates this timestamp whether there are changes to be processed or not.
The Apply program uses this value when advanced conflict detection is selected for update-anywhere replication to ensure that the Capture program captured all outstanding changes for a replication source table.
CCD_CONDENSED A flag that indicates:

Y
This CCD replication source has only the last captured change for a source table row.
N
This CCD replication source has one row for each source table row change.

CCD_COMPLETE
A flag that indicates:

Y
The CCD table contains a row for every primary key value of interest.
N
The CCD table is initially empty and then is populated with some subsets of primary key value rows.

ARCH_LEVEL The architectural level of the definition in the row. This level is defined by IBM, and for Version 6 is '0201'.
DESCRIPTION A field for comments that you enter when defining replication sources.
BEFORE_IMG_PREFIX Represents the default character identifying before-image column names in the CD table. The value can be NULL, but must not match any leading character identifying after-image user data column names in the CD table. The length of BEFORE_IMG_PREFIX is:

1
For an ASCII or an EBCDIC single-byte character system prefix character.
2
For an ASCII double-byte character system prefix character.
4
For an EBCDIC double-byte character system prefix character. This length allows for shift-in and shift-out characters.

CONFLICT_LEVEL This column is assumed to never change and to be the same for all descendents of the user table. A flag that indicates:

0
The Apply program does not check for conflicts. Data consistency must be enforced by your application design to avoid potential conflicting updates.
1
Standard detection with cascading transaction rejection. The Apply program checks for conflicts based on the changes captured to this point. The Apply program will reverse any conflicting transaction at the replica, as well as any transactions with dependencies on the conflicting transaction. Changes captured after the Apply program begins conflict detection will not be checked during this Apply cycle.
2
Enhanced detection with cascading transaction rejection. The Apply program waits until the Capture program captures all changes from the log or journal (see description of the SYNCHTIME column) and then does a standard conflict detection (CONFLICT_LEVEL = 1). During the wait time, the Apply program holds a LOCK on the source tables to ensure that no changes are made during the conflict detection process.

PARTITION_KEYS_CHG This value is assumed to be the same for all the user table's dependent replicas. A flag indicating:

N
Updates to the source table are staged by the Capture program as an update and processed by the Apply program as an update statement to the target table.
Y
Updates to the source table are staged by the Capture program as a delete and insert pair. The Apply program processes the delete first and the insert second. When this flag is set, every update to a replication source is stored in the CD table as two rows: a delete row and an insert row. This flag ensures that an update to a key or partitioning column is always processed correctly. Use this flag when:

The source columns for target table primary keys can be updated at the source table.
The source columns for target table partitioning columns that were defined in subscription predicates or in partitioned target databases can be updated at the source table.
The target table is stored in a DB2 for OS/390 partitioned table space and user transactions update all or part of the target table space's partitioning key.
The target table is a DB2 Extended Enterprise Edition table stored in a multi-node node group.

NULL
If this is the global control row.

Column name	Description
SOURCE_OWNER	The owner of the source table or view.
SOURCE_TABLE	The source from which data is being captured.
SOURCE_VIEW_QUAL	This value is set to 0 for physical tables that are defined as sources and is greater than 0 for views that are defined as sources. This column is used to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.
GLOBAL_RECORD	A flag that indicates whether this row is the global record. In the global record, only the SYNCHPOINT and SYNCHTIME columns are set by the Capture program to reflect its progress. If the Capture program has not been running, then there is no global record. Y This row is the global record. N This row is not the global record.
SOURCE_STRUCTURE	A value that identifies the structure of the source table or view: 1 User table 3 CCD table 4 Point-in-time table 5 Base aggregate table 6 Change aggregate table 7 Replica table 8 User copy table 9 Row-replica table
SOURCE_CONDENSED	A flag that indicates: Y For any given primary key, the CCD, replica, and user tables show only one row. N All changes must remain, retaining a complete update history. A Valid only for base aggregate or change aggregate tables.
SOURCE_COMPLETE	A flag that indicates: Y The source table contains a row for every primary key value of interest. N The source table contains some subset of rows of primary key values.
CD_OWNER	The owner of the change data table or a view.
CD_TABLE	The name of the change data table or view for captured updates to the source table (set when you define the replication source). This value is used by the Apply program and can be the name of a table or a view. The Capture program inserts one row into the CD table for every committed and uncommitted change to this replication source. The Apply program then joins this table with the UOW table so that only committed changes are replicated.
PHYS_CHANGE_OWNER	The owner of the PHYS_CHANGE_TABLE. For a view defined as a source, this value equals the value of a CD or CCD table referenced in the change data view definition. For non-view replication sources, the value equals the CD_OWNER or the CCD_OWNER column. The Capture program uses this value to properly maintain CD_OLD_SYNCHPOINT and CD_NEW_SYNCHPOINT for view replication sources. The Apply program uses this value to properly maintain CCD_OLD_SYNCHPOINT and SYNCHPOINT for view replication sources that are based on CCD tables that the Apply program maintains.
PHYS_CHANGE_TABLE	The name of the physical CD or CCD table. For a view replication source, this value equals the value of the CD or CCD table replication source definition referenced in the change data view definition. For non-view replication sources, the value equals the CD_TABLE or the CCD_TABLE column. The Capture program uses this value to properly maintain CD_OLD_SYNCHPOINT and CD_NEW_SYNCHPOINT for view replication sources. The Apply program uses this value to properly maintain CCD_OLD_SYNCHPOINT and SYNCHPOINT for view replication sources that are based on the CCD tables that the Apply program maintains.
CD_OLD_SYNCHPOINT	The approximate SYNCHPOINT value when the Capture program begins to capture changes from the source table. The Capture program sets this value to NULL during a cold start. The Apply program sets this value to NULL for a target replica when cascading a gap condition. If the value is null when the synchpoint column of the pruning control table is set to x'00000000000000000000', the Capture program sets an initial value, and the same sequence number is reflected back into the SYNCHPOINT column of the pruning control table; this is the sequence number associated with the pruning control table update. Subsequent values are set by the Capture program when old rows are pruned from the table.
CD_NEW_SYNCHPOINT	The Capture program advances this column as it inserts new rows into the CD table. If the Capture program did not insert into the change data table recently, then the value does not advance. The Apply program uses this column to see if there are new changes to be replicated.
DISABLE_REFRESH	When this column is created, it contains the '0' flag. If you set the flag to '1', the Apply program is not allowed to perform a full refresh of the source server until the flag is set back to '0'. This column is used to defer, not eliminate, a full refresh for a subscription. For example, you might want to defer a full refresh when the Capture program starts up cold or a gap in the log is detected. The Apply program will not process subscriptions to this replication source until control table values have been updated. This flag prevents full refresh activity from overloading the source database during peak periods. This column is initialized to '0'. You can use a program at the source database site to set this flag. 0 Full refreshes are allowed. 1 Full refreshes are prevented.
CCD_OWNER	The owner of the local consistent-change-data table.
CCD_TABLE	The name of the staging table that contains committed-only captured updates.
CCD_OLD_SYNCHPOINT	The SYNCHPOINT value of the oldest row in the external CCD table. This value can be much older than any row remaining in the CCD table. This value is set in one of the following ways: By the administration tool when the consistent-change-data table is automatically defined as a source. CCD_OLD_SYNCHPOINT is set to NULL. By the Control Center when a consistent-change-data table is defined as an external replication source table. CCD_OLD_SYNCHPOINT is set to MIN(IBMSNAP_COMMITSEQ) of the consistent-change-data table. By the Apply program or another external application. Manually, for CCD replication sources that are not created and maintained by the Apply program. This is the case for CCD tables that contain IMS changes generated by DataPropagator NonRelational.
SYNCHPOINT	In the global row, where the GLOBAL_RECORD column = 'Y', this is the log or journal identifier (synchpoint) of the last log or journal record processed by the Capture program. The Apply program compares this value to the last synchpoint that it processed to see if there are new changes available for replication. For CCD source definitions, this is the equivalent to CD_NEW_SYNCHPOINT and is updated by the Apply program that maintains the CCD table. This column must be set manually for a CCD replication source that is not created and maintained by the Apply program. An example is a CCD table of IMS changes generated by DataPropagator NonRelational.
SYNCHTIME	A source server timestamp. The Capture program or an external program, such as DataPropagator NonRelational, updates this timestamp whether there are changes to be processed or not. The Apply program uses this value when advanced conflict detection is selected for update-anywhere replication to ensure that the Capture program captured all outstanding changes for a replication source table.
CCD_CONDENSED	A flag that indicates: Y This CCD replication source has only the last captured change for a source table row. N This CCD replication source has one row for each source table row change.
CCD_COMPLETE	A flag that indicates: Y The CCD table contains a row for every primary key value of interest. N The CCD table is initially empty and then is populated with some subsets of primary key value rows.
ARCH_LEVEL	The architectural level of the definition in the row. This level is defined by IBM, and for Version 6 is '0201'.
DESCRIPTION	A field for comments that you enter when defining replication sources.
BEFORE_IMG_PREFIX	Represents the default character identifying before-image column names in the CD table. The value can be NULL, but must not match any leading character identifying after-image user data column names in the CD table. The length of BEFORE_IMG_PREFIX is: 1 For an ASCII or an EBCDIC single-byte character system prefix character. 2 For an ASCII double-byte character system prefix character. 4 For an EBCDIC double-byte character system prefix character. This length allows for shift-in and shift-out characters.
CONFLICT_LEVEL	This column is assumed to never change and to be the same for all descendents of the user table. A flag that indicates: 0 The Apply program does not check for conflicts. Data consistency must be enforced by your application design to avoid potential conflicting updates. 1 Standard detection with cascading transaction rejection. The Apply program checks for conflicts based on the changes captured to this point. The Apply program will reverse any conflicting transaction at the replica, as well as any transactions with dependencies on the conflicting transaction. Changes captured after the Apply program begins conflict detection will not be checked during this Apply cycle. 2 Enhanced detection with cascading transaction rejection. The Apply program waits until the Capture program captures all changes from the log or journal (see description of the SYNCHTIME column) and then does a standard conflict detection (CONFLICT_LEVEL = 1). During the wait time, the Apply program holds a LOCK on the source tables to ensure that no changes are made during the conflict detection process.
PARTITION_KEYS_CHG	This value is assumed to be the same for all the user table's dependent replicas. A flag indicating: N Updates to the source table are staged by the Capture program as an update and processed by the Apply program as an update statement to the target table. Y Updates to the source table are staged by the Capture program as a delete and insert pair. The Apply program processes the delete first and the insert second. When this flag is set, every update to a replication source is stored in the CD table as two rows: a delete row and an insert row. This flag ensures that an update to a key or partitioning column is always processed correctly. Use this flag when: The source columns for target table primary keys can be updated at the source table. The source columns for target table partitioning columns that were defined in subscription predicates or in partitioned target databases can be updated at the source table. The target table is stored in a DB2 for OS/390 partitioned table space and user transactions update all or part of the target table space's partitioning key. The target table is a DB2 Extended Enterprise Edition table stored in a multi-node node group. NULL If this is the global control row.

Register extension table for AS/400

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

ASN.IBMSNAP_REG_EXT

This table is an AS/400-specific table that provides supplemental information for the register table, ASN.IBMSNAP_REGISTER. For every register table row, there is a matching register extension table row containing a few additional AS/400-specific columns.

Use this table to complete the information from the register table to track where and how you defined your replication sources on an AS/400 server.

Table 44 provides a brief description of the register extension table columns.

Table 44. Register Extension Table Columns

Column name Description
SOURCE_OWNER The owner of the source table or view.
SOURCE_TABLE The source from which data is being captured.
SOURCE_NAME A 10-character source table or view system name, used to issue commands.
SOURCE_MBR The name of the source table member being captured. Used for issuing Receive Journal Entry (RCVJRNE) commands and ALIAS support.
SOURCE_TABLE_RDB For remote-journal cases, this column contains the Relational Database (RDB) name of the system where the source table actually resides. For non-remote-journal cases, this column is NULL.
JRN_LIB The library name of the journal that the source table uses.
JRN_NAME The name of the journal used by a source table. An asterisk followed by nine blanks in this column means that the source table is not currently journaled in a journal. Therefore, it is not possible to capture data for this source table.
FR_START_TIME Full refresh start time. This column is updated by Capture for AS/400, not the Control Center, during operations.
SOURCE_VIEW_QUAL Supports the view of subscriptions by matching the similar column in the register table. This value is set to equal 0 for physical tables that are defined as a source and is greater than 0 for views that are defined as sources. You must have this column to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.

Column name	Description
SOURCE_OWNER	The owner of the source table or view.
SOURCE_TABLE	The source from which data is being captured.
SOURCE_NAME	A 10-character source table or view system name, used to issue commands.
SOURCE_MBR	The name of the source table member being captured. Used for issuing Receive Journal Entry (RCVJRNE) commands and ALIAS support.
SOURCE_TABLE_RDB	For remote-journal cases, this column contains the Relational Database (RDB) name of the system where the source table actually resides. For non-remote-journal cases, this column is NULL.
JRN_LIB	The library name of the journal that the source table uses.
JRN_NAME	The name of the journal used by a source table. An asterisk followed by nine blanks in this column means that the source table is not currently journaled in a journal. Therefore, it is not possible to capture data for this source table.
FR_START_TIME	Full refresh start time. This column is updated by Capture for AS/400, not the Control Center, during operations.
SOURCE_VIEW_QUAL	Supports the view of subscriptions by matching the similar column in the register table. This value is set to equal 0 for physical tables that are defined as a source and is greater than 0 for views that are defined as sources. You must have this column to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.

Pruning control table

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

ASN.IBMSNAP_PRUNCNTL

The pruning control table coordinates the pruning of the change data (CD) tables, which have the potential for unlimited growth. For each new subscription, the Apply program first updates the pruning control table and then it begins a full refresh for the new subscription. After the full refresh, the Capture program begins capturing changes from the replication source. When the Capture program begins to capture data, it updates the pruning control table to notify the Apply program. During each Apply cycle, the Apply program updates the pruning control table to indicate the last change applied. The Capture program then uses the information to prune the CD and UOW tables.

The rows in the pruning control table are not deleted during a cold start of the Capture program. The administration tools use the values from the pruning control table to provide a list of copies defined as source tables and views.

There is one pruning control table at each source server and one row in the pruning control table for each subscription-set member.

You can manually prune your table by issuing the prune command or have it done automatically by updating the PRUNE_INTERVAL column in the tuning parameters table. See Tuning parameters table for more information about using the tuning parameters table.

Use this table to monitor the pruning status of your CD and UOW tables.

Table 45 provides a brief description of each of the pruning control table columns.

Table 45. Pruning Control Table Columns

Column name Description
TARGET_SERVER The remote database (RDB) name of the server where target tables or views are stored.
TARGET_OWNER A qualifier for a target table or view.
TARGET_TABLE The target to which data is being applied.
SYNCHTIME A source server timestamp. The SYNCHTIME value equals the SYNCHTIME field value in the subscription set table. The Capture program or an external program, such as DataPropagator NonRelational, updates this timestamp whether there are changes to be processed or not.
The Apply program uses this value when advanced conflict detection is selected for update-anywhere replication to ensure that the Capture program captures all outstanding changes for a replication source table.
SYNCHPOINT The SYNCHPOINT value equals the SYNCHPOINT field value in the subscription set table. This value is used to coordinate the pruning of CD tables. The Apply program sets this initial value to hex 0s, indicating refresh. If the Apply program sets a nonzero value, the CD table can be eligible for pruning.
SOURCE_OWNER The owner of the source table or view.
SOURCE_TABLE The source from which data is being captured.
SOURCE_VIEW_QUAL Supports the view of physical tables by matching the similar column in the register table. This value is set to 0 for physical tables that are defined as sources and is greater than 0 for views that are defined as sources. This column is used to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.
APPLY_QUAL A unique identifier for a group of subscription sets. This case sensitive value is supplied by the user when defining a subscription set. This column is part of the foreign key from the subscription set table. See Subscription set table for more details.
SET_NAME An identifier for a group of subscription-set members. This value is supplied by the user when defining a subscription set. This column is part of the foreign key from the subscription set table. See Subscription set table for more details.
CNTL_SERVER The RDB name of the control server for the Apply program updating this row.
TARGET_STRUCTURE A value that identifies the type of target table or view:

1
Source table
2
Not available
3
CCD table
4
Point-in-time table
5
Base aggregate table
6
Change aggregate table
7
Replica table
8
User copy table
9
Row-replica table

CNTL_ALIAS The DB2 Universal Database alias corresponding to the control server named in the CNTL_SERVER column.

Column name	Description
TARGET_SERVER	The remote database (RDB) name of the server where target tables or views are stored.
TARGET_OWNER	A qualifier for a target table or view.
TARGET_TABLE	The target to which data is being applied.
SYNCHTIME	A source server timestamp. The SYNCHTIME value equals the SYNCHTIME field value in the subscription set table. The Capture program or an external program, such as DataPropagator NonRelational, updates this timestamp whether there are changes to be processed or not. The Apply program uses this value when advanced conflict detection is selected for update-anywhere replication to ensure that the Capture program captures all outstanding changes for a replication source table.
SYNCHPOINT	The SYNCHPOINT value equals the SYNCHPOINT field value in the subscription set table. This value is used to coordinate the pruning of CD tables. The Apply program sets this initial value to hex 0s, indicating refresh. If the Apply program sets a nonzero value, the CD table can be eligible for pruning.
SOURCE_OWNER	The owner of the source table or view.
SOURCE_TABLE	The source from which data is being captured.
SOURCE_VIEW_QUAL	Supports the view of physical tables by matching the similar column in the register table. This value is set to 0 for physical tables that are defined as sources and is greater than 0 for views that are defined as sources. This column is used to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.
APPLY_QUAL	A unique identifier for a group of subscription sets. This case sensitive value is supplied by the user when defining a subscription set. This column is part of the foreign key from the subscription set table. See Subscription set table for more details.
SET_NAME	An identifier for a group of subscription-set members. This value is supplied by the user when defining a subscription set. This column is part of the foreign key from the subscription set table. See Subscription set table for more details.
CNTL_SERVER	The RDB name of the control server for the Apply program updating this row.
TARGET_STRUCTURE	A value that identifies the type of target table or view: 1 Source table 2 Not available 3 CCD table 4 Point-in-time table 5 Base aggregate table 6 Change aggregate table 7 Replica table 8 User copy table 9 Row-replica table
CNTL_ALIAS	The DB2 Universal Database alias corresponding to the control server named in the CNTL_SERVER column.

Tuning parameters table

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

ASN.IBMSNAP_CCPPARMS

This table contains parameters that you can modify to control the performance of the Capture program. You can set these parameters to modify the length of time that you retain data in the CD table, the amount of time that the Capture program is allowed to lag in processing log records, how often data will be committed, and how often your CD and UOW tables are pruned. These modifications must be done manually because there are no DB2 DataPropagator processes that update this table after it is created. The Capture program can only read your modifications during its start processing; therefore, you should stop and start the Capture program if you want your modifications to take effect.

Table 46 provides a brief description of the tuning parameters table columns.

Table 46. Tuning Parameters Table Columns

Column name Description
RETENTION_LIMIT The age limit, in minutes, for keeping CD table rows. This value is used with the SYNCHPOINT column of the pruning control table to determine the pruning limit. Any change data rows older than this value are pruned, even if they have not been copied to all targets. Transactions rejected after update conflict detection will have their changes pruned by RETENTION_LIMIT aging, not by normal pruning. The default value is 10 080.
LAG_LIMIT The amount of time, in minutes, that the Capture program is allowed to lag in processing log records before it shuts itself down. During periods of high update frequency, full refreshes can be more economical than updates. The default value is 10 080.
COMMIT_INTERVAL The Capture program commit threshold, in seconds, for any inserts, updates, or deletes to the global UOW table and any pruning control tables. The default value is 30.
On systems that do not support ISOLATION (UR), this value should be less than the DB2 lock timeout value to prevent Apply program instances from timing out due to contention with the Capture program.
PRUNE_INTERVAL The Capture program commit threshold, in seconds, for automatic or manual pruning of CD and UOW rows that are no longer needed. The default value is 300. Values set lower save space, but increase processing costs. Values set higher require more CD and UOW table space, but decrease processing costs. There is no effect on table space or processing cost when the NOPRUNE option is selected.

Column name	Description
RETENTION_LIMIT	The age limit, in minutes, for keeping CD table rows. This value is used with the SYNCHPOINT column of the pruning control table to determine the pruning limit. Any change data rows older than this value are pruned, even if they have not been copied to all targets. Transactions rejected after update conflict detection will have their changes pruned by RETENTION_LIMIT aging, not by normal pruning. The default value is 10 080.
LAG_LIMIT	The amount of time, in minutes, that the Capture program is allowed to lag in processing log records before it shuts itself down. During periods of high update frequency, full refreshes can be more economical than updates. The default value is 10 080.
COMMIT_INTERVAL	The Capture program commit threshold, in seconds, for any inserts, updates, or deletes to the global UOW table and any pruning control tables. The default value is 30. On systems that do not support ISOLATION (UR), this value should be less than the DB2 lock timeout value to prevent Apply program instances from timing out due to contention with the Capture program.
PRUNE_INTERVAL	The Capture program commit threshold, in seconds, for automatic or manual pruning of CD and UOW rows that are no longer needed. The default value is 300. Values set lower save space, but increase processing costs. Values set higher require more CD and UOW table space, but decrease processing costs. There is no effect on table space or processing cost when the NOPRUNE option is selected.

Capture enqueue table (VM and VSE specific)

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

ASN.IBMSNAP_CCPENQ

The Capture enqueue table is used in the VM and VSE environments only. This table is used to ensure that there is only one Capture program running per database.

Table 47 provides a list and a brief description of the Capture enqueue table column.

Table 47. Capture Enqueue Table Column

Column name Description
LOCKNAME Unique name of the resource for this database.

Column name	Description
LOCKNAME	Unique name of the resource for this database.

Warm start table

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

ASN.IBMSNAP_WARM_START

This table is created in the same database as the register table and contains information that enables the Capture program to restart from the last log or journal record read. Use the information in this table to avoid a full refresh of your system.

The following three tables show platform-specific layouts of the warm start table. The first table shows the layout for all platforms other than VM/VSE and AS/400, the second table shows the VM/VSE layout, and the last table shows the AS/400 layout.

Table 48. Warm Start Table Columns

Column name Description
SEQ The last captured sequence number from the log or journal record. Used for quickly restarting following a shutdown or failure.
AUTHTKN The DB2 token for the unit of work associated with the SEQ log or journal record.
AUTHID The DB2 authorization ID for the unit of work associated with the SEQ log or journal record.
CAPTURED A flag indicating whether or not this unit of work was captured.

Y
This unit of work was captured.
N
This unit of work was not captured.

UOWTIME The MVS time of day, or Windows NT, HP-UX, Sun Solaris, OS/2, and AIX Coordinated Universal Time (UTC) clock indicating when the unit of work associated with the SEQ position was captured (source server timestamp).

Column name	Description
SEQ	The last captured sequence number from the log or journal record. Used for quickly restarting following a shutdown or failure.
AUTHTKN	The DB2 token for the unit of work associated with the SEQ log or journal record.
AUTHID	The DB2 authorization ID for the unit of work associated with the SEQ log or journal record.
CAPTURED	A flag indicating whether or not this unit of work was captured. Y This unit of work was captured. N This unit of work was not captured.
UOWTIME	The MVS time of day, or Windows NT, HP-UX, Sun Solaris, OS/2, and AIX Coordinated Universal Time (UTC) clock indicating when the unit of work associated with the SEQ position was captured (source server timestamp).

Table 49. Warm Start Table Columns for VM and VSE Platforms

Column name Description
SEQ The last captured sequence number from the log or journal record. Used for quickly restarting following a shutdown or failure.
UOWID The unit-of-recovery ID from the log record header for this unit of work.
AUTHID The DB2 authorization ID for the unit of work associated with the SEQ log or journal record.
CAPTURED A flag indicating whether or not this unit of work was captured.

Y
This unit of work was captured.
N
This unit of work was not captured.

UOWTIME The VSE and VM time-of-day clock indicating when the unit of work associated with the SEQ log or journal record was captured (source server timestamp).

Column name	Description
SEQ	The last captured sequence number from the log or journal record. Used for quickly restarting following a shutdown or failure.
UOWID	The unit-of-recovery ID from the log record header for this unit of work.
AUTHID	The DB2 authorization ID for the unit of work associated with the SEQ log or journal record.
CAPTURED	A flag indicating whether or not this unit of work was captured. Y This unit of work was captured. N This unit of work was not captured.
UOWTIME	The VSE and VM time-of-day clock indicating when the unit of work associated with the SEQ log or journal record was captured (source server timestamp).

This AS/400 table is used to determine the starting time of the RCVJRNE (Receive Journal Entry) command. A row is inserted into the warm start table for each journal that is used by a replication source or a group of replication sources.

Table 50 provides a brief description of the warm start table columns for the AS/400 platform.

Table 50. Warm Start Table Columns for AS/400 Platform

Column name Description
JRN_LIB The library name of the journal.
JRN_NAME The name of the journal used by a source table. An asterisk followed by nine blanks in this column means that the source table is not currently journaled in a journal. Therefore, it is not possible to capture data for this source table.
JRN_JOB_NUMBER The job number of the current job for a particular journal. If the journal is not active, this column contains the job number of the last job that was processed.
LOGMARKER The timestamp of the last processed journal entry.
UID A unique number that is used as a prefix for the contents of the IBMSNAP_UOWID column located in the ASN.IBMSNAP_UOW tables.
SEQNBR The sequence number of the last processed journal entry.

Column name	Description
JRN_LIB	The library name of the journal.
JRN_NAME	The name of the journal used by a source table. An asterisk followed by nine blanks in this column means that the source table is not currently journaled in a journal. Therefore, it is not possible to capture data for this source table.
JRN_JOB_NUMBER	The job number of the current job for a particular journal. If the journal is not active, this column contains the job number of the last job that was processed.
LOGMARKER	The timestamp of the last processed journal entry.
UID	A unique number that is used as a prefix for the contents of the IBMSNAP_UOWID column located in the ASN.IBMSNAP_UOW tables.
SEQNBR	The sequence number of the last processed journal entry.

Critical section table

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

ASN.IBMSNAP_CRITSEC

This table is used to prevent circular replication in an update-anywhere scenario.

Table 51 provides a brief description of the critical section table column.

Table 51. Critical Section Table Column

Column name Description
APPLY_QUAL A unique identifier for a group of subscription sets. This value is supplied by the user when defining a subscription set. Each Apply process is started with an APPLY_QUAL. This value is used during update-anywhere replication to prevent circular propagation of the changes made by the Apply program. See Subscription set table for more details.

Column name	Description
APPLY_QUAL	A unique identifier for a group of subscription sets. This value is supplied by the user when defining a subscription set. Each Apply process is started with an APPLY_QUAL. This value is used during update-anywhere replication to prevent circular propagation of the changes made by the Apply program. See Subscription set table for more details.

Prune lock table

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

ASN.IBMSNAP_PRUNE_LOCK

The prune lock table is used to serialize the access of staging tables during a cold start or retention limit pruning. There are no rows in this table. The Capture and Apply programs use this table as a logical lock to serialize their operations during these critical phases.

Trace table

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

ASN.IBMSNAP_TRACE

This table contains audit trail information for the Capture program. Everything that is done by the Capture program is recorded in this table, which makes it one of the best places to look if a problem with the Capture program occurs. If you issue a cold start, all of the trace table's entries are deleted, so you might want to save a copy of this table before you issue a cold start command.

The following two tables show platform-specific layouts of the trace table. Table 52 shows the layout for all platforms other than AS/400, and Table 53 shows the AS/400 layout.

Table 52. Trace Table Columns

Column name Description
OPERATION The type of Capture program operation, for example, initialization, capture, or error condition.
TRACE_TIME The time that a row is inserted into the trace table.
DESCRIPTION The message ID followed by the message text. The message can be informational or error. This column contains English-only text. See Capture and Apply messages for a detailed description of the correlating message ID in the DESCRIPTION column.

Column name	Description
OPERATION	The type of Capture program operation, for example, initialization, capture, or error condition.
TRACE_TIME	The time that a row is inserted into the trace table.
DESCRIPTION	The message ID followed by the message text. The message can be informational or error. This column contains English-only text. See Capture and Apply messages for a detailed description of the correlating message ID in the DESCRIPTION column.

Table 53. Trace Table Columns for AS/400

Column name Description
OPERATION The type of Capture program operation, for example, initialization, capture, or error condition.
TRACE_TIME The time that a row is inserted into the trace table.
JOB_NAME The fully qualified name of the job that wrote this trace entry.

position 1-10: 'QDPRCTL5' or the journal job name
position 11-20: The ID of the user who started the Capture program
position 21-26: The job number

JOB_STR_TIME The starting time of the job named in the JOB_NAME column.
DESCRIPTION The message ID followed by the message text. The message ID is the first 7 characters of the DESCRIPTION column. The message text starts at the 9th position of the DESCRIPTION column.

Column name	Description
OPERATION	The type of Capture program operation, for example, initialization, capture, or error condition.
TRACE_TIME	The time that a row is inserted into the trace table.
JOB_NAME	The fully qualified name of the job that wrote this trace entry. position 1-10: 'QDPRCTL5' or the journal job name position 11-20: The ID of the user who started the Capture program position 21-26: The job number
JOB_STR_TIME	The starting time of the job named in the JOB_NAME column.
DESCRIPTION	The message ID followed by the message text. The message ID is the first 7 characters of the DESCRIPTION column. The message text starts at the 9th position of the DESCRIPTION column.

Apply-qualifier-cross-reference table (AS/400 specific)

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

ASN.IBMSNAP_AUTHTKN

The Apply-qualifier-cross-reference table is used in the AS/400 environment only. This table is used during update-anywhere replication to keep track of the jobs run by a particular Apply qualifier.

Table 54 provides a brief description of the apply-qualifier-cross-reference table columns.

Table 54. Apply-Qualifier-Cross-Reference Table Columns

Column name Description
APPLY_QUAL A unique identifier for a group of subscription sets. This value is supplied by the user when defining a subscription set. Each Apply process is started with an APPLY_QUAL. This value is used during update-anywhere replication to prevent circular propagation of the changes made by the Apply program. See Subscription set table for more details.
IBMSNAP_AUTHTKN The job name associated with a transaction. Capture for AS/400 matches this column with the name of the job that issued the transaction to determine if a transaction is issued by either the Apply program or a user application. If the names match, then Capture for AS/400 copies the APPLY_QUAL column to the UOW row. If the names do not match, then Capture for AS/400 sets the APPLY_QUAL column of the UOW row blank. This column is not automatically copied to other tables; you must select it and copy it as a user data column.
IBMSNAP_LOGMARKER The approximate commit time at the source server.

Column name	Description
APPLY_QUAL	A unique identifier for a group of subscription sets. This value is supplied by the user when defining a subscription set. Each Apply process is started with an APPLY_QUAL. This value is used during update-anywhere replication to prevent circular propagation of the changes made by the Apply program. See Subscription set table for more details.
IBMSNAP_AUTHTKN	The job name associated with a transaction. Capture for AS/400 matches this column with the name of the job that issued the transaction to determine if a transaction is issued by either the Apply program or a user application. If the names match, then Capture for AS/400 copies the APPLY_QUAL column to the UOW row. If the names do not match, then Capture for AS/400 sets the APPLY_QUAL column of the UOW row blank. This column is not automatically copied to other tables; you must select it and copy it as a user data column.
IBMSNAP_LOGMARKER	The approximate commit time at the source server.

Register synchronization table

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

ASN.IBMSNAP_REG_SYNCH

There is an update trigger on this table that initiates an update of the SYNCHPOINT value for all the rows in the register table when the Apply program fetches data from a non-IBM data source.

Table 55 provides a brief description of the register synchronization table column.

Table 55. Register Synchronization Table Column

Column name Description
TRIGGER_ME

Y
A trigger was initiated to update the SYNCHPOINT value for all rows in the register table.

Column name	Description
TRIGGER_ME	Y A trigger was initiated to update the SYNCHPOINT value for all rows in the register table.

Unit-of-work table

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

ASN.IBMSNAP_UOW

The unit-of-work (UOW) table ensures data integrity by recording transactions that were committed at the source server. The Apply program joins the UOW and CD table based on matching IBMSNAP_UOWID values to ensure that only committed changes are being copied. The results are ordered by the log or journal record sequence number of the change in the CD table within the committed units of work. If you issue a cold start, all of this table's entries are deleted, so you might want to save a copy of this table before you issue a cold start command.

The Capture program requires that there is one UOW table for each source server. The Capture program inserts one new row into this table for every log or journal record that commits changes to replication sources. The Capture program also prunes the UOW table based on information inserted into the pruning control table by the Apply program.

Table 56 provides a brief description of the UOW table columns.

Table 56. UOW Table Columns

Column name Description
IBMSNAP_UOWID The unit-of-work identifier from the log record header for this unit of work.
IBMSNAP_COMMITSEQ The log record sequence number of the captured commit statement.
IBMSNAP_LOGMARKER The approximate commit time at the source server.
IBMSNAP_AUTHTKN The authorization token associated with the transaction. This ID is useful for database auditing. For DB2 for OS/390, this column is the correlation ID. For DB2 for AS/400, this column is the job name of the job that caused a transaction. This column is not automatically copied to other tables; you must select it and copy it as a user data column. This user data column should be a CCD target type.
IBMSNAP_AUTHID The authorization ID associated with the transaction. It is useful for database auditing. For DB2 for OS/390, this column is the primary authorization ID. For DB2 for AS/400, this column has the name of the user profile ID under which the application that caused the transaction ran. This column holds a 10-character ID padded with blanks. This column is not automatically copied to other tables; you must select it and copy it as a user data column. This user data column should be a CCD target type.
IBMSNAP_REJ_CODE This value is set only during update-anywhere replication if conflict detection is specified as standard or advanced when you define your replication source.

0
A transaction with no known conflict.
1
A transaction that contains a conflict where the same row in the source and replica tables have a change that was not propagated. When a conflict occurs, the transaction will be reversed at the replica table.
2
A cascade-rejection of a transaction dependent on a prior transaction having at least one same-row conflict. When a conflict occurs, the transaction will be reversed at the replica table.
3
A transaction that contains at least one referential-integrity constraint violation. Because this transaction violates the referential constraints defined on the source table, the Apply program will mark this subscription set as failed. Updates cannot be copied until the referential integrity definitions are corrected.
4
For a cascade-rejection of a transaction dependent on a prior transaction having at least one constraint conflict.

IBMSNAP_APPLY_QUAL This column prevents circular replication during update-anywhere processing. It remains blank for local updates, but contains the name of the associated Apply program for updates that are made by the Apply program for an update-anywhere subscription set. The Capture program derives this value from the critical section table.

Column name	Description
IBMSNAP_UOWID	The unit-of-work identifier from the log record header for this unit of work.
IBMSNAP_COMMITSEQ	The log record sequence number of the captured commit statement.
IBMSNAP_LOGMARKER	The approximate commit time at the source server.
IBMSNAP_AUTHTKN	The authorization token associated with the transaction. This ID is useful for database auditing. For DB2 for OS/390, this column is the correlation ID. For DB2 for AS/400, this column is the job name of the job that caused a transaction. This column is not automatically copied to other tables; you must select it and copy it as a user data column. This user data column should be a CCD target type.
IBMSNAP_AUTHID	The authorization ID associated with the transaction. It is useful for database auditing. For DB2 for OS/390, this column is the primary authorization ID. For DB2 for AS/400, this column has the name of the user profile ID under which the application that caused the transaction ran. This column holds a 10-character ID padded with blanks. This column is not automatically copied to other tables; you must select it and copy it as a user data column. This user data column should be a CCD target type.
IBMSNAP_REJ_CODE	This value is set only during update-anywhere replication if conflict detection is specified as standard or advanced when you define your replication source. 0 A transaction with no known conflict. 1 A transaction that contains a conflict where the same row in the source and replica tables have a change that was not propagated. When a conflict occurs, the transaction will be reversed at the replica table. 2 A cascade-rejection of a transaction dependent on a prior transaction having at least one same-row conflict. When a conflict occurs, the transaction will be reversed at the replica table. 3 A transaction that contains at least one referential-integrity constraint violation. Because this transaction violates the referential constraints defined on the source table, the Apply program will mark this subscription set as failed. Updates cannot be copied until the referential integrity definitions are corrected. 4 For a cascade-rejection of a transaction dependent on a prior transaction having at least one constraint conflict.
IBMSNAP_APPLY_QUAL	This column prevents circular replication during update-anywhere processing. It remains blank for local updates, but contains the name of the associated Apply program for updates that are made by the Apply program for an update-anywhere subscription set. The Capture program derives this value from the critical section table.

Change data table

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

Change data (CD) tables record all changes made to a replication source. Committed, uncommitted, and incomplete changes are inserted as rows into the CD table. The CD table works with the UOW table to provide commit information. (See Consistent-change-data (CCD) tables for more information.) Pruning of the CD table rows is coordinated by the pruning control table. (See Pruning control table for more information.)

CD tables are automatically created when you define a replication source. For each replication source that is enabled for data capture, there is one CD table. If you issue a cold start, all of the CD table's entries are deleted.

Manually changing the CD table is not recommended. However, the CD table can be a useful resource for problem determination. Knowing exactly what changes were committed or not committed can help you understand where the Capture program failed.

Recommendation: Although the Control Center automatically creates an index, a unique ascending index is strongly recommended for the IBMSNAP_UOWID and IBMSNAP_INTENTSEQ columns.

Table 57 provides a list and a brief description of each of the CD table columns.

Table 57. CD Table Columns

Column name Description
IBMSNAP_UOWID Unit-of-work ID for an update. The Apply program uses this column to join the CD table with the UOW table so that only committed changes are replicated.
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.
DATA1 User column from source table specified by the user when defining replication sources.
AFTER-IMAGE User column from source table selected by the user when defining a replication source. This column will have the same name, data type, and null attributes as the source column. The after-image column also contains the equivalent source table column value after the change has been made.
BEFORE-IMAGE User column from source table selected by the user when defining a replication source. This column will have the same name, data type, and null attributes as the source column. The name is the source column prefixed with the BEFORE_IMG_PREFIX value from the register table. This column contains the equivalent source table column value before the change was made.

Column name	Description
IBMSNAP_UOWID	Unit-of-work ID for an update. The Apply program uses this column to join the CD table with the UOW table so that only committed changes are replicated.
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.
DATA1	User column from source table specified by the user when defining replication sources.
AFTER-IMAGE	User column from source table selected by the user when defining a replication source. This column will have the same name, data type, and null attributes as the source column. The after-image column also contains the equivalent source table column value after the change has been made.
BEFORE-IMAGE	User column from source table selected by the user when defining a replication source. This column will have the same name, data type, and null attributes as the source column. The name is the source column prefixed with the BEFORE_IMG_PREFIX value from the register table. This column contains the equivalent source table column value before the change was made.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]