Replication Guide and Reference

Control Tables Used at the Source Server

Table 23 lists the tables that are used at the control server and their internal names and provides a brief description of each table. The structure of all control tables is subject to change.

Table 23. Control Tables Used at the Source Server

Table name Internal name Description
Capture enqueue table ASN.IBMSNAP_CCPENQ
Change data table userid.ASNtimestampCD This table is created while defining replication sources. It contains changed data information. The timestamp in the table name is from the source server; for auto-registered tables, the timestamp is from the target server.
Consistent change data table userid.target_table There are two types of consistent change data tables; internal and external. The internal table is a join of the change data and UOW tables. An external consistent change data table is also called an external source table because it originates outside the IBM Replication environment. Any table created outside that environment that conforms to the consistent change data table format is an external source table.
Critical section table ASN.IBMSNAP_CRITSEC This table is used as a logical lock between the Capture program and the Apply program for serialization of internal logic.
Pruning control table ASN.IBMSNAP_PRUNCNTL This table contains pruning control information for the current source server. There is one at each source server and one row per source-to-target copy. It translates time stamp/log address, allows the Apply program to trigger the Capture program, and coordinates pruning.
Register table ASN.IBMSNAP_REGISTER This table contains information about replication sources, such as the names of the replication source tables, their attributes, and their staging table names.
Trace table ASN.IBMSNAP_TRACE This table is required if the Capture program is installed. It contains Capture program audit trail information.
Tuning parameters table ASN.IBMSNAP_CCPPARMS This table contains parameters that can be modified to control the performance of the Capture program.
Unit-of-work table ASN.IBMSNAP_UOW This table is required for transaction consistency. It is a staging table that contains commit information. The information from this table is joined with change information from the change data table to produce consistent, committed changes.
Warm start table ASN.IBMSNAP_WARM_START This table contains information that enables the Capture program to resynchronize.
Warm start table for Capture for VSE and VM ASN.IBMSNAP_WARM_START This table contains information that enables the Capture program to resynchronize in the VM and VSE environments.

Table name	Internal name	Description
Capture enqueue table	ASN.IBMSNAP_CCPENQ
Change data table	`userid`.ASNtimestampCD	This table is created while defining replication sources. It contains changed data information. The timestamp in the table name is from the source server; for auto-registered tables, the timestamp is from the target server.
Consistent change data table	userid.target_table	There are two types of consistent change data tables; internal and external. The internal table is a join of the change data and UOW tables. An external consistent change data table is also called an external source table because it originates outside the IBM Replication environment. Any table created outside that environment that conforms to the consistent change data table format is an external source table.
Critical section table	ASN.IBMSNAP_CRITSEC	This table is used as a logical lock between the Capture program and the Apply program for serialization of internal logic.
Pruning control table	ASN.IBMSNAP_PRUNCNTL	This table contains pruning control information for the current source server. There is one at each source server and one row per source-to-target copy. It translates time stamp/log address, allows the Apply program to trigger the Capture program, and coordinates pruning.
Register table	ASN.IBMSNAP_REGISTER	This table contains information about replication sources, such as the names of the replication source tables, their attributes, and their staging table names.
Trace table	ASN.IBMSNAP_TRACE	This table is required if the Capture program is installed. It contains Capture program audit trail information.
Tuning parameters table	ASN.IBMSNAP_CCPPARMS	This table contains parameters that can be modified to control the performance of the Capture program.
Unit-of-work table	ASN.IBMSNAP_UOW	This table is required for transaction consistency. It is a staging table that contains commit information. The information from this table is joined with change information from the change data table to produce consistent, committed changes.
Warm start table	ASN.IBMSNAP_WARM_START	This table contains information that enables the Capture program to resynchronize.
Warm start table for Capture for VSE and VM	ASN.IBMSNAP_WARM_START	This table contains information that enables the Capture program to resynchronize in the VM and VSE environments.

+---------------Diagnosis, Modification or Tuning information----------------+

Capture Enqueue Table

ASN.IBMSNAP_CCPENQ

The Capture enqueue table is used in the MV and VSE environments only.

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

Table 24. Capture Enque Table Column

Column name Description
LOCKNAME The unique name of the resource for this database.

Column name	Description
LOCKNAME	The unique name of the resource for this database.

Figure 34 shows the index for Capture enqueue tables.

