IBMQREP_SUBS table

The IBMQREP_SUBS table contains information about Q subscriptions and publications, including the type, search conditions, data-sending options, load options, and state.

Server: Q Capture server

Default schema: ASN

Primary key: SUBNAME

Non-unique index: SUB_ID

Important: Do not alter this table using SQL. Altering this table inappropriately can cause unexpected results and loss of data.

Table 1 provides a brief description of the columns in the IBMQREP_SUBS table.

Table 1. Columns in the IBMQREP_SUBS table
Column name	Description
SUBNAME	Data type: VARCHAR(132); Nullable: No The Q subscription or publication name. For each instance of the Q Capture program, all Q subscription or publication names must be unique.
SOURCE_OWNER	Data type: VARCHAR(128); Nullable: No The schema name or high-level qualifier of the source table for this Q subscription or publication.
SOURCE_NAME	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.
TARGET_SERVER	Data type: VARCHAR(18); Nullable: Yes 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.
TARGET_ALIAS	Data type: VARCHAR(8); Nullable: Yes The Db2 database alias that corresponds to the Q Apply server that is named in the TARGET_SERVER column.
TARGET_OWNER	Data type: VARCHAR(128); Nullable: Yes The schema name or high-level qualifier of the target table or stored procedure for a Q subscription.
TARGET_NAME	Data type: VARCHAR(128); Nullable: Yes The name of the target table for a Q subscription.
TARGET_TYPE	Data type: INTEGER; Nullable: Yes 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
APPLY_SCHEMA	Data type: VARCHAR(128); Nullable: Yes The schema of the Q Apply program that is applying transactions for this Q subscription.
SENDQ	Data type: VARCHAR(48); Nullable: No The name of the IBM® 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.
SEARCH_CONDITION	Data type: VARCHAR(2048); Nullable: Yes, with default 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
SUB_ID	Data type: INTEGER; Nullable: Yes, with default 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
SUBTYPE	Data type: CHAR(1); Nullable: No, with default A flag that indicates the type of replication that a Q subscription is involved in. U (default) Unidirectional replication. This value is also used for publications. B Bidirectional replication. P Peer-to-peer replication.
ALL_CHANGED_ROWS	Data type: CHAR(1); Nullable: No, with default 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.
BEFORE_VALUES	Data type: CHAR(1); Nullable: No, with default For an update operation, this flag indicates whether the Q Capture program sends the before values of non-key columns in addition to their after values. For a delete, this flag indicates whether the Q Capture program sends the before values of non-key columns in addition to the before values of the key columns. N (default) The Q Capture program does not send before values of non-key columns that change. If a key column changes, the Q Capture program sends both its before and after values. For delete statements that involve key columns, only before values are sent. Y When there are changes to non-key columns in the source table that are part of a Q subscription or publication, the Q Capture program sends both before and after values. You must use BEFORE_VALUES=Y in the following cases: When all replicated columns in a Q subscription are used in the replication key (IS_KEY=Y in the IBMQREP_TRG_COLS table) When the target is federated When the conflict rule is A or C When publishing delimited messages
CHANGED_COLS_ONLY	Data type: CHAR(1); Nullable: No, with default 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.
HAS_LOADPHASE	Data type: CHAR(1); Nullable: No, with default 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.
STATE	Data type: CHAR(1); Nullable: No, with default 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. D Activation of the Q subscription is being temporarily delayed to control the number of simultaneous load operations for target tables. This state is enabled when you specify the max_capstarts_intload parameter. W Q Capture is waiting for all in-flight transactions to complete before allowing the export of data from the source table to begin for internal loading of the target. 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 bidirectional or peer-to-peer Q subscription, and the Q subscription is being deactivated. U An internal state that indicates that the Q Capture program created this Q subscription because there is a schema-level subscription, and the Q subscription is waiting to be activated. When the Q Capture program detects newly created tables in the schema, it inserts a STATE of U in the IBMQREP_SUBS and IBMQREP_TARGETS tables. The IBMQREP_SRC_COLS and IBMQREP_TRG_COLS tables are not populated for the subscription at this time. Q Capture does not activate Q subscriptions that are in U state until it detects a DML or load operation for the table. When it detects those operations, it populates IBMQREP_SRC_COLS and IBMQREP_TRG_COLS and also activates the subscription. Important: Do not try to activate a subscription that is in U state; the CAPSTART operation will fail.
STATE_TIME	Data type: TIMESTAMP; Nullable: No, with default The timestamp in the local time of the Q Capture server of the last change in Q subscription or publication state. Default: Current timestamp
STATE_INFO	Data type: CHAR(8); Nullable: Yes The number for the ASN message about the Q subscription state. For details, see the IBMQREP_CAPTRACE table, or the Q Capture diagnostic log.
STATE_TRANSITION	Data type: VARCHAR(256) FOR BIT DATA; Nullable: Yes An internal value used to store half state and related information.
SUBGROUP	Data type: VARCHAR(30); Nullable: Yes, with default The name of the bidirectional or peer-to-peer group that includes this Q subscription. This column does not apply for a publication. Default: NULL.
SOURCE_NODE	Data type: SMALLINT; Nullable: No, with default An identifying number for the source server in a bidirectional or peer-to-peer Q subscription. This column does not apply for a publication. Default: 0
TARGET_NODE	Data type: SMALLINT; Nullable: No, with default An identifying number for the target server in a bidirectional or peer-to-peer Q subscription. This column does not apply for a publication. Default: 0
GROUP_MEMBERS	Data type: CHAR(254) FOR BIT DATA; Nullable: Yes, with default This column is updated by the Q Capture program when members join or leave a bidirectional or peer-to-peer group. Default: NULL
OPTIONS_FLAG	Data type: CHAR(4); Nullable: No, with default Reserved for future.
SUPPRESS_DELETES	Data type: CHAR(1);Nullable: No, with default 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.
DESCRIPTION	Data type: VARCHAR(200); Nullable: Yes A user-supplied description of the Q subscription or publication.
TOPIC	Data type: VARCHAR(256); Nullable: Yes A user-supplied topic to be included in the JMS-compliant (MQRFH2) message header for each XML message that is sent for the publication.
CAPTURE_LOAD	Data type: CHAR(1); Nullable: No, with default The action that the Q Capture program takes when it detects a load operation that uses the Db2 LOAD utility 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. L (z/OS) When the LOAD utility is run against a source table without any input records to be loaded (a dummy load), replication calls the DSNUTILS stored procedure to perform a dummy load at the target table. With this option the Q Apply program does not need to connect to the source database or create a spill queue for the loading process. If the Q subscription is for a partitioned table, the Q Capture program detects the load of the partition and notifies the Q Apply program to load that partition only and not load any other partitions that are included in the Q subscription.
CHANGE_CONDITION	Data type: VARCHAR(2048); Nullable: Yes, with default An SQL predicate that uses log record variables to filter which rows are replicated or published. See Log record variables to filter rows (unidirectional replication) for more information. Default: NULL
REPL_ADDCOL	Data type: CHAR(1); Nullable: No, with default 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. Note: This option is only supported for Q Replication. For event publishing with XML or delimited messages, you must use the ADDCOL signal to add a column to an existing publication. 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.
IGNTRIG	Data type: CHAR(1); Nullable: No, with default 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.
IGNCASDEL	Data type: CHAR(1); Nullable: No, with default 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.
IGNSETNULL	Data type: CHAR(1); Nullable: No, with default 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.
SCHEMA_SUBNAME	Data type: VARCHAR(128) on z/OS; VARCHAR(64) on Linux, UNIX, and Windows; Nullable: Yes 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.
SUB_CREATOR	Data type: VARCHAR(12); Nullable: Yes, with default 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
CAPTURE_TRUNCATE	Data type: CHAR(1); Nullable: No, with default A flag that specifies the action that the Q Capture program takes when it detects a TRUNCATE operation for a source table in the log. z/OS: The CAPTURE_TRUNCATE column is available only for Db2 on the Linux, AIX, and Windows platforms. The reason is that, in Db2 for z/OS, a truncate operation on a table with Data Capture Changes is not executed as a truncate operation, but as a massive delete. Replication replays the truncate as delete operations at the target. On Linux, AIX, and Windows platforms, a truncate operation is replicated as a truncate. To run a truncate table operation on the z/OS platform for a table with DCC (Data Capture Changes), you need to do a load from dummy input, which has the same effect of truncating the table and is replicated as a load from dummy. See CAPTURE_LOAD=L. W (default) Q Capture issues a warning message but does not replicate the TRUNCATE operation. T Q Capture replicates the TRUNCATE operation. Restrictions: Replication of TRUNCATE operations is not supported when the target is a noncomplete consistent-change data (CCD) table (CCD_COMPLETE=N). For this target type, a warning is always issued for TRUNCATE operations. If the target table has referential integrity constraints, do not perform a truncate operation at the source table unless CAPTURE_TRUNCATE=W.
LOGRD_ERROR_ACTION	Data type: CHAR(1); Nullable: No, with default Reserved for future use.
HAS_PARTS	Data type: CHAR(1); Nullable: No, with default z/OS: A flag that indicates whether the Q subscription filters rows for replication based on one or more partitions of a range-partitioned table. N (default) The Q subscription does not use any filtering of rows that is based on partitions of a range-partitioned table. Y The Q subscription filters rows for replication based on whether the rows belong to specific partitions of a range-partitioned table. The partitions that are used for filtering are specified in the IBMQREP_SUB_PARTS control table.