IBMQREP_SUBS table

The Q subscription or publication name. For each instance of the Q Capture program, all Q subscription or publication names must be unique.

Data type: VARCHAR(128); Nullable: No

The schema name or high-level qualifier of the source table for this Q subscription or publication.

Data type: VARCHAR(128); VARCHAR(18) for DB2® for z/OS® Version 7 and Version 8 compatibility mode; Nullable: No

The name of the source table for this Q subscription or publication.

The name of the database or subsystem where the Q Apply program runs and targets are defined. For z/OS, this is a location name.

The DB2 database alias that corresponds to the Q Apply server that is named in the TARGET_SERVER column.

The schema name or high-level qualifier of the target table or stored procedure for a Q subscription.

The name of the target table for a Q subscription.

A flag that indicates the type of replication target.

1

User table

2

Consistent-change-data (CCD) table

3

Reserved for future use.

4

Reserved for future use.

5

Stored procedure

The schema of the Q Apply program that is applying transactions for this Q subscription.

The name of the WebSphere® MQ queue that the Q Capture program uses to send transactional data for this Q subscription or publication. Each source table is paired with one send queue.

The search condition that is used to filter rows for the Q subscription or publication. This must be an annotated select WHERE clause, with a single colon directly in front of the names of the source columns. Default: NULL

An integer that is generated by the Q Capture program and used to uniquely identify a Q subscription in the subscription schema message to the Q Apply program. Default: NULL

A flag that indicates the type of replication that a Q subscription is involved in, or whether this is a publication.

A flag that indicates the type of replication that a Q subscription is involved.

U (default)

Unidirectional replication. This value is also used for publications.

B

Bidirectional replication.

P

Peer-to-peer replication.

A flag that indicates whether the Q Capture program sends a message when a row in the source table changes, even if none of the columns that are part of a Q subscription changed:

N (default)

The Q Capture program sends a message only when columns that are part of a Q subscription change.

Y

When any row in the source table changes, the Q Capture program sends the columns from that row that are part of a Q subscription, even if none of them changed.

This column is deprecated for Version 9 Fix Pack 1.

A flag that indicates whether the Q Capture program publishes columns that are part of a Q subscription or publication only if they have changed. This field applies to update operations only.

Y (default)

When the Q Capture program sends an updated row, it sends only the changed columns that are part of a Q subscription or publication.

N

The Q Capture program sends all columns in a row that are part of a Q subscription or publication whenever any of them has changed.

A flag that indicates whether the target table for the Q subscription or publication will be loaded with data from the source:

I (default)

An automatic load. The Q Apply program calls one of several different utilities, depending on the LOAD_TYPE that is specified in the IBMQREP_TARGETS table, and on the platform of the Q Apply server and Q Capture server.

E

A manual load. An application other than the Q Apply program loads the target table. In this case, the user or Replication Center inserts the LOADDONE signal into the IBMQREP_SIGNAL table at the Q Capture server, or the Q Capture program inserts this signal after it receives the load done message.

N

The target will not be loaded.

A flag that is inserted by the Q Capture program to indicate the current state of the Q subscription or publication. The initial state is new, and the STATE_INFO field is initially set to ASN7024I (new Q subscription or publication).

N (default)

The Q subscription or publication is new. The Q Capture program automatically activates this Q subscription or publication when the program is started or reinitialized.

I

The Q subscription or publication is inactive. The Q Capture program saw a CAPSTOP signal in the log, or an error occurred and the Q subscription or publication was deactivated. The Q Capture program stopped sending messages for this Q subscription or publication but continued with others.

L

The Q subscription is loading. The Q Capture program processed the CAPSTART signal and sent the subscription schema message to the Q Apply program or user application. The Q Capture program is sending transaction messages that include before values for all columns, and it is waiting for the LOADDONE signal.

A

The Q subscription or publication is active. If there is a load phase, the Q Capture program processed the LOADDONE signal and sent a load done received message to the Q Apply program or user application. The Q Capture program is sending data messages based on the options defined for the Q subscription or publication.

T

An internal state that indicates that the Q Capture program read a CAPSTART signal in the log for this peer-to-peer Q subscription, and the Q subscription is being initialized within the peer-to-peer group.

G

An internal state that indicates that the Q Capture program read a CAPSTOP signal in the log for this peer-to-peer Q subscription, and the Q subscription is being deactivated within the peer-to-peer group.

U

An internal state that indicates that the Q Capture program created this Q subscription, and the Q subscription is waiting to be activated.

The timestamp in the local time of the Q Capture server of the last change in Q subscription or publication state. Default: Current® timestamp