Figure 33. CREATE TABLE Statement

CREATE TABLE useridIBMSNAP_CCPENQ LOCKNAME CHAR(9) NOT NULL);

+------------End of Diagnosis, Modification or Tuning information------------+

+------------------Product-sensitive programming interface-------------------+

Change Data Table

userid.ASNtimestampCD

CD tables contain changed data assigned to source table rows, in time series. The changed data is used to update other target tables. There is one CD table for each base table, created while defining a replication source that is enabled for data capture.

CD tables can contain both committed and uncommitted changes, and possibly incomplete changes in rows.

Commit sequencing, which is not known when records are inserted into the CD table, is provided through the unit-of-work table.

Normally, the Capture program inserts rows into this table for a particular platform. It also deletes rows periodically.

The Control Center automatically creates the indexes. A non-unique descending index is required on IBMSNAP_UOW.

Other qualities of CD tables are as follows:

Before-image user data columns and computed user data columns should be nullable.
Null attributes of the after-image user data columns should match null attributes of the source.
Views of change data tables can be included in view replication sources.
If standard or enhanced conflict detection is used for replicas, all before-image and after-image column values must be captured.
DPROPR V1 supported an IBMSNAP_LOGMARKER column in the changed data tables. For compatibility with existing installations, the presence of the once optional IBMSNAP_LOGMARKER column is tolerated. This column is no longer used.

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

Table 25. CD Table Columns

Column name Description
IBMSNAP_UOW Unit-of-work ID. It is also the foreign key into the ASN.IBMSNAP_COMMIT table.
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.
<user data columns> Columns from source tables specified while defining replication sources.

Column name	Description
IBMSNAP_UOW	Unit-of-work ID. It is also the foreign key into the ASN.IBMSNAP_COMMIT table.
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.
<user data columns>	Columns from source tables specified while defining replication sources.

Figure 34 shows the index for CD tables.

Figure 34. CREATE TABLE Statement

CREATE TABLE CD ( IBMSNAP_UOWID CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_INTENTSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_OPERATION CHAR(1) NOT NULL, KEY1 CHAR(1), DATA1 CHAR(1) ) CREATE INDEX ZCD ON CD(IBMSNAP_UOWID DESC);

+---------------End of Product-sensitive programming interface---------------+

+---------------Diagnosis, Modification or Tuning information----------------+

Critical Section Table

ASN.IBMSNAP_CRITSEC

The critical section table is used for concurrence control purposes between the Capture and Apply programs.

Table 26 provides a list and a brief description of each of the CCD table columns.

Table 26. Critical Section Table Columns

Column name Description
APPLY_QUAL The unique value used to associate each replication subscription to an Apply instance in the Apply qualifier field. This value must be supplied by the user during subscription. This value is used to populate the IBMSNAP_APPLY_QUAL column of the IBMSNAP_UOW table.

Column name	Description
APPLY_QUAL	The unique value used to associate each replication subscription to an Apply instance in the Apply qualifier field. This value must be supplied by the user during subscription. This value is used to populate the IBMSNAP_APPLY_QUAL column of the IBMSNAP_UOW table.

Figure 35 shows the index for critical section tables.

Figure 35. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_CRITSEC( APPLY_QUAL CHAR(18) NOT NULL, <+padding where row level locking not supported>) DATA CAPTURE CHANGES; CREATE UNIQUE INDEX IBMSNAP_CRITSECX ON ASN.IBMSNAP_CRITSEC (APPLY_QUAL);

+------------End of Diagnosis, Modification or Tuning information------------+

+------------------Product-sensitive programming interface-------------------+

Pruning Control Table

ASN.IBMSNAP_PRUNCNTL

The pruning control table provides a timestamp and log-address translation in case the SYNCHPOINT column for Capture for MVS is a relative byte address value.

Also, the pruning control table coordinates the pruning of the change data tables, which have the potential for unlimited growth. There is one pruning control table at each source server and one row for each subscription member.

This table is located at the source server, but it is also automatically created at the target server when replica and CCD target tables are defined. Replica and CCD target tables created at the target server can be used as source tables for further subscriptions, making the target server a source server as well. This is performed as part of auto-registration, described in Part 3. "Administering Your Replication System"

The rows in the pruning control table are not deleted during a cold start of the Capture program. The Control Center uses the values from the pruning control table to list direct copies of a particular source table.

