Replication Guide and Reference

Control Tables Used at the Control Server

This section, describes tables that are created automatically during the course of defining subscription activities.

Table 34. Control Tables Used at the Control Server

Table name Internal name Description
Apply trail table ASN.IBMSNAP_APPLYTRAIL This table records a history of updates performed against replication subscriptions. The subscription statistics can be used to audit update activity and is a repository of diagnostics and performance statistics.
Set subscription table ASN.IBMSNAP_SUBS_SET This table ensures that each set name is used only once for every Apply qualifier.
Subscription columns table ASN.IBMSNAP_SUBS_COLS This table contains information on the common subscription columns being copied in a replication subscription.
Subscription events table ASN.IBMSNAP_SUBS_EVENT This table contains information on the events being copied in a replication subscription.
Subscription statements table ASN.IBMSNAP_SUBS_STMTS This table contains information on the statements being copied in a replication subscription.
Subscription targets member table ASN.IBMSNAP_SUBS_MEMBR This table contains information on the targets member being copied in a replication subscription.

Table name	Internal name	Description
Apply trail table	ASN.IBMSNAP_APPLYTRAIL	This table records a history of updates performed against replication subscriptions. The subscription statistics can be used to audit update activity and is a repository of diagnostics and performance statistics.
Set subscription table	ASN.IBMSNAP_SUBS_SET	This table ensures that each set name is used only once for every Apply qualifier.
Subscription columns table	ASN.IBMSNAP_SUBS_COLS	This table contains information on the common subscription columns being copied in a replication subscription.
Subscription events table	ASN.IBMSNAP_SUBS_EVENT	This table contains information on the events being copied in a replication subscription.
Subscription statements table	ASN.IBMSNAP_SUBS_STMTS	This table contains information on the statements being copied in a replication subscription.
Subscription targets member table	ASN.IBMSNAP_SUBS_MEMBR	This table contains information on the targets member being copied in a replication subscription.

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

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 and is a repository of diagnostics and performance statistics.

Table 35. Apply Trail Table Columns

Column name Description
APPLY_QUAL Is from IBMSNAP_SUBS_SET.
SET_NAME Is from IBMSNAP_SUBS_SET.
WHOS_ON_FIRST Is from IBMSNAP_SUBS_SET.
ASNLOAD

Y or N
Indicates whether LOAD was called during the subscription set process.
Null
Indicates an error.

MASS_DELETE

Y or N
Indicates whether LOAD was called during the subscription set process.
Null
if error. If the error occured before the decision to issue a mass delete, then a mass delete will not be executed.

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 From IBMSNAP_SUBS_SET. A value that represents in-progress and completed work status for Apply.

-1
A known failed execution; the definition can be modified.
0
A stable definition row which can be modified.
1
A pending or in-progress execution; this definition should not be modified.
2
A continuing execution of a single logical subscription which was divided according to the MAX_SYNCH_MINUTES control column and is being serviced by multiple subscription cycles.

