Replication Guide and Reference

Tables used at the control server

The control server is the DB2 system that you chose to hold your subscription definitions. The following section provides a brief description of the tables used at the control server and the columns in each table. If you are using the Control Center, these tables, which contain information about your subscription definitions, are automatically created when you define a subscription if they do not already exist.

The subscription set, subscription-targets-member, subscription column, subscription statements, row-replica, and subscription-schema-changes tables contain information about subscriptions. When a new subscription set is defined, the administration tools simultaneously update rows in the subscription set, subscription columns, subscription-targets-member, and subscription statements tables.

The subscription events and Apply trail tables are used by the Apply program to control and audit your data.

Subscription set table

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

ASN.IBMSNAP_SUBS_SET

The subscription set table lists all of the subscription sets defined at the control server and identifies the source and target server pairs that are processed as a group. Rows are inserted into this table when you create your subscription definition.

Use this table to identify subscription sets that have been defined.

Table 58 provides a brief description of the subscription set table columns.

Table 58. Subscription Set Table Columns

Column name Description
APPLY_QUAL Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set. See Operations for more details.
SET_NAME Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction during the Apply program processing cycle.
WHOS_ON_FIRST The following values are used to control the order of processing in update-anywhere replication scenarios.

F
(first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.

ACTIVATE The following values are flags set by either the Control Center (0 and 1) or by the Apply program (2).

0
The subscription set is deactivated.
1
The subscription set is active indefinitely.
2
The subscription set is used for a one-time-only subscription execution.

SOURCE_SERVER The RDB name of the source server where the source tables and views are defined.
SOURCE_ALIAS The DB2 Universal Database alias corresponding to the source server named in the SOURCE_SERVER column.
TARGET_SERVER The RDB name of the server where the target tables and views are defined.
TARGET_ALIAS The DB2 Universal Database alias corresponding to the target server named in the TARGET_SERVER column.
STATUS A value that represents in-progress and completed work status for the Apply program.

-1
A failed execution.
0
A stable definition row.
1
A pending or in-progress execution. Do not modify this definition or any rows related to this subscription set in other control tables.
2
A continuing execution of a single logical subscription set that was divided according to the MAX_SYNCH_MINUTES control column and is being serviced by multiple subscription cycles. Do not modify this row or any row related to this subscription set in other control tables.

LASTRUN The estimated time that the last subscription set began. The Apply program sets the LASTRUN value each time a subscription set is processed. It is the approximate time at the control server that the Apply program begins processing the subscription set.
REFRESH_TIMING Sets the timing between statement executions.

R
The Apply program uses the value in SLEEP_MINUTES to determine replication timing.
E
The Apply program checks the time value in the SUBS_EVENT table to determine replication timing.
B
Indicates that a subscription set has both relative and event timing specifications. Therefore, this subscription set can be eligible for a refresh based on either the timer or event timing criteria.

SLEEP_MINUTES Specifies the time of inactivity between subscription set processing when REFRESH_TIMING = 'R' or 'B'.
EVENT_NAME A unique character string used to represent an event. Use this identifier to update the subscription events table when you want to trigger replication for a subscription set.
LASTSUCCESS The control server timestamp for the beginning of the last successful processing of a subscription set.
SYNCHPOINT The Apply program uses the SYNCHPOINT value from the global row of the register table at the source server if GLOBAL_RECORD = 'Y'. If data blocking is specified in the subscription set definition, then the SYNCHPOINT value is the log or journal record sequence number of the last change applied during the Apply process.
SYNCHTIME 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 has captured all outstanding changes for a replication source table.
MAX_SYNCH_MINUTES A time-threshold limit to regulate the amount of change data to fetch and apply during a subscription cycle. The Apply program breaks the subscription set processing into mini-cycles based on the IBMSNAP_LOGMARKER column in the UOW or CCD table at the source server and issues a COMMIT at the target server after each successful mini-cycle. The limit is automatically recalculated if the Apply program encounters a resource constraint that makes the set limit unfeasible. MAX_SYNCH_MINUTES values that are less than 1 will be treated the same as a MAX_SYNCH_MINUTES value equal to NULL.
AUX_STMTS The number of SQL BEFORE and AFTER statements that you define in the subscriptions statements table.
ARCH_LEVEL The architectural level of the definition contained in the row. This field identifies the rules under which a row was created.

Column name	Description
APPLY_QUAL	Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set. See Operations for more details.
SET_NAME	Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction during the Apply program processing cycle.
WHOS_ON_FIRST	The following values are used to control the order of processing in update-anywhere replication scenarios. F (first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions. S (second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.
ACTIVATE	The following values are flags set by either the Control Center (0 and 1) or by the Apply program (2). 0 The subscription set is deactivated. 1 The subscription set is active indefinitely. 2 The subscription set is used for a one-time-only subscription execution.
SOURCE_SERVER	The RDB name of the source server where the source tables and views are defined.
SOURCE_ALIAS	The DB2 Universal Database alias corresponding to the source server named in the SOURCE_SERVER column.
TARGET_SERVER	The RDB name of the server where the target tables and views are defined.
TARGET_ALIAS	The DB2 Universal Database alias corresponding to the target server named in the TARGET_SERVER column.
STATUS	A value that represents in-progress and completed work status for the Apply program. -1 A failed execution. 0 A stable definition row. 1 A pending or in-progress execution. Do not modify this definition or any rows related to this subscription set in other control tables. 2 A continuing execution of a single logical subscription set that was divided according to the MAX_SYNCH_MINUTES control column and is being serviced by multiple subscription cycles. Do not modify this row or any row related to this subscription set in other control tables.
LASTRUN	The estimated time that the last subscription set began. The Apply program sets the LASTRUN value each time a subscription set is processed. It is the approximate time at the control server that the Apply program begins processing the subscription set.
REFRESH_TIMING	Sets the timing between statement executions. R The Apply program uses the value in SLEEP_MINUTES to determine replication timing. E The Apply program checks the time value in the SUBS_EVENT table to determine replication timing. B Indicates that a subscription set has both relative and event timing specifications. Therefore, this subscription set can be eligible for a refresh based on either the timer or event timing criteria.
SLEEP_MINUTES	Specifies the time of inactivity between subscription set processing when REFRESH_TIMING = 'R' or 'B'.
EVENT_NAME	A unique character string used to represent an event. Use this identifier to update the subscription events table when you want to trigger replication for a subscription set.
LASTSUCCESS	The control server timestamp for the beginning of the last successful processing of a subscription set.
SYNCHPOINT	The Apply program uses the SYNCHPOINT value from the global row of the register table at the source server if GLOBAL_RECORD = 'Y'. If data blocking is specified in the subscription set definition, then the SYNCHPOINT value is the log or journal record sequence number of the last change applied during the Apply process.
SYNCHTIME	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 has captured all outstanding changes for a replication source table.
MAX_SYNCH_MINUTES	A time-threshold limit to regulate the amount of change data to fetch and apply during a subscription cycle. The Apply program breaks the subscription set processing into mini-cycles based on the IBMSNAP_LOGMARKER column in the UOW or CCD table at the source server and issues a COMMIT at the target server after each successful mini-cycle. The limit is automatically recalculated if the Apply program encounters a resource constraint that makes the set limit unfeasible. MAX_SYNCH_MINUTES values that are less than 1 will be treated the same as a MAX_SYNCH_MINUTES value equal to NULL.
AUX_STMTS	The number of SQL BEFORE and AFTER statements that you define in the subscriptions statements table.
ARCH_LEVEL	The architectural level of the definition contained in the row. This field identifies the rules under which a row was created.

Subscription-targets-member table

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

ASN.IBMSNAP_SUBS_MEMBR

This table or view contains information about the individual source and target table pairs defined for a subscription set. Rows are automatically inserted into this table when you create a subscription member.

Use this table or view to identify a specific source and target table pair within a subscription set.

Table 59 provides a brief description of the subscription-targets-member table columns.

Table 59. Subscription-Targets-Member Table Columns

Column name Description
APPLY_QUAL Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction during the Apply program processing cycle.
WHOS_ON_FIRST The following values are used to control the order of processing in update-anywhere replication scenarios.

F
(first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.

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.
TARGET_OWNER A qualifier for a target table or view.
TARGET_TABLE The target to which data is being applied.
TARGET_CONDENSED A flag indicating:

Y
For any given primary key, the target table shows only one row.
N
All changes must remain, retaining a complete update history.
A
Valid only for base aggregate or change aggregate tables.

TARGET_COMPLETE
A flag indicating:

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

TARGET_STRUCTURE The structure of the target table:

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

PREDICATES Lists the predicates to be placed in a WHERE clause to subset the horizontal fragment maintained in the TARGET_TABLE column. The letter 'A' is a predefined correlation-name for the physical source table used in a correlated subquery. Do not specify an ORDER BY clause because the Apply program can not generate an ORDER BY clause. Aggregate tables require a dummy predicate followed by a GROUP BY clause as a predicate.

Column name	Description
APPLY_QUAL	Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME	Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction during the Apply program processing cycle.
WHOS_ON_FIRST	The following values are used to control the order of processing in update-anywhere replication scenarios. F (first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions. S (second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.
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.
TARGET_OWNER	A qualifier for a target table or view.
TARGET_TABLE	The target to which data is being applied.
TARGET_CONDENSED	A flag indicating: Y For any given primary key, the target table shows only one row. N All changes must remain, retaining a complete update history. A Valid only for base aggregate or change aggregate tables.
TARGET_COMPLETE	A flag indicating: Y The target table contains a row for every primary key value of interest. N The target table contains some subset of rows of primary key values.
TARGET_STRUCTURE	The structure of the target table: 1 User table 3 CCD table 4 Point-in-time table 5 Base aggregate table 6 Change aggregate table 7 Replica 8 User copy 9 Row-replica (Microsoft Jet specific)
PREDICATES	Lists the predicates to be placed in a WHERE clause to subset the horizontal fragment maintained in the TARGET_TABLE column. The letter 'A' is a predefined correlation-name for the physical source table used in a correlated subquery. Do not specify an ORDER BY clause because the Apply program can not generate an ORDER BY clause. Aggregate tables require a dummy predicate followed by a GROUP BY clause as a predicate.

Subscription columns table

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

ASN.IBMSNAP_SUBS_COLS

This table contains information about the columns of the subscription-set members being copied in a subscription set. The subscription columns table contains supplemental information to the subscription-targets-member table.

Rows are automatically inserted or deleted from this table when information at the column level of a source and target table pair is changed.

Use this table if you need information about specific columns in a subscription-set member.

Table 60 provides a brief description of the subscription columns table columns.

Table 60. Subscription Columns Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription set. The value must be unique among all Apply program processes maintaining dependent replicas of a user table or parent replica, and unique among all Apply program processes sharing a common set of control tables. This value is case sensitive. You must specify this value when you define a subscription set.
SET_NAME Names a subscription set. This value is unique within an Apply qualifier.
WHOS_ON_FIRST The following values are used to control the order of processing in update-anywhere replication scenarios.

F
(first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.

TARGET_OWNER A qualifier for a target table or view.
TARGET_TABLE The target to which data is being applied.
COL_TYPE
A flag indicating:

A
For an after-image column.
B
For a before-image column.
C
For a computed column without a SQL column function reference.
F
For a computed column with a SQL column function reference.
L
A large object (LOB) column.
R
Signifies a relative record number column, provided by the system and used as a primary key column. Used only by DPROPR for AS/400.

TARGET_NAME The name of the target table or view column. It does not need to match the source column name.
Internal CCD column names cannot be renamed. They must match the CD table column names.
IS_KEY

Y
The column is all or part of the primary key of the target (all condensed copies must have primary keys).
N
The column is not part of a key of the target.

COLNO The numeric location of the column in the original source, to be preserved relative to other user columns in displays and subscriptions.
EXPRESSION The source column name or an SQL expression representing the target column.

Column name	Description
APPLY_QUAL	Identifies the Apply program for the platform instance that will run this subscription set. The value must be unique among all Apply program processes maintaining dependent replicas of a user table or parent replica, and unique among all Apply program processes sharing a common set of control tables. This value is case sensitive. You must specify this value when you define a subscription set.
SET_NAME	Names a subscription set. This value is unique within an Apply qualifier.
WHOS_ON_FIRST	The following values are used to control the order of processing in update-anywhere replication scenarios. F (first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions. S (second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.
TARGET_OWNER	A qualifier for a target table or view.
TARGET_TABLE	The target to which data is being applied.
COL_TYPE	A flag indicating: A For an after-image column. B For a before-image column. C For a computed column without a SQL column function reference. F For a computed column with a SQL column function reference. L A large object (LOB) column. R Signifies a relative record number column, provided by the system and used as a primary key column. Used only by DPROPR for AS/400.
TARGET_NAME	The name of the target table or view column. It does not need to match the source column name. Internal CCD column names cannot be renamed. They must match the CD table column names.
IS_KEY	Y The column is all or part of the primary key of the target (all condensed copies must have primary keys). N The column is not part of a key of the target.
COLNO	The numeric location of the column in the original source, to be preserved relative to other user columns in displays and subscriptions.
EXPRESSION	The source column name or an SQL expression representing the target column.

Subscription statements table

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

ASN.IBMSNAP_SUBS_STMTS

This table contains the user-defined SQL statements or stored procedure calls that will be executed before or after each subscription-set processing cycle. Execute immediately (EI) statements or stored procedures can be executed at the source or target server only. This table is populated when you define a subscription that uses SQL statements or stored procedure calls.

Table 61 provides a brief description of the subscription statements table columns.

Table 61. Subscription Statements Table Columns

Column name Description
APPLY_QUAL Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription-set members in a set are processed in a single transaction during the Apply program processing cycle.
WHOS_ON_FIRST The following values are used to control the order of processing in update-anywhere replication scenarios.

F
(first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.

BEFORE_OR_AFTER A value indicating:

A
The statement is executed at the target server after all of the answer set rows are applied.
B
The statement is executed at the target server before any of the answer set rows are applied.
S
The statement is executed at the source server before opening the answer set cursors.
G
The statement is executed at the source server before opening any cursors to either fetch answer set rows or fetch registration details.

STMT_NUMBER Defines the relative order of execution within the scope of BEFORE_OR_AFTER.
EI_OR_CALL A value indicating:

E
The SQL statement should be run as an EXEC SQL EXECUTE IMMEDIATE.
C
The SQL statement contains a stored procedure name to run as an EXEC SQL CALL.

SQL_STMT One of the following values:

Statement
The SQL statement should run as an EXEC SQL EXECUTE IMMEDIATE statement, if EI_OR_CALL = 'E'.
Procedure
The 8-byte name of an SQL-stored procedure, without parameters or the CALL keyword, that runs as an EXEC SQL CALL statement if EI_OR_CALL = 'C'.

ACCEPT_SQLSTATES One to ten 5-byte SQLSTATE values that you specified when you defined the subscription. These non-zero values are accepted by the Apply program as a successful execution. Any other value will cause a failed execution.

Column name	Description
APPLY_QUAL	Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME	Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription-set members in a set are processed in a single transaction during the Apply program processing cycle.
WHOS_ON_FIRST	The following values are used to control the order of processing in update-anywhere replication scenarios. F (first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions. S (second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.
BEFORE_OR_AFTER	A value indicating: A The statement is executed at the target server after all of the answer set rows are applied. B The statement is executed at the target server before any of the answer set rows are applied. S The statement is executed at the source server before opening the answer set cursors. G The statement is executed at the source server before opening any cursors to either fetch answer set rows or fetch registration details.
STMT_NUMBER	Defines the relative order of execution within the scope of BEFORE_OR_AFTER.
EI_OR_CALL	A value indicating: E The SQL statement should be run as an EXEC SQL EXECUTE IMMEDIATE. C The SQL statement contains a stored procedure name to run as an EXEC SQL CALL.
SQL_STMT	One of the following values: Statement The SQL statement should run as an EXEC SQL EXECUTE IMMEDIATE statement, if EI_OR_CALL = 'E'. Procedure The 8-byte name of an SQL-stored procedure, without parameters or the CALL keyword, that runs as an EXEC SQL CALL statement if EI_OR_CALL = 'C'.
ACCEPT_SQLSTATES	One to ten 5-byte SQLSTATE values that you specified when you defined the subscription. These non-zero values are accepted by the Apply program as a successful execution. Any other value will cause a failed execution.

Row-replica-target-list table (Microsoft Jet specific)

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

ASN.IBMSNAP_SUBS_TGTS

This table is necessary to identify when a member has been deleted from a subscription set for a Microsoft Jet database target, so that the row-replica table can be deleted from the Microsoft Jet database. The row-replica-target-list table allows DataPropagator for Microsoft Jet to maintain a list of known row-replica tables in a stable DB2 or DataJoiner database. DataPropagator for Microsoft Jet will use this information during schema analysis to determine if any row-replica tables should be deleted because the corresponding subscription-set member was dropped since the last synchronization.

Table 62 provides a brief description of the row-replica-target-list table columns.

Table 62. Row-Replica-Target-List Table Columns

Column name Description
APPLY_QUAL Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription-set.
SET_NAME Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier.
WHOS_ON_FIRST The following values are used to control the order of processing in update-anywhere replication scenarios.

F
(first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.

TARGET_OWNER A qualifier for a target table or view.
TARGET_TABLE The target to which data is being applied.
LAST_POSTED This column is the timestamp of when this row was inserted into the table. This column is for informational purposes only.

Column name	Description
APPLY_QUAL	Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription-set.
SET_NAME	Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier.
WHOS_ON_FIRST	The following values are used to control the order of processing in update-anywhere replication scenarios. F (first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions. S (second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.
TARGET_OWNER	A qualifier for a target table or view.
TARGET_TABLE	The target to which data is being applied.
LAST_POSTED	This column is the timestamp of when this row was inserted into the table. This column is for informational purposes only.

Subscription-schema-changes table (Microsoft Jet specific)

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

ASN.IBMSNAP_SCHEMA_CHG

This table allows DataPropagator for Microsoft Jet to quickly determine if some relevant schema change has occurred since its last synchronization. If a modification is made, DataPropagator for Microsoft Jet will drive a thorough analysis of the replication control information. DataPropagator for Microsoft Jet will then create or drop row-replica tables, or columns in row-replica tables, to automatically converge the Microsoft Jet database schema with the schema described by the replication control information. This schema convergence occurs before data synchronization, so that new columns and new tables are copied.

Table 63 provides a brief description of the subscription-schema-changes table columns.

Table 63. Subscription-Schema-Changes Table Columns

Column name Description
APPLY_QUAL Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction at the target site during the Apply program processing cycle.
LAST_CHANGED This column is the timestamp of when this row was last changed in this table. This column is for informational purposes only.

Column name	Description
APPLY_QUAL	Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME	Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction at the target site during the Apply program processing cycle.
LAST_CHANGED	This column is the timestamp of when this row was last changed in this table. This column is for informational purposes only.

Subscription events table

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

ASN.IBMSNAP_SUBS_EVENT

This table contains information about the event triggers that are copied in a subscription set. The subscription events table contains event names and timestamps associated with the event names. You insert a row into this table when you create a new event to execute the start of an Apply process. See Event timing.

Table 64 provides a brief description of the subscription events table columns.

Table 64. Subscription Events Table Columns

Column name Description
EVENT_NAME When you replicate events between systems, this column contains a globally unique character string in a global name space configuration. Otherwise, this column contains a control server unique character string.
EVENT_TIME A control server timestamp of a current or future posting time. User applications signalling replication events provide the values in this column.
END_OF_PERIOD A source server timestamp value that acts like an upper boundary. Any transactions that are committed after this period are not replicated, until a later event is posted.
The only way to prevent eligible change data from replicating during a subscription cycle is to make sure that the value in this column is less than the CURRENT TIMESTAMP value at the source server.

Column name	Description
EVENT_NAME	When you replicate events between systems, this column contains a globally unique character string in a global name space configuration. Otherwise, this column contains a control server unique character string.
EVENT_TIME	A control server timestamp of a current or future posting time. User applications signalling replication events provide the values in this column.
END_OF_PERIOD	A source server timestamp value that acts like an upper boundary. Any transactions that are committed after this period are not replicated, until a later event is posted. The only way to prevent eligible change data from replicating during a subscription cycle is to make sure that the value in this column is less than the CURRENT TIMESTAMP value at the source server.

A unique index on EVENT_NAME and EVENT_TIME is created automatically by either the Control Center or through DPCNTL.

Apply trail table

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

ASN.IBMSNAP_APPLYTRAIL

The Apply trail table contains audit trail information for the Apply program. This table records a history of updates performed against subscriptions. It is a repository of diagnostic and performance statistics. These facts make the apply trail table one of the best places to look if a problem occurs with the Apply program. Because this table is not automatically pruned, it is up to you to do so.

Table 65 provides a brief description of the Apply trail table columns.

Table 65. Apply Trail Table Columns

Column name Description
APPLY_QUAL Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction at the target site during the Apply program processing cycle.
WHOS_ON_FIRST The following values are used to control the order of processing in update-anywhere replication scenarios.

F
(first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.

ASNLOAD Contains one of the following values:

Y
Indicates that the Apply program was started and that the LOADXit parameter and the ASNLOAD exit routine were called to perform a full refresh on a subscription set.
N
Indicates that the ASNLOAD exit routine was not called because either a full refresh was not needed or the Apply program was not started with the LOADXit parameter.
NULL
Indicates that an Apply program error occurred before the Apply program could determine whether the ASNLOAD exit routine should be called.

MASS_DELETE A mass delete is always triggered during a full refresh. The following are values for this column:

Y
Indicates that a full refresh was done for a subscription set.
N
Indicates that a full refresh was not done for a subscription set.
NULL
Indicates that an error occurred before the Apply program could determine whether or not a full refresh was needed.

EFFECTIVE_MEMBERS The number of subscription-set members that are changed during an Apply cycle, either by a full refresh or by the replication of inserts, updates, and deletes. This number ranges between zero and the number of defined subscription-set members.
SET_INSERTED The total number of rows inserted into subscription-set members during the subscription cycle.
SET_DELETED The total number of rows deleted from subscription-set members during the subscription cycle.
SET_UPDATED The total number of rows updated in subscription-set members during the subscription cycle.
SET_REWORKED The Apply program will rework changes under the following conditions:

If an insert fails because the row already exists in the target table, the Apply program converts the insert to an update of the existing row.
If the update fails because the row does not exist in the target table, the Apply program converts the update to an insert.

SET_REJECTED_TRXS The total number of transactions rejected due to an update-anywhere conflict. This column is used only for update-anywhere subscription sets where conflict detection has been defined as standard or advanced.
STATUS A value that represents in-progress and completed work status for the Apply program.

-1
A failed execution.
0
A stable definition row.
1
A pending or in-progress execution. Do not modify this definition or any rows related to this subscription set in other control tables.
2
A continuing execution of a single logical subscription that was divided according to the MAX_SYNCH_MINUTES control column and is being serviced by multiple subscription cycles. Do not modify this row or any row related to this subscription set in other control tables.

LASTRUN The estimated time that the last subscription began. The Apply program sets the LASTRUN value each time a subscription set is processed. It is the approximate time at the control server that the Apply program begins processing the subscription set.
LASTSUCCESS The control server timestamp for the beginning of the last successful processing of a subscription set.
SYNCHPOINT The Apply program uses the SYNCHPOINT value from the global row of the register table at the source server if GLOBAL_RECORD = 'Y'. If data blocking is specified in the subscription definition, then the SYNCHPOINT value is the log or journal record sequence number of the last change applied during the Apply process.
SYNCHTIME 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 has captured all outstanding changes for a replication source table.
SOURCE_SERVER The RDB name of DB2 for OS/390, DB2 for VSE, and DB2 for VM where the source tables and views are defined.
SOURCE_ALIAS The DB2 Universal Database alias corresponding to the source server named in the SOURCE_SERVER column.
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 source. This column is used to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.
TARGET_SERVER The RDB name of the target server where the target tables and views are defined.
TARGET_ALIAS The DB2 Universal Database alias corresponding to the target server named in the TARGET_SERVER column.
TARGET_OWNER A qualifier for a target table or view.
TARGET_TABLE The target to which data is being applied.
SQLSTATE The SQL state code for a failed execution. Otherwise, NULL.
SQLCODE The SQL error code for a failed execution. Otherwise, NULL.
SQLERRP The database product identifier of the server where an SQL error occurred that caused a failed execution. Otherwise, NULL.
SQLERRM The SQL error information for a failed execution. Otherwise, NULL.
APPERRM The Apply error message ID and text for a failed execution. Refer to Capture and Apply messages for detailed message information. Otherwise, NULL.

Column name	Description
APPLY_QUAL	Uniquely identifies a group of subscription sets that are processed by the same Apply program process. This user-specified value must be unique for the control server where the subscription set table is located. For update-anywhere, this value must be unique at the control server and at the source server. This value is case-sensitive. You must specify this value when you define a subscription set.
SET_NAME	Identifies a group of target tables (subscription-set members) that are processed by the Apply program as a group. This user-specified value must be unique within an Apply qualifier. Changes for subscription members in a set are processed in a single transaction at the target site during the Apply program processing cycle.
WHOS_ON_FIRST	The following values are used to control the order of processing in update-anywhere replication scenarios. F (first) The target table is the user table or parent replica. The source table is the dependent row-replica that is lower in the hierarchy of replication. F is not used for read-only subscriptions. S (second) The source table is the user table, parent replica, or other source. The target table is the dependent row-replica or other copy that is lower in the hierarchy of replication. S is used for all read-only subscriptions.
ASNLOAD	Contains one of the following values: Y Indicates that the Apply program was started and that the LOADXit parameter and the ASNLOAD exit routine were called to perform a full refresh on a subscription set. N Indicates that the ASNLOAD exit routine was not called because either a full refresh was not needed or the Apply program was not started with the LOADXit parameter. NULL Indicates that an Apply program error occurred before the Apply program could determine whether the ASNLOAD exit routine should be called.
MASS_DELETE	A mass delete is always triggered during a full refresh. The following are values for this column: Y Indicates that a full refresh was done for a subscription set. N Indicates that a full refresh was not done for a subscription set. NULL Indicates that an error occurred before the Apply program could determine whether or not a full refresh was needed.
EFFECTIVE_MEMBERS	The number of subscription-set members that are changed during an Apply cycle, either by a full refresh or by the replication of inserts, updates, and deletes. This number ranges between zero and the number of defined subscription-set members.
SET_INSERTED	The total number of rows inserted into subscription-set members during the subscription cycle.
SET_DELETED	The total number of rows deleted from subscription-set members during the subscription cycle.
SET_UPDATED	The total number of rows updated in subscription-set members during the subscription cycle.
SET_REWORKED	The Apply program will rework changes under the following conditions: If an insert fails because the row already exists in the target table, the Apply program converts the insert to an update of the existing row. If the update fails because the row does not exist in the target table, the Apply program converts the update to an insert.
SET_REJECTED_TRXS	The total number of transactions rejected due to an update-anywhere conflict. This column is used only for update-anywhere subscription sets where conflict detection has been defined as standard or advanced.
STATUS	A value that represents in-progress and completed work status for the Apply program. -1 A failed execution. 0 A stable definition row. 1 A pending or in-progress execution. Do not modify this definition or any rows related to this subscription set in other control tables. 2 A continuing execution of a single logical subscription that was divided according to the MAX_SYNCH_MINUTES control column and is being serviced by multiple subscription cycles. Do not modify this row or any row related to this subscription set in other control tables.
LASTRUN	The estimated time that the last subscription began. The Apply program sets the LASTRUN value each time a subscription set is processed. It is the approximate time at the control server that the Apply program begins processing the subscription set.
LASTSUCCESS	The control server timestamp for the beginning of the last successful processing of a subscription set.
SYNCHPOINT	The Apply program uses the SYNCHPOINT value from the global row of the register table at the source server if GLOBAL_RECORD = 'Y'. If data blocking is specified in the subscription definition, then the SYNCHPOINT value is the log or journal record sequence number of the last change applied during the Apply process.
SYNCHTIME	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 has captured all outstanding changes for a replication source table.
SOURCE_SERVER	The RDB name of DB2 for OS/390, DB2 for VSE, and DB2 for VM where the source tables and views are defined.
SOURCE_ALIAS	The DB2 Universal Database alias corresponding to the source server named in the SOURCE_SERVER column.
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 source. This column is used to support multiple subscriptions for different source views with identical SOURCE_OWNER and SOURCE_TABLE column values.
TARGET_SERVER	The RDB name of the target server where the target tables and views are defined.
TARGET_ALIAS	The DB2 Universal Database alias corresponding to the target server named in the TARGET_SERVER column.
TARGET_OWNER	A qualifier for a target table or view.
TARGET_TABLE	The target to which data is being applied.
SQLSTATE	The SQL state code for a failed execution. Otherwise, NULL.
SQLCODE	The SQL error code for a failed execution. Otherwise, NULL.
SQLERRP	The database product identifier of the server where an SQL error occurred that caused a failed execution. Otherwise, NULL.
SQLERRM	The SQL error information for a failed execution. Otherwise, NULL.
APPERRM	The Apply error message ID and text for a failed execution. Refer to Capture and Apply messages for detailed message information. Otherwise, NULL.

Apply job 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.

IBMSNAP_APPLY_JOB

This AS/400-specific table is used to guarantee a unique APPLY_QUAL value for all instances of the Apply program running at the control server. A row will be added to this table every time an instance of the Apply program is started. If you start a new instance of the Apply program with an APPLY_QUAL value that already exists, your start command will fail.

Table 66 provides a brief description of the Apply job table columns.

Table 66. Apply Job 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 instance of the Apply program is started with an APPLY_QUAL. This value is used during update-anywhere replication to prevent circular replication of the changes made by the Apply program. See the subscription set table on page Subscription set table for more details.
CONTROL_SERVER Name of the RDB where the control tables and view are defined.
USER_NAME Name of the user who started a new instance of the Apply program
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_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.

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 instance of the Apply program is started with an APPLY_QUAL. This value is used during update-anywhere replication to prevent circular replication of the changes made by the Apply program. See the subscription set table on page Subscription set table for more details.
CONTROL_SERVER	Name of the RDB where the control tables and view are defined.
USER_NAME	Name of the user who started a new instance of the Apply program
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_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.

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

[ DB2 List of Books | Search the DB2 Books ]