Table 27 provides a list and a brief description of each of the pruning control table columns.

Table 27. Pruning Control Table Columns

Column name Description
TARGET_SERVER The target server to the Apply program updating this row; also, the name of the database (DRDA application server) where the target is stored. For DB2 for OS/2, there is no DRDA application server, so this column is informational. Matches the column in the IBMSNAP_SUBS_SET table of the same name.
TARGET_OWNER The middle qualifier of the target table name; default is the user ID of the user defining the subscription. Matches the column in the IBMSNAP_SUBS_MEMBR table of the same name.
TARGET_TABLE The name of the target table as defined in the subscription default as the source server name. Matches the column in the IBMSNAP_SUBS_MEMBR table of the same name.
SYNCHTIME A source server timestamp, which can be added to any captured log records, for any source table, indicating that the change did not occur before this time. If the log records are individually timestamped, use those timestamps; otherwise, these values are approximate and are set by the Apply program at the start of a subscription cycle and after each subsequent cycle. They are eventually appended into each change data table row.
SYNCHPOINT The SYNCHPOINT value equals the SYNCHPOINT field value in the common subscription set table. This value is used to coordinate the pruning of change data tables. The Apply program sets this initial value to 0, indicating refresh. If the Apply program sets a nonzero value, the change data table can be eligible for pruning.
SOURCE_OWNER The middle-level qualifier of the source table.
SOURCE_TABLE The name of the source table.
SOURCE_VIEW_QUAL Supports join subscriptions by matching the similar columns in ASN.IBMSNAP_REGISTER. Without this column, it would not be possible to support multiple subscriptions for the different source views with identical SOURCE_OWNER, SOURCE_TABLE values.
APPLY_QUAL Part of the foreign key from the IBMSNAP_SUBS_SET table.
SET_NAME Part of the foreign key from the IBMSNAP_SUBS_SET table.
CNTL_SERVER The RDB name of the control server for the Apply programupdating this row; either Apply for MVS, VSE, or VM.
TARGET_STRUCTURE A value that identifies the type of target table:

1
Source table
2
N/A
3
CCD table
4
Point-in-time table
5
Base aggregate table
6
Change aggregate table
7
Replica
8
User copy

CNTL_ALIAS The name of the control server used by the Apply program on the DB2 Universal Database. This identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This does not necessarily match the alias of the control server used by the Apply program for the DB2 Universal Database.

Column name	Description
TARGET_SERVER	The target server to the Apply program updating this row; also, the name of the database (DRDA application server) where the target is stored. For DB2 for OS/2, there is no DRDA application server, so this column is informational. Matches the column in the IBMSNAP_SUBS_SET table of the same name.
TARGET_OWNER	The middle qualifier of the target table name; default is the user ID of the user defining the subscription. Matches the column in the IBMSNAP_SUBS_MEMBR table of the same name.
TARGET_TABLE	The name of the target table as defined in the subscription default as the source server name. Matches the column in the IBMSNAP_SUBS_MEMBR table of the same name.
SYNCHTIME	A source server timestamp, which can be added to any captured log records, for any source table, indicating that the change did not occur before this time. If the log records are individually timestamped, use those timestamps; otherwise, these values are approximate and are set by the Apply program at the start of a subscription cycle and after each subsequent cycle. They are eventually appended into each change data table row.
SYNCHPOINT	The SYNCHPOINT value equals the SYNCHPOINT field value in the common subscription set table. This value is used to coordinate the pruning of change data tables. The Apply program sets this initial value to 0, indicating refresh. If the Apply program sets a nonzero value, the change data table can be eligible for pruning.
SOURCE_OWNER	The middle-level qualifier of the source table.
SOURCE_TABLE	The name of the source table.
SOURCE_VIEW_QUAL	Supports join subscriptions by matching the similar columns in ASN.IBMSNAP_REGISTER. Without this column, it would not be possible to support multiple subscriptions for the different source views with identical SOURCE_OWNER, SOURCE_TABLE values.
APPLY_QUAL	Part of the foreign key from the IBMSNAP_SUBS_SET table.
SET_NAME	Part of the foreign key from the IBMSNAP_SUBS_SET table.
CNTL_SERVER	The RDB name of the control server for the Apply programupdating this row; either Apply for MVS, VSE, or VM.
TARGET_STRUCTURE	A value that identifies the type of target table: 1 Source table 2 N/A 3 CCD table 4 Point-in-time table 5 Base aggregate table 6 Change aggregate table 7 Replica 8 User copy
CNTL_ALIAS	The name of the control server used by the Apply program on the DB2 Universal Database. This identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This does not necessarily match the alias of the control server used by the Apply program for the DB2 Universal Database.

