DB2 Replication Guide and Reference

Control Tables Used at the Control Server

The following section provides a brief description of the control tables used at the control server, the columns in each control table, and the create table and create index statements for each table. These tables are automatically created when you define subscriptions. The structure of all control tables is subject to change.

Apply Trail Table

ASN.IBMSNAP_APPLYTRAIL

The Apply trail table records a history of updates performed against subscriptions. The subscription statistics can be used to audit update activity. This table is a repository of diagnostics and performance statistics. Because this table is not automatically pruned, it is up to you to do so.

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

Table 45. Apply Trail Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription. 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_NAME Names a subscription set. This value is unique within an Apply qualifier.
WHOS_ON_FIRST Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is:

F
(first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.

ASNLOAD One of the following values:

Y or N
Indicates whether ASNLOAD was called during the subscription set process.
NULL
Indicates that an error occured before the decision to call ASNLOAD was made.

MASS_DELETE One of the following values:

Y or N
Indicates whether MASS_DELETE was triggered during the full refresh of a subscription set.
NULL
Indicates that an error occurred before the decision to issue a mass delete was made.

EFFECTIVE_MEMBERS The number of members associated with calls to ASNLOAD, or the number of members for which rows are fetched and inserted, updated, or deleted.
SET_INSERTED The total number of rows individually inserted into any set members during the subscription cycle.
SET_DELETED The total number of rows individually deleted from any set members during the subscription cycle.
SET_UPDATED The total number of rows individually updated in any set members during the subscription cycle.
SET_REWORKED The total number of inserts reworked as updates and updates reworked as inserts for any set members during the subscription cycle.
SET_REJECTED_TRXS The total number of rejected replica transactions due to a direct or cascading update conflict. Always zero if either source or target is a replica.
STATUS A value that represents in-progress and completed work status for the Apply program.

-1
A known failed execution. You can modify the definition.
0
A stable definition row that can be modified. This value is the default.
1
A pending or in-progress execution. Do not modify this definition or any rows related to this subscription 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 in other control tables.
is refreshed.
LASTRUN The estimated time that the last subscription definition began. This value is calculated by adding the LASTRUN value with the INTERVAL_MINUTES value.
LASTSUCCESS The control server wall clock time of the beginning of a successful subscription cycle, recorded at the end of the cycle if STATUS=0 or STATUS=2. If not, existing value is unchanged.
SYNCHPOINT The SYNCHPOINT value equals the SYNCHPOINT field value in the register 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.
SYNCHTIME A source server timestamp that can be added to any captured log records for any source table. This timestamp indicates that a 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. These values are eventually appended into each change data table row.
SOURCE_SERVER The RDB name of DB2 for MVS, DB2 for VSE, and DB2 for VM where the source tables and views are defined.
SOURCE_ALIAS The name of the source server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the source server used by the Apply program for the DB2 Universal Database and can be null if the database has no DB2 Universal Database name.
SOURCE_OWNER The middle qualifier of the source table, which names the owner of the source table whose updates are being captured.
SOURCE_TABLE The third qualifier of the source table, which names the source table whose updates are being captured.
SOURCE_VIEW_QUAL Supports join subscriptions by matching the similar column in the register table.
TARGET_SERVER The RDB name of the MVS, VSE, or VM server where the target table and views are defined.
TARGET_ALIAS The name of the target server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the target server used by the Apply program for the DB2 Universal Database.
TARGET_OWNER The middle qualifier of the target name. Use the value in the SOURCE_OWNER column as the default.
TARGET_TABLE The third qualifier of the target table name. Use the value in the SOURCE_TABLE column as the default.
SQLSTATE If an error, the SQL error code. Otherwise, NULL.
SQLCODE If an error, the database-specific SQL error code. Otherwise, NULL.
SQLERRP If an error, the database product identifier. Otherwise, NULL.
SQLERRM If an error, the string containing the SQL error. Otherwise, NULL.
APPERRM The Apply error message text from SQLCA if there was an error, otherwise NULL. This value is constant for each copy derived, directly or indirectly, from the original source table.

Column name	Description
APPLY_QUAL	Identifies the Apply program for the platform instance that will run this subscription. 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_NAME	Names a subscription set. This value is unique within an Apply qualifier.
WHOS_ON_FIRST	Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is: F (first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions. S (second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.
ASNLOAD	One of the following values: Y or N Indicates whether ASNLOAD was called during the subscription set process. NULL Indicates that an error occured before the decision to call ASNLOAD was made.
MASS_DELETE	One of the following values: Y or N Indicates whether MASS_DELETE was triggered during the full refresh of a subscription set. NULL Indicates that an error occurred before the decision to issue a mass delete was made.
EFFECTIVE_MEMBERS	The number of members associated with calls to ASNLOAD, or the number of members for which rows are fetched and inserted, updated, or deleted.
SET_INSERTED	The total number of rows individually inserted into any set members during the subscription cycle.
SET_DELETED	The total number of rows individually deleted from any set members during the subscription cycle.
SET_UPDATED	The total number of rows individually updated in any set members during the subscription cycle.
SET_REWORKED	The total number of inserts reworked as updates and updates reworked as inserts for any set members during the subscription cycle.
SET_REJECTED_TRXS	The total number of rejected replica transactions due to a direct or cascading update conflict. Always zero if either source or target is a replica.
STATUS	A value that represents in-progress and completed work status for the Apply program. -1 A known failed execution. You can modify the definition. 0 A stable definition row that can be modified. This value is the default. 1 A pending or in-progress execution. Do not modify this definition or any rows related to this subscription 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 in other control tables. is refreshed.
LASTRUN	The estimated time that the last subscription definition began. This value is calculated by adding the LASTRUN value with the INTERVAL_MINUTES value.
LASTSUCCESS	The control server wall clock time of the beginning of a successful subscription cycle, recorded at the end of the cycle if STATUS=0 or STATUS=2. If not, existing value is unchanged.
SYNCHPOINT	The SYNCHPOINT value equals the SYNCHPOINT field value in the register 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.
SYNCHTIME	A source server timestamp that can be added to any captured log records for any source table. This timestamp indicates that a 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. These values are eventually appended into each change data table row.
SOURCE_SERVER	The RDB name of DB2 for MVS, DB2 for VSE, and DB2 for VM where the source tables and views are defined.
SOURCE_ALIAS	The name of the source server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the source server used by the Apply program for the DB2 Universal Database and can be null if the database has no DB2 Universal Database name.
SOURCE_OWNER	The middle qualifier of the source table, which names the owner of the source table whose updates are being captured.
SOURCE_TABLE	The third qualifier of the source table, which names the source table whose updates are being captured.
SOURCE_VIEW_QUAL	Supports join subscriptions by matching the similar column in the register table.
TARGET_SERVER	The RDB name of the MVS, VSE, or VM server where the target table and views are defined.
TARGET_ALIAS	The name of the target server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the target server used by the Apply program for the DB2 Universal Database.
TARGET_OWNER	The middle qualifier of the target name. Use the value in the SOURCE_OWNER column as the default.
TARGET_TABLE	The third qualifier of the target table name. Use the value in the SOURCE_TABLE column as the default.
SQLSTATE	If an error, the SQL error code. Otherwise, NULL.
SQLCODE	If an error, the database-specific SQL error code. Otherwise, NULL.
SQLERRP	If an error, the database product identifier. Otherwise, NULL.
SQLERRM	If an error, the string containing the SQL error. Otherwise, NULL.
APPERRM	The Apply error message text from SQLCA if there was an error, otherwise NULL. This value is constant for each copy derived, directly or indirectly, from the original source table.

Figure 51 shows the create table statement for the Apply trail table.

Figure 51. Create Table Statement for the Apply Trail Table

For All Platforms

CREATE TABLE ASN.IBMSNAP_APPLYTRAIL ( APPLY_QUAL CHAR(18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, ASNLOAD CHAR(1), MASS_DELETE CHAR(1), EFFECTIVE_MEMBERS INT, SET_INSERTED INT NOT NULL, SET_DELETED INT NOT NULL, SET_UPDATED INT NOT NULL, SET_REWORKED INT NOT NULL, SET_REJECTED_TRXS INT NOT NULL, STATUS SMALLINT NOT NULL, LASTRUN TIMESTAMP NOT NULL, LASTSUCCESS TIMESTAMP, SYNCHPOINT CHAR(10) FOR BIT DATA, SYNCHTIME TIMESTAMP, SOURCE_SERVER CHAR(18) NOT NULL, SOURCE_ALIAS CHAR(8), SOURCE_OWNER CHAR (18), SOURCE_TABLE CHAR(18), SOURCE_VIEW_QUAL SMALLINT, TARGET_SERVER CHAR(18) NOT NULL, TARGET_ALIAS CHAR(8), TARGET_OWNER CHAR (18) NOT NULL, TARGET_TABLE CHAR(18) NOT NULL, SQLSTATE CHAR(5), SQLCODE INT, SQLERRP CHAR(8), SQLERRM VARCHAR(70), APPERRM VARCHAR(760) );

Subscription Columns Table

This section contains programming interface information.

ASN.IBMSNAP_SUBS_COLS

This table contains information on the subscription columns being copied in a replication subscription. The subscription columns table contains supplemental information to the subscription targets member table. For example, it contains the replication subscription column name, target owner column, and target tables where the target members exists; whether the column is part of the primary key or not; and the SQL expression necessary to generate an aggregate column.

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

Table 46. Subscription Columns Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription. 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_NAME Names a subscription set. This value is unique within an Apply qualifier.
WHOS_ON_FIRST Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is:

F
(first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.

TARGET_OWNER The middle qualifier of the target name. Use the value in the SOURCE_OWNER column as the default.
TARGET_TABLE The third qualifier of the target table name. Use the value in the SOURCE_TABLE column as the default.
COL_TYPE
A flag indicating:

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

TARGET_NAME The name of the target table column. It does not need to match the source column name.
CCD column names cannot be renamed columns. 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 identifier or source column expression.

Column name	Description
APPLY_QUAL	Identifies the Apply program for the platform instance that will run this subscription. 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_NAME	Names a subscription set. This value is unique within an Apply qualifier.
WHOS_ON_FIRST	Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is: F (first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions. S (second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.
TARGET_OWNER	The middle qualifier of the target name. Use the value in the SOURCE_OWNER column as the default.
TARGET_TABLE	The third qualifier of the target table name. Use the value in the SOURCE_TABLE column as the default.
COL_TYPE	A flag indicating: A For after-image column. B For before-image column. C For computed column without SQL column function reference. F For computed column with SQL column function reference. R Signifies a relative record number column, provided by the system and used as a primary key column. Used by only DPROPR for OS/400.
TARGET_NAME	The name of the target table column. It does not need to match the source column name. CCD column names cannot be renamed columns. 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 identifier or source column expression.

Figure 52 and Figure 53 show the create table and create index statements for the subscription columns table.

Figure 52. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher Subscription Columns Tables

For DB2 for OS/390 Version 4 or Higher

CREATE TABLE ASN.IBMSNAP_SUBS_COLS ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, TARGET_OWNER CHAR(18) NOT NULL, TARGET_TABLE CHAR(18) NOT NULL, COL_TYPE CHAR(1) NOT NULL, TARGET_NAME CHAR(18) NOT NULL, IS_KEY CHAR(1) NOT NULL, COLNO SMALLINT NOT NULL, EXPRESSION VARCHAR(254) NOT NULL); CREATE TYPE 2 UNIQUE INDEX ASN.IBMSNAP_SUBS_COLSX ON ASN.IBMSNAP_SUBS_COLS (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC, TARGET_OWNER ASC, TARGET_TABLE ASC, TARGET_NAME ASC);

Figure 53. Create Table and Create Index Statements for Subscription Columns Tables on All Other Platforms

For All Other Platforms

CREATE TABLE ASN.IBMSNAP_SUBS_COLS ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, TARGET_OWNER CHAR(18) NOT NULL, TARGET_TABLE CHAR(18) NOT NULL, COL_TYPE CHAR(1) NOT NULL, TARGET_NAME CHAR(18) NOT NULL, IS_KEY CHAR(1) NOT NULL, COLNO SMALLINT NOT NULL, EXPRESSION VARCHAR(254) NOT NULL); CREATE UNIQUE INDEX ASN.IBMSNAP_SUBS_COLSX ON ASN.IBMSNAP_SUBS_COLS (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC, TARGET_OWNER ASC, TARGET_TABLE ASC, TARGET_NAME ASC);

Subscription Events Table

This section contains programming interface information.

ASN.IBMSNAP_SUBS_EVENT

This table contains information on the events being copied in a replication subscription. The common subscription events table contains event names and timestamps associated with the event names.

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

Table 47. Subscription Events Table Columns

Column name Description
EVENT_NAME A globally unique character string in a global name space configuration or 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. An upper-bound function that blocks replication 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	A globally unique character string in a global name space configuration or 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. An upper-bound function that blocks replication 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.

Figure 54 and Figure 55 show the create table and create index statements for the subscription events table columns.

Figure 54. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher Subscription Events Tables

For DB2 for OS/390 Version 4 or Higher

CREATE TABLE ASN.IBMSNAP_SUBS_EVENT ( EVENT_NAME CHAR(18) NOT NULL, EVENT_TIME TIMESTAMP NOT NULL, END_OF_PERIOD TIMESTAMP ); CREATE TYPE 2 UNIQUE INDEX ASN.IBMSNAP_SUBS_EVENX ON ASN.IBMSNAP_SUBS_EVENT (EVENT_NAME ASC, EVENT_TIME ASC);

Figure 55. Create Table and Create Index Statements for Subscription Events Tables on All Other Platforms

For All Other Platforms

CREATE TABLE ASN.IBMSNAP_SUBS_EVENT ( EVENT_NAME CHAR(18) NOT NULL, EVENT_TIME TIMESTAMP NOT NULL, END_OF_PERIOD TIMESTAMP ); CREATE UNIQUE INDEX ASN.IBMSNAP_SUBS_EVENX ON ASN.IBMSNAP_SUBS_EVENT (EVENT_NAME ASC, EVENT_TIME ASC);

Subscription Set Table

This section contains programming interface information.

ASN.IBMSNAP_SUBS_SET

The subscription set table lists all the sets defined at the Control Center and ensures that each set name is used only once for every Apply qualifier.

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

Table 48. Subscription Set Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription. 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_NAME Names a subscription set. This value must be unique within an Apply qualifier.
WHOS_ON_FIRST Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is:

F
(first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.

ACTIVATE Indicates:

0
The subscription set is deactivated.
1
The request is active indefinitely.
2
The set can be copied immediately.

SOURCE_SERVER The RDB name of the source server where the source tables and views are defined.
SOURCE_ALIAS The name of the source server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the source server used by the Apply program for the DB2 Universal Database and can be NULL if the database has no DB2 Universal Database name.
TARGET_SERVER The RDB name of the server where the target table and views are defined.
TARGET_ALIAS The name of the target server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the target server used by the Apply program for the DB2 Universal Database.
STATUS A value that represents in-progress and completed work status for the Apply program.

-1
A known failed execution. You can modify this definition.
0
A stable definition row that can be modified. This value is the default.
1
A pending or in-progress execution. Do not modify this definition or any rows related to this subscription 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 in other control tables.
control server until the copy is refreshed.
LASTRUN The estimated time that the last subscription definition began. This value is calculated by adding the LASTRUN value with the INTERVAL_MINUTES value.
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 a subscription that has both relative and event timing specifications.

SLEEP_MINUTES Specifies the time of inactivity between statement executions.
EVENT_NAME The optional foreign key into the subscription event table located at the control server.
LASTSUCCESS The control server wall clock time of the beginning of a successful subscription cycle, recorded at the end of the cycle if STATUS=0 or STATUS=2. If not, the existing value is unchanged.
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.
SYNCHTIME A source server timestamp that can be added to any captured log records for any source table. This timestamp indicates that a 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. These values are eventually appended into each change data table row.
MAX_SYNCH_MINUTES A time-threshold limit to regulate the amount of change data to fetch and apply during a subscription cycle. The limit is automatically recalculated if the Apply program encounters a resource constraint making 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 A 0 value represents the absence of any subscription statements table rows for the subscription. Otherwise, this value represents the total number of subscription statement table rows.
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	Identifies the Apply program for the platform instance that will run this subscription. 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_NAME	Names a subscription set. This value must be unique within an Apply qualifier.
WHOS_ON_FIRST	Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is: F (first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions. S (second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.
ACTIVATE	Indicates: 0 The subscription set is deactivated. 1 The request is active indefinitely. 2 The set can be copied immediately.
SOURCE_SERVER	The RDB name of the source server where the source tables and views are defined.
SOURCE_ALIAS	The name of the source server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the source server used by the Apply program for the DB2 Universal Database and can be NULL if the database has no DB2 Universal Database name.
TARGET_SERVER	The RDB name of the server where the target table and views are defined.
TARGET_ALIAS	The name of the target server used by the Apply program on the DB2 Universal Database client interface. This alias identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This alias does not necessarily match the alias of the target server used by the Apply program for the DB2 Universal Database.
STATUS	A value that represents in-progress and completed work status for the Apply program. -1 A known failed execution. You can modify this definition. 0 A stable definition row that can be modified. This value is the default. 1 A pending or in-progress execution. Do not modify this definition or any rows related to this subscription 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 in other control tables. control server until the copy is refreshed.
LASTRUN	The estimated time that the last subscription definition began. This value is calculated by adding the LASTRUN value with the INTERVAL_MINUTES value.
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 a subscription that has both relative and event timing specifications.
SLEEP_MINUTES	Specifies the time of inactivity between statement executions.
EVENT_NAME	The optional foreign key into the subscription event table located at the control server.
LASTSUCCESS	The control server wall clock time of the beginning of a successful subscription cycle, recorded at the end of the cycle if STATUS=0 or STATUS=2. If not, the existing value is unchanged.
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.
SYNCHTIME	A source server timestamp that can be added to any captured log records for any source table. This timestamp indicates that a 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. These values are eventually appended into each change data table row.
MAX_SYNCH_MINUTES	A time-threshold limit to regulate the amount of change data to fetch and apply during a subscription cycle. The limit is automatically recalculated if the Apply program encounters a resource constraint making 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	A 0 value represents the absence of any subscription statements table rows for the subscription. Otherwise, this value represents the total number of subscription statement table rows.
ARCH_LEVEL	The architectural level of the definition contained in the row. This field identifies the rules under which a row was created.

Figure 56 and Figure 57 show the create table and create index statements for subscription set tables.

Figure 56. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher Subscription Set Tables

For DB2 for OS/390 Version 4 or Higher

CREATE TABLE ASN.IBMSNAP_SUBS_SET ( APPLY_QUAL CHAR(18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, ACTIVATE SMALLINT NOT NULL, SOURCE_SERVER CHAR(18) NOT NULL, SOURCE_ALIAS CHAR(8), TARGET_SERVER CHAR(18) NOT NULL, TARGET_ALIAS CHAR(8), STATUS SMALLINT NOT NULL, LASTRUN TIMESTAMP NOT NULL, REFRESH_TIMING CHAR(1) NOT NULL, SLEEP_MINUTES INT, EVENT_NAME CHAR(18), LASTSUCCESS TIMESTAMP, SYNCHPOINT CHAR(10) FOR BIT DATA, SYNCHTIME TIMESTAMP, MAX_SYNCH_MINUTES INT, AUX_STMTS SMALLINT NOT NULL, ARCH_LEVEL CHAR(4) NOT NULL, CREATE TYPE 2 UNIQUE INDEX ASN.IBMSNAP_SUBS_SETX ON ASN.IBMSNAP_SUBS_SET (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC);

Figure 57. Create Table and Create Index Statements for Subscription Set Tables on All Other Platforms

For All Other Platforms

CREATE TABLE ASN.IBMSNAP_SUBS_SET ( APPLY_QUAL CHAR(18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, ACTIVATE SMALLINT NOT NULL, SOURCE_SERVER CHAR(18) NOT NULL, SOURCE_ALIAS CHAR(8), TARGET_SERVER CHAR(18) NOT NULL, TARGET_ALIAS CHAR(8), STATUS SMALLINT NOT NULL, LASTRUN TIMESTAMP NOT NULL, REFRESH_TIMING CHAR(1) NOT NULL, SLEEP_MINUTES INT, EVENT_NAME CHAR(18), LASTSUCCESS TIMESTAMP, SYNCHPOINT CHAR(10) FOR BIT DATA, SYNCHTIME TIMESTAMP, MAX_SYNCH_MINUTES INT, AUX_STMTS SMALLINT NOT NULL, ARCH_LEVEL CHAR(4) NOT NULL, <additional padding columns for DB2 for OS/390 version 3>); CREATE UNIQUE INDEX ASN.IBMSNAP_SUBS_SETX ON ASN.IBMSNAP_SUBS_SET (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC);

Subscription Statements Table

This section contains programming interface information.

ASN.IBMSNAP_SUBS_STMTS

This table contains information on the statements being executed in a replication subscription set. Execute immediately (EI) statements or stored procedures can be executed at the source or target server only. The subscription statements table contains zero rows if you did not specify any statements or stored procedures.

A unique index on the APPLY_QUAL, SET_NAME, WHOS_ON_FIRST, BEFORE_OR_AFTER, and STMT_NUMBER columns is created automatically by the Control Center.

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

Table 49. Subscription Statements Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription. 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_NAME Names a subscription set. This value must be unique within an Apply qualifier.
WHOS_ON_FIRST Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is:

F
(first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for 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 the opening of the answer set cursors.

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 at the target server.
C
The SQL statement contains a stored procedure name to run as an EXEC SQL CALL at the target server.

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 values are acceptable SQLSTATES. The values would otherwise cause the subscription to stop executing.

Column name	Description
APPLY_QUAL	Identifies the Apply program for the platform instance that will run this subscription. 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_NAME	Names a subscription set. This value must be unique within an Apply qualifier.
WHOS_ON_FIRST	Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is: F (first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions. S (second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for 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 the opening of the answer set cursors.
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 at the target server. C The SQL statement contains a stored procedure name to run as an EXEC SQL CALL at the target server.
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 values are acceptable SQLSTATES. The values would otherwise cause the subscription to stop executing.

Figure 58 and Figure 59 show the create table and create index statements for the subscription statements table.

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

For DB2 for OS/390 Version 4 or Higher

CREATE TABLE ASN.IBMSNAP_SUBS_STMTS ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, BEFORE_OR_AFTER CHAR(1) NOT NULL, STMT_NUMBER SMALLINT NOT NULL, EI_OR_CALL CHAR(1) NOT NULL, SQL_STMT VARCHAR(1024), ACCEPT_SQLSTATES VARCHAR (50)); CREATE TYPE 2 UNIQUE INDEX ASN.IBMSNAP_SUBS_STMTX ON ASN.IBMSNAP_SUBS_STMTS (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC, BEFORE_OR_AFTER ASC, STMT_NUMBER ASC);

Figure 59. Create Table and Create Index Statements for Subscription Statements Tables on All Other Platforms

For All Other Platforms

CREATE TABLE ASN.IBMSNAP_SUBS_STMTS ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, BEFORE_OR_AFTER CHAR(1) NOT NULL, STMT_NUMBER SMALLINT NOT NULL, EI_OR_CALL CHAR(1) NOT NULL, SQL_STMT VARCHAR(1024), ACCEPT_SQLSTATES VARCHAR (50)); CREATE UNIQUE INDEX ASN.IBMSNAP_SUBS_STMTX ON ASN.IBMSNAP_SUBS_STMTS (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC, BEFORE_OR_AFTER ASC, STMT_NUMBER ASC);

Subscription Targets Member Table

This section contains programming interface information.

ASN.IBMSNAP_SUBS_MEMBR

This table contains information about the individual member and target tables defined for a subscription set.

A unique index on APPLY_QUAL, SET_NAME, WHOS_ON_FIRST, SOURCE_OWNER, SOURCE_TABLE, SOURCE_VIEW_QUAL, TARGET_OWNER, and TARGET_TABLE is created automatically by the Control Center.

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

Table 50. Subscription Targets Member Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription. 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_NAME Names a subscription set. This value must be unique within an Apply qualifier.
For replicas, both the to-replica and from-replica subscriptions share the same APPLY_QUAL and SET_NAME values determined by their WHOS_ON_FIRST values.
WHOS_ON_FIRST Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is:

F
(first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions.
S
(second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.

SOURCE_OWNER The middle qualifier of the source table, which names the owner of the source table whose updates are being captured.
SOURCE_TABLE The third qualifier of the source table, which names the source table whose updates are being captured.
SOURCE_VIEW_QUAL Supports the join of subscriptions by matching the similar column in the register table.
TARGET_OWNER The middle qualifier of the target name. Use the value in the SOURCE_OWNER column as the default.
TARGET_TABLE The third qualifier of the target table name. Use the value in the SOURCE_TABLE column as the default.
TARGET_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.

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

TARGET_STRUCTURE The structure of the target table:

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

PREDICATES Lists the predicates to be placed in a WHERE clause to subset the view (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 as the Apply program can generate an ORDER BY clause. The PREDICATES column can be updated directly with a predicate in situations where an out-of-range insert is performed by a local application.

Column name	Description
APPLY_QUAL	Identifies the Apply program for the platform instance that will run this subscription. 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_NAME	Names a subscription set. This value must be unique within an Apply qualifier. For replicas, both the to-replica and from-replica subscriptions share the same APPLY_QUAL and SET_NAME values determined by their WHOS_ON_FIRST values.
WHOS_ON_FIRST	Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing. When the value of this column is: F (first) The target table is the user table or parent replica and the source table is the dependent replica-propagation is UP the hierarchy. 'F' is not used for read-only subscriptions. S (second) The source table is the user table, parent replica or other source and the target table is the dependent replica or other copy which is lower in the hierarchy-propagation is DOWN the hierarchy. 'S' is also used for read-only subscriptions.
SOURCE_OWNER	The middle qualifier of the source table, which names the owner of the source table whose updates are being captured.
SOURCE_TABLE	The third qualifier of the source table, which names the source table whose updates are being captured.
SOURCE_VIEW_QUAL	Supports the join of subscriptions by matching the similar column in the register table.
TARGET_OWNER	The middle qualifier of the target name. Use the value in the SOURCE_OWNER column as the default.
TARGET_TABLE	The third qualifier of the target table name. Use the value in the SOURCE_TABLE column as the default.
TARGET_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.
TARGET_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.
TARGET_STRUCTURE	The structure of the target table: 1 Source table 3 CCD table 4 Point-in-time table 5 Base aggregate table 6 Change aggregate table 7 Replica 8 User copy
PREDICATES	Lists the predicates to be placed in a WHERE clause to subset the view (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 as the Apply program can generate an ORDER BY clause. The PREDICATES column can be updated directly with a predicate in situations where an out-of-range insert is performed by a local application.

Figure 60 and Figure 61 show the create table and create index statements for the subscription targets member table.

Figure 60. Create Table and Create Index Statements for DB2 for OS/390 Version 4 or Higher Subscription Targets Member Tables

For DB2 for OS/390 Version 4 or Higher

CREATE TABLE ASN.IBMSNAP_SUBS_MEMBR ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, SOURCE_OWNER CHAR (18) NOT NULL, SOURCE_TABLE CHAR(18) NOT NULL, SOURCE_VIEW_QUAL SMALLINT NOT NULL, TARGET_OWNER CHAR (18) NOT NULL, TARGET_TABLE CHAR(18) NOT NULL, TARGET_CONDENSED CHAR(1) NOT NULL, TARGET_COMPLETE CHAR(1) NOT NULL, TARGET_STRUCTURE SMALLINT NOT NULL, PREDICATES VARCHAR (512) ); CREATE TYPE 2 UNIQUE INDEX ASN.IBMSNAP_SUBS_MEMIX ON ASN.IBMSNAP_SUBS_MEMBR (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC, SOURCE_OWNER ASC, SOURCE_TABLE ASC, SOURCE_VIEW_QUAL ASC, TARGET_OWNER ASC, TARGET_TABLE ASC);

Figure 61. Create Table and Create Index Statements for Subscription Targets Member Tables on All Other Platforms

For All Other Platforms

CREATE TABLE ASN.IBMSNAP_SUBS_MEMBR ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, SOURCE_OWNER CHAR (18) NOT NULL, SOURCE_TABLE CHAR(18) NOT NULL, SOURCE_VIEW_QUAL SMALLINT NOT NULL, TARGET_OWNER CHAR (18) NOT NULL, TARGET_TABLE CHAR(18) NOT NULL, TARGET_CONDENSED CHAR(1) NOT NULL, TARGET_COMPLETE CHAR(1) NOT NULL, TARGET_STRUCTURE SMALLINT NOT NULL, PREDICATES VARCHAR (512) ); CREATE UNIQUE INDEX ASN.IBMSNAP_SUBS_MEMIX ON ASN.IBMSNAP_SUBS_MEMBR (APPLY_QUAL ASC, SET_NAME ASC, WHOS_ON_FIRST ASC, SOURCE_OWNER ASC, SOURCE_TABLE ASC, SOURCE_VIEW_QUAL ASC, TARGET_OWNER ASC, TARGET_TABLE ASC);

To provide the necessary source/target member name uniqueness, you can introduce either source or target view names when there are multiple members with the same fully qualified source and target names.

In a cloned subscription set, the SOURCE_SERVER, SOURCE_OWNER, SOURCE_TABLE, SOURCE_VIEW_QUAL, TARGET_OWNER, TARGET_TABLE, and SET_NAME values are identical to the original subscription set. The cloned subscription is distinguished from the original by differing APPLY_QUAL values.

Subscription Schema Changes Table (Microsoft Jet Specific)

This section contains programming interface information.

ASN.IBMSNAP_SCHEMA_CHG

This table is used to signal that one or more of the following modifications has been made to a subscription:

A new member has been added (ADDMEMBR)
An existing member has been deleted (DELMEMBR)
An existing member had an additional column added (ALTERMEM)

This table allows DataPropagator for Microsoft Jet to quickly determine that some relevant schema change has occured since its last syncronization. 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 51 provides a brief description of the subscription schema changes table columns.

Table 51. Subscription Schema Changes Table

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription. 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_NAME Names a subscription set. This value must be unique within an Apply qualifier.
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	Identifies the Apply program for the platform instance that will run this subscription. 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_NAME	Names a subscription set. This value must be unique within an Apply qualifier.
LAST_CHANGED	This column is the timestamp of when this row was last changed in this table. This column is for informational purposes only.

Figure 62 shows the create table statement for the subscription schema changes table.

Figure 62. Create Table Subscription Schema Changes Table

For Microsoft Jet

CREATE TABLE ASN.IBMSNAP_SCHEMA_CHG ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, LAST_CHANGED TIMESTAMP NOT NULL);

Row-Replica Target List Table (Microsoft Jet Specific)

This section contains programming interface information.

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 member has been dropped since the last synchronization.

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

Table 52. Row-Replica Target List Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance that will run this subscription. 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_NAME Names a subscription set. This value must be unique within an Apply qualifier.
WHOS_ON_FIRST Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing.
TARGET_OWNER The middle qualifier of the target table name; default is the user ID of the user defining the subscription. Matches the column of the same name in the subscription targets member table.
TARGET_TABLE The third qualifier of the target table name. Matches the column of the same name in the subscription targets member table.
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	Identifies the Apply program for the platform instance that will run this subscription. 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_NAME	Names a subscription set. This value must be unique within an Apply qualifier.
WHOS_ON_FIRST	Allows both the up and down replication subscriptions to be contained in the same set without the potential for multiple SOURCE_SERVER values for each APPLY_QUAL, SET_NAME pairing.
TARGET_OWNER	The middle qualifier of the target table name; default is the user ID of the user defining the subscription. Matches the column of the same name in the subscription targets member table.
TARGET_TABLE	The third qualifier of the target table name. Matches the column of the same name in the subscription targets member table.
LAST_POSTED	This column is the timestamp of when this row was inserted into the table. This column is for informational purposes only.

Figure 63 shows the create table and create index statements for the row-replica target list table.

Figure 63. Create Table and Create Index Statements for Row-Replica Target List Tables

For Microsoft Jet

CREATE TABLE ASN.IBMSNAP_SUBS_TGTS ( APPLY_QUAL CHAR (18) NOT NULL, SET_NAME CHAR (18) NOT NULL, WHOS_ON_FIRST CHAR(1) NOT NULL, TARGET_OWNER CHAR(18) NOT NULL, TARGET_TABLE CHAR(18) NOT NULL, LAST_POSTED TIMESTAMP ); CREATE UNIQUE INDEX ASN.IBMSNAP_SUBS_TGTSX ON ASN.IBMSNAP_SUBS_TGTS (APPLY_QUAL, SET_NAME, WHOS_ON_FIRST, TARGET_OWNER, TARGET_TABLE);

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

[ DB2 List of Books | Search the DB2 Books ]