The number for the ASN message about the Q subscription state. For details, see the IBMQREP_CAPTRACE table, or the Q Capture diagnostic log.

An internal value used to store half state and related information.

The name of the peer-to-peer group that includes this Q subscription. This column does not apply for a publication.

Default: NULL.

An identifying number for the source server in a peer-to-peer Q subscription. This column does not apply for a publication. Default: 0

An identifying number for the target server in a peer-to-peer Q subscription. This column does not apply for a publication. Default: 0

This column is updated by the Q Capture program when members join or leave a peer-to-peer group. Default: NULL

Reserved for future.

A flag that tells the Q Capture program whether to send rows that were deleted from the source table:

N (default)

Send deleted rows.

Y

Do not send deleted rows.

A user-supplied description of the Q subscription or publication.

A user-supplied topic to be included in the JMS-compliant (MQRFH2) message header for each XML message that is sent for the publication.

The action that the Q Capture program takes when the recovery log shows a load operation that uses the DB2 LOAD utility occurred at the source table:

W (default)

Q Capture issues a warning message after the load completes.

R

Q Capture issues a warning message and then stops and starts the Q subscription for the source table, prompting a load of the target table if one is specified for the Q subscription.

An SQL predicate that uses log record variables to filter which rows are replicated or published. This predicate does not require a WHERE clause like that used in the SEARCH_CONDITION column, but it can include the following log record variables:

$OPERATION

The DML operation. Valid values are I (insert), U (update), and D (delete).

$AUTHID

The authorization ID of a transaction.

$AUTHTOKEN

z/OS: The authorization token (job name) of a committed transaction.

$PLANNAME

z/OS: The plan name of a committed transaction.

For example, the following predicate tells Q Capture to only replicate or publish log records for insert operations committed by user HR for the affected Q subscription:

$OPERATION = 'I' AND $AUTHID = 'HR'

If a WHERE clause is specified in the SEARCH_CONDITION column, the value of CHANGE_CONDITION and SEARCH_CONDITION are combined into a single predicate by using the AND operator. However, you can leave the SEARCH_CONDITION column empty and specify the full predicate that includes a WHERE clause in this column. The value in this column is not used when the Q Apply program loads the target table.

For more detail on specifying filters that use log records, see Log record variables to filter rows (unidirectional replication).

Default: NULL

A flag that tells the Q Capture program whether to automatically add new source table columns to the Q subscription for the table. The columns are also added to the target table if they do not exist.

N (default)

Do not automatically add new columns to the Q subscription.

Y

When an ALTER TABLE ADD COLUMN statement is detected, automatically add any new columns to the Q subscription for the source table, and to the target table if the columns do not exist.

A flag that specifies whether the Q Capture program ignores (does not replicate) trigger-generated rows.

N (default)

Specifies that Q Capture replicates rows that are generated by trigger operations at the source.

Y

Specifies to not replicate rows that are generated by source trigger operations. This option can be useful if you have matching triggers on the target database and do not need trigger-generated rows to be replicated.

A flag that tells the Q Capture program whether to replicate delete operations that result from the delete of parent rows in tables with referential integrity relationships (ON DELETE CASCADE rule).

N (default)

When a parent row is deleted, Q Capture replicates the delete operations from child rows that result from the ON DELETE CASCADE rule.

Y

When a parent row is deleted, Q Capture replicates only the delete of the parent row. This option can be useful if you have the ON DELETE CASCADE rule in force at the target.

A flag that tells the Q Capture program whether to replicate UPDATE operations that result from the delete of parent rows in tables with referential integrity relationships (ON DELETE SET NULL rule). For Version 10.1 and later on Linux, UNIX, and Windows only.

N (default)

When a parent row is deleted at the source, Q Capture replicates the UPDATE operations from child rows in which one or more column values are set to NULL.

Y

Q Capture does not replicate UPDATE operations at child tables that result from the ON DELETE SET NULL rule. Only the deletion of the parent row is replicated. This option can be useful if you have the ON DELETE SET NULL rule in force at the target database and do not need these updates replicated.

The name of the schema-level subscription that this table-level Q subscription belongs to. This column is populated by the ASNCLP command-line program when a schema-level subscription is created, and by the Q Capture program when it detects a CREATE TABLE operation within the schema.

Identifies how this Q subscription was created. The values are "asnclp," "replcenter" (the Replication Center), and "asnqcap" (the Q Capture program). When the Q Capture control tables are migrated from a release before Version 10.1 on Linux, UNIX, and Windows, the column value is NULL. Default: NULL

Reserved for future use. Default: R

Reserved for future use. Default: D