Figure 36 shows the index for pruning control tables.

Figure 36. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_PRUNCNTL ( TARGET_SERVER CHAR (18) NOT NULL, TARGET_OWNER CHAR (18) NOT NULL, TARGET_TABLE CHAR(18) NOT NULL, SYNCHTIME TIMESTAMP SYNCHPOINT CHAR(10) FOR BIT DATA, SOURCE_OWNER CHAR (18), SOURCE_TABLE CHAR (18), SOURCE_VIEW_QUAL SMALLINT, APPLY_QUAL CHAR (18), SET_NAME CHAR (18), CNTL_SERVER CHAR(18) NOT NULL, TARGET_STRUCTURE SMALLINT NOT NULL, CNTL_ALIAS CHAR(8) < + padding where row level locking not supported>) DATA CAPTURE CHANGES; CREATE UNIQUE INDEX IBMSNAP_PRUNCNTLX ON ASN.IBMSNAP_PRUNCNTL (SOURCE_OWNER, SOURCE_TABLE, SOURCE_VIEW_QUAL, SET NAME,TARGET_SERVER, TARGET_TABLE, TARGET_OWNER);

To protect against the potential for clone descriptions which differ only by the APPLY_QUAL value, that value is not included in the index and each entry is required to be posted at the time of subscription definition.

+---------------End of Product-sensitive programming interface---------------+

+------------------Product-sensitive programming interface-------------------+

Register Table

ASN.IBMSNAP_REGISTER

The register table holds information about Replication Sources objects at the source server.

A unique index is automatically created on columns SOURCE_OWNER and SOURCE_TABLE.

Table 28. Register Table Columns

SOURCE_OWNER The owner of the source table whose updates are being captured.
SOURCE_TABLE The name of the source table whose updates are being captured.
SOURCE_VIEW_QUAL This value is set to equal 0 for registered physical tables and is greater than 0 for registered views.
GLOBAL RECORD A flag that indicates whether this row is the global record. This record is maintained by the Capture program. If the Capture program is not installed, then there is no global record.

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

SOURCE_STRUCTURE The structure of the base table:

1
source table
3
CCD table
4
point-in-time copy
5
base aggregate copy
6
change aggregate copy
7
replica
8
user copy

SOURCE_CONDENSED A flag indicating:

Y
Changes can be netted out, with at most one row in the base table for every original table primary key value.
N
All changes must remain, retaining a complete update history.
A
Valid only for base aggregate or change aggregate tables.

SOURCE_COMPLETE
A flag indicating:

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

CD_OWNER The owner of the change data table.
CHANGE_DATA_TABLE The name of the table for captured updates to the base table (set while defining the replication source).
PHYS_CHANGE_OWNER The owner of the PHYS_CHANGE_TABLE. For a view replication source, the value equals the value of the replication source that includes the change data referenced in the change data view definition. Capture 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 repgistrations based on CCD copy table registrations that the Apply program maintains.
PHYS_CHANGE_TABLE The name of the physical CD or CCD table. For a view replication source, the value equals the value of the replication source definition that includes the change data table referenced in the change data view definition. Capture 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 registrations based on the CCD copy table the Apply program maintains.
CD_OLD_SYNCHPOINT The SYNCHPOINT value of the oldest row in the change data table. The Capture program sets this value when the change data table is emptied. If null, the Capture program sets an initial value (the RBA of the logmarker timestamp related to a refresh of the associated base table). You define whether the Capture and Apply programs set subsequent values when old rows are pruned from the CD table.
CD_NEW_SYNCHPOINT The SYNCHPOINT value associated with the most recent change inserted into the CHANGE DATA TABLE. If the Capture program has not inserted into the CHANGE DATA TABLE recently, then the value will not advance.
Note: This value reduces the need to prepare and open cursors on change data tables. CD_NEW_SYNCHPOINT can only reliably indicate a no-change condition, when CD_NEW_SYNCHPOINT is less than or equal to IBMSNAP_SUBS_SET.SYNCHPOINT. Change conditions are not reported as reliably because uncommitted work will cause this value to increase, even when there are no committed updates to replicate.