LASTRUN The time when this subcycle is supposed to start. This value is calculated by adding the LASTRUN value with the INTERVAL_MINUTES value.
LASTSUCCESS Is from IBMSNAP_SUB_SET.
SYNCHPOINT Is from IBMSNAP_SUB_SET.
SYNCHTIME Is from IBMSNAP_SUB_SET.
SOURCE_SERVER Is from IBMSNAP_SUBS_SET.
SOURCE_ALIAS Is from IBMSNAP_SUBS_SET.
SOURCE_OWNER Is from IBMSNAP_SUBS_SET.
SOURCE_TABLE Is from IBMSNAP_SUBS_SET.
SOURCE_VIEW_QUAL Is from IBMSNAP_SUBS_SET.
TARGET_SERVER Is from IBMSNAP_SUBS_SET.
TARGET_ALIAS Is from IBMSNAP_SUBS_SET.
TARGET_OWNER Is from IBMSNAP_SUBS_SET.
TARGET_TABLE Is from IBMSNAP_SUBS_SET.
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	Is from IBMSNAP_SUBS_SET.
SET_NAME	Is from IBMSNAP_SUBS_SET.
WHOS_ON_FIRST	Is from IBMSNAP_SUBS_SET.
ASNLOAD	Y or N Indicates whether LOAD was called during the subscription set process. Null Indicates an error.
MASS_DELETE	Y or N Indicates whether LOAD was called during the subscription set process. Null if error. If the error occured before the decision to issue a mass delete, then a mass delete will not be executed.
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	From IBMSNAP_SUBS_SET. A value that represents in-progress and completed work status for Apply. -1 A known failed execution; the definition can be modified. 0 A stable definition row which can be modified. 1 A pending or in-progress execution; this definition should not be modified. 2 A continuing execution of a single logical subscription which was divided according to the MAX_SYNCH_MINUTES control column and is being serviced by multiple subscription cycles.
LASTRUN	The time when this subcycle is supposed to start. This value is calculated by adding the LASTRUN value with the INTERVAL_MINUTES value.
LASTSUCCESS	Is from IBMSNAP_SUB_SET.
SYNCHPOINT	Is from IBMSNAP_SUB_SET.
SYNCHTIME	Is from IBMSNAP_SUB_SET.
SOURCE_SERVER	Is from IBMSNAP_SUBS_SET.
SOURCE_ALIAS	Is from IBMSNAP_SUBS_SET.
SOURCE_OWNER	Is from IBMSNAP_SUBS_SET.
SOURCE_TABLE	Is from IBMSNAP_SUBS_SET.
SOURCE_VIEW_QUAL	Is from IBMSNAP_SUBS_SET.
TARGET_SERVER	Is from IBMSNAP_SUBS_SET.
TARGET_ALIAS	Is from IBMSNAP_SUBS_SET.
TARGET_OWNER	Is from IBMSNAP_SUBS_SET.
TARGET_TABLE	Is from IBMSNAP_SUBS_SET.
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 43. CREATE TABLE Statement

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 INTEGER, SQLERRP CHAR(8), SQLERRM VARCHAR(70),

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

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

Subscription Columns Table

ASN.IBMSNAP_SUBS_COLS

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

A unique index on APPLY_QUAL, SET_NAME, TARGET_OWNER, TARGET_TABLE, and TARGET_NAME is created automatically by the Control Center.

Table 36. Subscription Columns Table Columns

Column name Description
APPLY_QUAL From IBMSNAP_SUBS_MEMBR.
SET_NAME From IBMSNAP_SUBS_MEMBR.
WHOS_ON_FIRST From IBMSNAP_SUBS_MEMBR.
TARGET_OWNER The middle qualifier of the target table name.
TARGET_TABLE The third qualifier of the target table name.
COL_TYPE

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

TARGET_NAME The name of the target table column that does not need to match the source column name.
Note: 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.

Figure 44. CREATE TABLE Statement

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 IBMSNAP_SUBS_COLSX ON ASN.IBMSNAP_SUBS_COLS (APPLY_QUAL, SET-NAME, WHOS_ON_FIRST TARGET_OWNER, TARGET_TABLE, TARGET_NAME);

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

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

Subscription Events Table

ASN.IBMSNAP_SUBS_EVENT

This table contains information on the events being copied in a replication subscription. The common subscription events table contains the replication subscription name, target owner, and target tables where the event exists, whether the event is part of the primary key or not, and the SQL expression necessary to generate an aggregate column.

A unique index on EVENT_NAME and EVENT_TIME is created automatically by the Control Center.

Table 37. 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.
Note: This is the only way to prevent eligible change data from replicating during a subscription cycle. The value in this column must be less than the CURRENT TIMESTAMP value at the source server.

Figure 45. CREATE TABLE Statement

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

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

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

Subscription Set Table

ASN.IBMSNAP_SUBS_SET

The subscription set table ensures that each set name is used only once for every Apply qualifier.

Table 38. Subscription Set Table Columns

Column name Description
APPLY_QUAL Identifies the Apply program for the platform instance which will run this subscription.
SET_NAME From APPLY_QUAL. Names the replication subscription.
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 per APPLY_QUAL, SET_NAME pairing.
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 DB2 for MVS, VSE, and 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 identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This does not necessarily match the alias of the source server used by the Apply program for the DB2 Universal Database and may be null if the database has no DB2 Universal Database name.
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 identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This does not necessarily match the alias of the target server used by the Apply program for the DB2 Universal Database.
STATUS Records in-progress and completed work status as the Apply program for the platform disconnects from the CNTL_SERVER where the control table is stored to access data at the SOURCE_SERVER and update the copy at the TARGET_SERVER. The completion state cannot be set at the CNTL_SERVER until the copy has been refreshed.
LASTRUN The estimated time the last subscription style began.
REFRESH_TIMING Sets the timing between statement executions.