DISABLE_REFRESH A flag that indicates whether refresh queries are issued against the source table if a change data table becomes invalid, if a gap is detected, or if the Capture program cold starts. This prevents refresh activity from overloading the source database when the Capture program is restarted. You can set this flag manually, use a program at the source database site to set it, or have the Capture program set it automatically while executing an UPDATE operation.

0
Full refreshes are enabled.
1
Full refreshes are prevented.

This column is initialized to 1.
CCD_OWNER The owner of the local consistent change data table.
CONSISTENT CHANGE DATA TABLE The name of the staging table that contains committed-only captured updates as copied from a join of the local change data table (defined by a subscription definition, which names the source table and the unit-of-work table).
CCD_OLD_SYNCHPOINT The SYNCHPOINT value of the oldest row in the external consistent change data table. This value is set when one of the applications listed below is used to generate the CONSISTENT CHANGE DATA TABLE.
CCD_OLD_SYNCHPOINT is set in the following ways:

By the Control Center when the consistent change data table is being auto-registered. 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 CCD_TABLE.
Otherwise by the Apply program or another external application.

SYNCHPOINT The sequence value that is useful for maintaining the state of consistent change data table copies, subscription states, and for controlling pruning.
SYNCHTIME The timestamp associated with the SYNCHPOINT column.
CCD_CONDENSED A flag indicating:

Y
Changes can be netted out, with at most one row in the CCD table for every base table primary key value.
N
All changes must remain, retaining a complete update history.

CCD_COMPLETE
A flag indicating that:

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

ARCH_LEVEL The architectural level of the definition in the row. This level is defined by IBM, and for Version 5 is '0201'.
DESCRIPTION A field for user comments entered while 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 PREFIX_CHAR is as follows:

1
For an ASCII single-byte character system prefix character.
1
For 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 allows for shift-in and shift-out characters.

CONFLICT_LEVEL Where:

2
Enhanced detection with cascading transaction rejection.
1
Standard detection (no log flush to CD) with cascading transaction rejection.
0
No conflict detection.

Note:

CONFLICT_LEVEL 2 is only supported for DB2 for MVS version 4 or higher.
CONFLICT_LEVEL is assumed to never change and to be the same for all descendents of the user table.

PARTITION _KEYS_CHG This value is assumed to be the same for all the user table's dependent replics. Where:

N
The source table columns are:Not updated, if part of the primary key of a copy table

Not updated, if part of a target table space partitioning key (DB2 for MVS) or node positioning key (DB2 Parallel Edition)

Not updated, if referenced in any subscription predicate
Y
The columns in the SOURCE_TABLE that are included in the partitioning keys are never ever updated by applications. Updates are captured as two rows in the change data table with ascending IBMSNAP_INTENTSEQ values. The first row contains the IBMSNAP_OPERATION value, where 'D' = all other deletions, meaning the before-image of all columns. The second row contains the IBMSNAP_OPERATION value, where 'I' = all other insertions, meaning the after-image of all columns. The delete must have a lower IBMSNAP_INTENTSEQ value that the insert, so that the updated partitioning key will not allow out-migration from a particular partition. In such cases, the insert must come last so that the row will remain in the partition.
NULL
If this is the global control row.

Figure 37. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_REGISTER ( SOURCE_OWNER CHAR(18) NOT NULL, SOURCE_TABLE CHAR(18) NOT NULL, SOURCE_VIEW_QUAL SMALLINT NOT NULL, GLOBAL_RECORD CHAR(1) NOT NULL, SOURCE_STRUCTURE SMALLINT NOT NULL, SOURCE_CONDENSED CHAR(1) NOT NULL, SOURCE_COMPLETE CHAR(1) NOT NULL, CD_OWNER CHAR(18), CD_TABLE CHAR(18), PHYS_CHANGE_OWNER CHAR(18), PHYS_CHANGE_TABLE CHAR(18), CD_OLD_SYNCHPOINT CHAR(10) FOR BIT DATA, CD_NEW_SYNCHPOINT CHAR(10) FOR BIT DATA, DISABLE_REFRESH SMALLINT NOT NULL, CCD_OWNER CHAR(18), CD_TABLE CHAR(18), CCD_OLD_SYNCHPOINT CHAR(10) FOR BIT DATA, SYNCHPOINT CHAR(10) FOR BIT DATA, SYNCHTIME TIMESTAMP, CCD_CONDENSED CHAR(1), CCD_COMPLETE CHAR(1), ARCH_LEVEL CHAR(4) NOT NULL, DESCRIPTION CHAR(254), BEFORE_IMG_PREFIX VARCHAR(4), CONFLICT_LEVEL CHAR(1), PARTITION_KEYS_CHG CHAR(1), <+padding where row level locking not supported>); CREATE UNIQUE INDEX ZIBMSNAP_REGISTERX ON ASN.IBMSNAP_REGISTER (SOURCE_OWNER, SOURCE_TABLE, SOURCE_VIEW_QUAL);

+---------------End of Product-sensitive programming interface---------------+

+---------------Diagnosis, Modification or Tuning information----------------+

Trace Table

ASN.IBMSNAP_TRACE

This table contains trace information for Capture program.
Note:

On cold start, all of this table's entries will be deleted. On warm start, it will keep adding rows in a round robin fashion until the limit has been reached.
The message numbers ASN0100I, ASN0101W, ASN0102W are issued for warnings and initialization information.

Table 29. Trace Table Columns

Column name Description
OPERATION The type of Capture operation, for example, initialization, capture, or error condition.
TRACE_TIME Notes the time 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 and is used by IBM service personnel.

Column name	Description
OPERATION	The type of Capture operation, for example, initialization, capture, or error condition.
TRACE_TIME	Notes the time 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 and is used by IBM service personnel.

Figure 38. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_TRACE ( OPERATION CHAR(8) NOT NULL, TRACE_TIME TIMESTAMP NOT NULL, DESCRIPTION VARCHAR(254) NOT NULL); CREATE INDEX ASN.ZIBMSNAP_TRACE ON ASN.IBMSNAP_TRACE (TRACE_TIME ASC)

+------------End of Diagnosis, Modification or Tuning information------------+

+---------------Diagnosis, Modification or Tuning information----------------+

Tuning Parameters Table

ASN.IBMSNAP_CCPPARMS

This table contains parameters that you can modify to control the performance of the Capture program.

Table 30. Tuning Parameters Table Columns

Column name Description
RETENTION_LIMIT The age limit, in minutes, for keeping CD table rows. This value is used with PRUNE_LIMIT_SEQ to determine the pruning limit. Any change data rows older than this value are pruned, even if they have not been copied by all clients. The default value is 10,800.
LAG_LIMIT The amount of time, in minutes, that Capture program is allowed to lag in processing log records before it shuts itself down. During periods of high update frequency, refreshes can be more economical than updates. The default value is 10,800.
COMMIT_INTERVAL The Capture program commit threshold, in seconds, for any inserts, updates, or deletes to any CD tables, pruning control tables, and the global ASN.IBMSNAP_UOW table. 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 deletion of CD and UOW rows that are no longer needed. The default value is 300. Values set lower will save space, but will increase processing costs. Values set higher require more CD and UOW table space but decrease processing costs.

Column name	Description
RETENTION_LIMIT	The age limit, in minutes, for keeping CD table rows. This value is used with PRUNE_LIMIT_SEQ to determine the pruning limit. Any change data rows older than this value are pruned, even if they have not been copied by all clients. The default value is 10,800.
LAG_LIMIT	The amount of time, in minutes, that Capture program is allowed to lag in processing log records before it shuts itself down. During periods of high update frequency, refreshes can be more economical than updates. The default value is 10,800.
COMMIT_INTERVAL	The Capture program commit threshold, in seconds, for any inserts, updates, or deletes to any CD tables, pruning control tables, and the global ASN.IBMSNAP_UOW table. 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 deletion of CD and UOW rows that are no longer needed. The default value is 300. Values set lower will save space, but will increase processing costs. Values set higher require more CD and UOW table space but decrease processing costs.

Figure 39. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_CCPPARMS ( RETENTION_LIMIT INT, LAG_LIMIT INT, COMMIT_INTERVAL INT, PRUNE_INTERVAL INT)

+------------End of Diagnosis, Modification or Tuning information------------+

+------------------Product-sensitive programming interface-------------------+