R
Apply uses the value in SLEEP_MINUTES to determine replication timing.
E
Apply checks the time value in the SUBS_EVENT table to determine replication timing.
B
Indicates a subscription having both relative and event timing specifications.

SLEEP_MINUTES Specifies the time of inactivity between statement executions.
EVENT_NAME The optional foreign key into IBMSNAP_SUBS_EVENT in the control server.
LASTSUCCESS The control server wallclock time of the beginning of a susccessful subscription cycle, recorded at the end of the cycle if STATUS=1 or STATUS=2. If not, the existing value is unchanged.
SYNCHPOINT The sysnchpoint value of the upper-bound of requested transaction data during the previous subscription cycle. This value may advance even if no change data rows were fetched.
SYNCHTIME The timestamp associated with the SYNCHPOINT column of the current table.
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.
AUX_STMTS A 0 value representing the absence of one or more IBMSNAP_SUBS_STMTS rows for the subscription. The value represents the number of such rows.
ARCH_LEVEL The architectural level of the definition contained in the row. This field identifies the rules under which a row was created.
BEFORE_OR_AFTER Specifies:

A
The statement is executed at the TARGET_SERVER and should succeed the application of changes to the target tables.
B
The statement is executed at the TARGET_SERVER and should preceed the application of changes to the target tables.
S
The statement is executed at the SOURCE_SERVER and should preceed the opening of answer set cursors.

Column name	Description
APPLY_QUAL	Identifies the Apply program for the platform instance which will run this subscription.
SET_NAME	From APPLY_QUAL. Names the replication subscription.
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 per APPLY_QUAL, SET_NAME pairing.
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 DB2 for MVS, VSE, and 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 identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This does not necessarily match the alias of the source server used by the Apply program for the DB2 Universal Database and may be null if the database has no DB2 Universal Database name.
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 identifies the location of the subscription definition and is later used to perform administrative actions on a subscription. This does not necessarily match the alias of the target server used by the Apply program for the DB2 Universal Database.
STATUS	Records in-progress and completed work status as the Apply program for the platform disconnects from the CNTL_SERVER where the control table is stored to access data at the SOURCE_SERVER and update the copy at the TARGET_SERVER. The completion state cannot be set at the CNTL_SERVER until the copy has been refreshed.
LASTRUN	The estimated time the last subscription style began.
REFRESH_TIMING	Sets the timing between statement executions. R Apply uses the value in SLEEP_MINUTES to determine replication timing. E Apply checks the time value in the SUBS_EVENT table to determine replication timing. B Indicates a subscription having both relative and event timing specifications.
SLEEP_MINUTES	Specifies the time of inactivity between statement executions.
EVENT_NAME	The optional foreign key into IBMSNAP_SUBS_EVENT in the control server.
LASTSUCCESS	The control server wallclock time of the beginning of a susccessful subscription cycle, recorded at the end of the cycle if STATUS=1 or STATUS=2. If not, the existing value is unchanged.
SYNCHPOINT	The sysnchpoint value of the upper-bound of requested transaction data during the previous subscription cycle. This value may advance even if no change data rows were fetched.
SYNCHTIME	The timestamp associated with the SYNCHPOINT column of the current table.
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.
AUX_STMTS	A 0 value representing the absence of one or more IBMSNAP_SUBS_STMTS rows for the subscription. The value represents the number of such rows.
ARCH_LEVEL	The architectural level of the definition contained in the row. This field identifies the rules under which a row was created.
BEFORE_OR_AFTER	Specifies: A The statement is executed at the TARGET_SERVER and should succeed the application of changes to the target tables. B The statement is executed at the TARGET_SERVER and should preceed the application of changes to the target tables. S The statement is executed at the SOURCE_SERVER and should preceed the opening of answer set cursors.

Figure 46. CREATE TABLE Statement

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, < + padding where row level locking not supported>) This index ensures that each set name is used just once per Apply qualifier. Each Apply process can only have one control server, so this index suffices, given that the assumption that unique APPLY_QUAL values are used for each Apply process. The set could be cloned, but only for a different Apply process using a different APPLY_QUAL value. CREATE UNIQUE INDEX IBMSNAP_SUBS_SETX ON ASN.IBMSNAP_SUBS_SET (APPLY_QUAL, SET_NAME, WHOS_ON_FIRST);

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

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

Subscription Statements Table