Unit-of-Work Table

ASN.IBMSNAP_UOW

The unit-of-work (UOW) table ensures data integrity by recording transactions that have been committed at the source server. By performing a join of this table and the changes logged by the Capture program, the Apply program ensures that only committed changes are being copied.

This table is indexed into the change data (CD) table, ordered by the commit sequence. This order is necessary for the following reasons:

The CD table is assumed to contain uncommitted changes.
The CD table cannot be ordered in commit sequence without updating records after insertion, because the relative timing of row updates might not be reflected in the relative timing of commits.

Note:

This table is created automatically during the define replication sources process. The existence and use of this table are required if the Capture program is installed.

Table 31. UOW Table Columns

Column name Description
IBMSNAP_UOWID The unit-of-recovery ID from the log record header for this unit-of-work.
IBMSNAP_COMMITSEQ The transaction commit sequencing value.
IBMSNAP_LOGMARKER The approximate commit time at the source server.
IBMSNAP_AUTHTKN The authorization token associated with the transaction. It is useful for database auditing. For DB2 for MVS, this field is the correlation ID. This column is not automatically copied to other tables; you must select it and copy it as a user data column.
IBMSNAP_AUTHID The authorization ID associated with the transaction. It is useful for database auditing. For DB2 for MVS, this field is the primary authorization ID. This column is not automatically copied to other tables; you must select it and copy it as a user data column.
IBMSNAP_REJ_CODE The character value.

0
for a transaction with no known conflict
1
for a transaction containing at least one same-row intra-table update conflict
2
for a cascade-rejection of a transaction dependent on a prior transaction having at least one same-row conflict
3
for a transaction containing at least one constraint conflict, such as an inter-table referential constraint conflict
4
for a cascade-rejection of a transaction dependent on a prior transaction having at least one constraint conflict

IBMSNAP_APPLY_QUAL This column is blank for local updates and the name of the associated Apply program for foreign updates. The Capture program derives this value from the CRITSEC table.

Column name	Description
IBMSNAP_UOWID	The unit-of-recovery ID from the log record header for this unit-of-work.
IBMSNAP_COMMITSEQ	The transaction commit sequencing value.
IBMSNAP_LOGMARKER	The approximate commit time at the source server.
IBMSNAP_AUTHTKN	The authorization token associated with the transaction. It is useful for database auditing. For DB2 for MVS, this field is the correlation ID. This column is not automatically copied to other tables; you must select it and copy it as a user data column.
IBMSNAP_AUTHID	The authorization ID associated with the transaction. It is useful for database auditing. For DB2 for MVS, this field is the primary authorization ID. This column is not automatically copied to other tables; you must select it and copy it as a user data column.
IBMSNAP_REJ_CODE	The character value. 0 for a transaction with no known conflict 1 for a transaction containing at least one same-row intra-table update conflict 2 for a cascade-rejection of a transaction dependent on a prior transaction having at least one same-row conflict 3 for a transaction containing at least one constraint conflict, such as an inter-table referential constraint conflict 4 for a cascade-rejection of a transaction dependent on a prior transaction having at least one constraint conflict
IBMSNAP_APPLY_QUAL	This column is blank for local updates and the name of the associated Apply program for foreign updates. The Capture program derives this value from the CRITSEC table.

Figure 40. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_UOW ( IBMSNAP_UOWID CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_COMMITSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_LOGMARKER TIMESTAMP NOT NULL, IBMSNAP_AUTHTKN CHAR(12) NOT NULL, IBMSNAP_AUTHID CHAR(18) NOT NULL) IBMSNAP_REJ_CODE CHAR(1) NOT NULL WITH DEFAULT IMBSNAP_APPLY_QUAL CHAR(18) NOT NULL WITH DEFAULT CREATE UNIQUE INDEX ASN.ZIBMSNAP_UOW ON ASN.IBMSNAP_UOW (IBMSNAP_COMMITSEQ DESC) CREATE UNIQUE INDEX ASN.ZIBMSNAP_UOW2 ON ASN.IBMSNAP_UOW (IBMSNAP_UOWID DESC)

Note: On AS/400 systems, IBMSNAP_AUTHTKN is CHAR(26)

To improve fetch performance, create the following index:

CREATE UNIQUE INDEX U1yymmddhhmmssIX1 ON ASN.IBMSNAP_UOW (IBMSNAP_COMMITSEQ ASC, IBMSNAP_UOWID ASC, IBMSNAP_lOGMARKER ASC)

To improve pruning performance, create the following index:

For MVS:

CREATE UNIQUE INDEX U2yymmddhhmmssIX2 ON ASN.IBMSNAP_UOW (IBMSNAP_COMMITSEQ DESC, IBMSNAP_UOWID ASC)

For all other platforms:

CREATE UNIQUE INDEX U2yymmddhhmmssIX2 ON ASN.IBMSNAP_UOW (IBMSNAP_

+---------------End of Product-sensitive programming interface---------------+

+---------------Diagnosis, Modification or Tuning information----------------+

Warm Start Table

ASN.IBMSNAP_WARM_START

This table contains information that enables the Capture program to resynchronize (see the warm start section of the Capture and Apply programs chapter for your platform). It is created in the same database as ASN.IBMSNAP_REGISTER.

Table 32. Warm Start Table Columns

Column name Description
SEQ The log RBA for this unit of work. Records Capture's position in the DBMS log. Used for quickly restarting following a shutdown or failure.
AUTHTKN The DB2 token for the unit of work associated with the SEQ position.
AUTHID The DB2 authorization ID for the unit of work associated with the SEQ position.
CAPTURED A flag indicating whether or not this unit of work has been captured.

Y
This unit of work has been captured.
N
This unit of work has not been captured.

UOWTIME The MVS TOD (time of day) or OS/2 and AIX CUT (coordinated universal time) clock indicating when the unit of work associated with the SEQ position was captured (source server timestamp).

Column name	Description
SEQ	The log RBA for this unit of work. Records Capture's position in the DBMS log. Used for quickly restarting following a shutdown or failure.
AUTHTKN	The DB2 token for the unit of work associated with the SEQ position.
AUTHID	The DB2 authorization ID for the unit of work associated with the SEQ position.
CAPTURED	A flag indicating whether or not this unit of work has been captured. Y This unit of work has been captured. N This unit of work has not been captured.
UOWTIME	The MVS TOD (time of day) or OS/2 and AIX CUT (coordinated universal time) clock indicating when the unit of work associated with the SEQ position was captured (source server timestamp).

Figure 41. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_WARM_START ( SEQ CHAR(10) FOR BIT DATA, UOWID CHAR(10) FOR BIT DATA AUTHTKN CHAR(12), AUTHID CHAR(18), CAPTURED CHAR(1), UOWTIME INT);

+------------End of Diagnosis, Modification or Tuning information------------+

+---------------Diagnosis, Modification or Tuning information----------------+

Warm Start Table for Capture for VSE and VM

ASN.IBMSNAP_WARM_START

This table contains information that enables the Capture program to resynchronize for VSE and VM (see the warm start section for the Capture and Apply programs for VSE and VM). It is created in the same database as ASN.IBMSNAP_REGISTER.

Table 33. Warm Start for Capture for VSE and VM Table Columns

Column name Description
SEQ The log RBA for this unit of work. Records Capture's position in the DBMS log. 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 position.
CAPTURED A flag indicating whether or not this unit of work has been captured.

Y
This unit of work has been captured.
N
This unit of work has not been captured.

UOWTIME The MVS TOD (time of day) or OS/2 and AIX CUT (coordinated universal time) clock indicating when the unit of work associated with the SEQ position was captured (source server timestamp).

Column name	Description
SEQ	The log RBA for this unit of work. Records Capture's position in the DBMS log. 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 position.
CAPTURED	A flag indicating whether or not this unit of work has been captured. Y This unit of work has been captured. N This unit of work has not been captured.
UOWTIME	The MVS TOD (time of day) or OS/2 and AIX CUT (coordinated universal time) clock indicating when the unit of work associated with the SEQ position was captured (source server timestamp).

Figure 42. CREATE TABLE Statement

CREATE TABLE ASN.IBMSNAP_WARM_START ( SEQ CHAR(10) FOR BIT DATA, UOWID CHAR(10) FOR BIT DATA AUTHID CHAR(18), CAPTURED CHAR(1), UOWTIME INT) IN DROPTS;

+------------End of Diagnosis, Modification or Tuning information------------+

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

[ DB2 List of Books | Search the DB2 Books ]