ASN.IBMSNAP_SUBS_STMTS

This table contains information on the statements being copied in a replication subscription. The common subscription statements table contains the replication subscription name, target owner, and target tables where the statement exists, whether the statement is part of the primary key or not, and the SQL expression necessary to generate an aggregate column.

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 39. Subscription Statements Table Columns

Column name Description
APPLY_QUAL From IBMSNAP_SUBS_SET.
SET_NAME From IBMSNAP_SUBS_SET.
WHOS_ON_FIRST From IBMSNAP_SUBS_SET.
BEFORE_OR_AFTER Specifies either:

A
The statement is executed at the target server and should succeed the application of changes to the target tables.
B
The statement is executed at the target server and should succeed the application of changes to the target tables.
S
The statement is executed at the source server and should precede 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 Specifies either:

E
The SQL_STMT should be run as an EXEC SQL EXECUTE IMMEDIATE at the target server.
C
The SQL_STMT contains a stored procedure name to run as an EXEC SQL CALL at the target server.

SQL_STMT Specifies either:

E
The SQL string runs as an EXEC SQL EXECUTE IMMEDIATE statement.
C
The 8-byte name of an SQL-stored procedure, without parameters or the CALL keyword, runs as an EXEC SQL CALL statement.

ACCEPT_SQLSTATES One to ten 5-byte SQLSTATE values may be specified as acceptable SQLSTATES. The values would otherwise cause termination of subscription execution.

Column name	Description
APPLY_QUAL	From IBMSNAP_SUBS_SET.
SET_NAME	From IBMSNAP_SUBS_SET.
WHOS_ON_FIRST	From IBMSNAP_SUBS_SET.
BEFORE_OR_AFTER	Specifies either: A The statement is executed at the target server and should succeed the application of changes to the target tables. B The statement is executed at the target server and should succeed the application of changes to the target tables. S The statement is executed at the source server and should precede 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	Specifies either: E The SQL_STMT should be run as an EXEC SQL EXECUTE IMMEDIATE at the target server. C The SQL_STMT contains a stored procedure name to run as an EXEC SQL CALL at the target server.
SQL_STMT	Specifies either: E The SQL string runs as an EXEC SQL EXECUTE IMMEDIATE statement. C The 8-byte name of an SQL-stored procedure, without parameters or the CALL keyword, runs as an EXEC SQL CALL statement.
ACCEPT_SQLSTATES	One to ten 5-byte SQLSTATE values may be specified as acceptable SQLSTATES. The values would otherwise cause termination of subscription execution.

Figure 47. CREATE TABLE Statement

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 IBMSNAP_SUBS_STMTX ON ASN.IBMSANP_SUBS_STMTS (APPLY_QUAL, SET_NAME, WHOS_ON_FIRST, BEFORE_OR_AFTER, STMT_NUMBER);

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

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

Subscription Targets Member Table

ASN.IBMSNAP_SUBS_MEMBR

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

A unique index on APPLY_QUAL, SET_NAME, TARGET_OWNER, TARGET_TABLE, and TARGET_NAME is created automatically by the Control Center.

Table 40. Subscription Targets Member Table Columns

Column name Description
APPLY_QUAL From IBMSNAP_SUBS_SET.
SET_NAME From IBMSNAP_SUBS_SET.
Note: 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 From IBMSNAP_SUBS_SET.
SOURCE_OWNER The middle qualifier of the source table name.
SOURCE_TABLE The third qualifier of the source table name.
SOURCE_VIEW_QUAL Supports join subscriptions by matching the similar column in IBMSNAP_REGISTER.
TARGET_OWNER The middle qualifier of the target name. The value in SOURCE_OWNER should be used 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

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. This column can be updated directly with a predicate in situations where an out-of-range insert is performed by a local application.

Figure 48. CREATE TABLE Statement

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, PREDICATE VARCHAR (512)); CREATE UNIQUE INDEX IBMSNAP_SUBS_MEMIX ON ASN.IBMSNAP_SUBS_MEMBR (APPLY_QUAL, SET_NAME, WHOS_ON_FIRST SOURCE_OWNER, SOURCE_TABLE, SOURCE_VIEW_QUAL, TARGET_OWNER, TARGET_TABLE);

Note: You can introduce either source or target view names when there are multiple members with the same fully qualified source and target names to provide the necessary source/target member name uniqueness.

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

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

[ DB2 List of Books | Search the DB2 Books ]