IBMQREP_TARGETS table

The IBMQREP_TARGETS table stores Q subscription information for the Q Apply program, including type and state, default error actions, and rules for handling row conflicts.

Server: Q Apply server

Default schema: ASN

Unique index: SUBNAME, RECVQ

Non-unique index: TARGET_OWNER ASC, TARGET_NAME ASC, RECVQ ASC, SOURCE_OWNER ASC, SOURCE_NAME ASC

Non-unique index: RECVQ, SUB_ID

Non-unique index: SPILLQ, STATE

Important: Do not alter this table by 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_TARGETS table.

Table 1. Columns in the IBMQREP_TARGETS table
Column name	Description
SUBNAME	Data type: VARCHAR(132); Nullable: No The name of the Q subscription. It must be unique for each source-target pair, and cannot contain blanks.
RECVQ	Data type: VARCHAR(48); Nullable: No The name of the receive queue used for this Q subscription.
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
SOURCE_SERVER	Data type: VARCHAR(18); Nullable: No The name of the database or subsystem that contains the source table for this Q subscription. For z/OS®, this is a location name.
SOURCE_ALIAS	Data type: VARCHAR(8); Nullable: No The Db2® database alias that corresponds to the Q Capture server that is named in the SOURCE_SERVER column.
SOURCE_OWNER	Data type: VARCHAR(128); Nullable: No The schema name or high-level qualifier of the source table for this Q subscription.
SOURCE_NAME	Data type: VARCHAR(128); Nullable: No The name of the source table for this Q subscription.
SRC_NICKNAME_OWNER	Data type: VARCHAR(128); Nullable: Yes The schema of the nickname that is assigned to the source table for automatic loads that uses the LOAD from CURSOR utility when the Q Apply program is running on a non-z/OS platform.
SRC_NICKNAME	Data type: VARCHAR(128); Nullable: Yes The nickname that is assigned to the source table for automatic loads that uses the LOAD from CURSOR utility when the Q Apply program is running on a non-z/OS platform.
TARGET_OWNER	Data type: VARCHAR(128); Nullable: No The schema name or high-level qualifier of the target table or stored procedure for this Q subscription.
TARGET_NAME	Data type: VARCHAR(128); Nullable: No The name of the target table for this Q subscription.
TARGET_TYPE	Data type: INTEGER; Nullable: No, with default A flag that indicates the type of replication target. 1 (default) User table 2 Consistent-change-data (CCD) table 3 Reserved for future use. 4 Reserved for future use. 5 Stored procedure
FEDERATED_TGT_SRVR	Data type: VARCHAR(18); Nullable: Yes, with default The name of the non-Db2 relational database that contains the Q subscription target. Default: NULL
STATE	Data type: CHAR(1); Nullable: No, with default A flag that is inserted by the Q Apply program to describe the current state of the Q subscription. I (default) The Q Apply is not applying changes to the target because the Q subscription is new or in error. The Q Apply program discards all transactions that it receives for the Q subscription and waits for a new `subscription schema` message. L The Q Capture program has begun activating the Q subscription by sending a `subscription schema` message, and is sending changes from the source table. E The target table is being loaded by an external application. The Q Apply program is putting change messages in a spill queue while it waits for the table to be loaded. D The target table is loaded and the Q Apply program is ready to send a `load done` message to the Q Capture program. This state is used for automatic loads only. F The Q Apply program is applying messages from the spill queue (for internal load only). T The Q Apply program is terminating because of an error. It deactivates the Q subscription, then empties and deletes the spill queue. A The Q Apply program is applying changes to the target. R The Q Apply program is resuming operations after one of the following conditions occurred: During the initial loading of the target table, a `load done received` message was received from the Q Capture program. A Q subscription was placed in the spilled state (S) by the spillsub parameter of the MODIFY or asnqacmd command. The resumesub parameter places the Q subscription in the resuming state (R) after target table maintenance is complete. A Q subscription was placed in the spilled state (S) after an SQL error because the error action of B was set for the Q subscription. The resumesub parameter places the Q subscription in the resuming state (R) after the SQL error is fixed. This state means that the Q Apply program is processing rows that are in the spill queue. Until the Q Apply program empties the spill queue, incoming rows continue to be spilled. When the Q Apply program empties the spill queue, the Q subscription is placed in the active (A) state and normal operations resume. For external load, Q Apply changes the Q subscription state from E to R to A. For internal load, the state changes from L to D to F to R and then to A. S The Q Apply program is placing rows for the Q subscription in a temporary spill queue. Specifying the spillsub parameter places the Q subscription in the spilling state so that you can perform maintenance on the target table. W For peer-to-peer configurations with more than two servers and a load phase: The Q Apply program has seen a subscription schema message, is actively spilling changes to the source table, and is waiting for a confirmation (another schema message) from the Q Capture program to start loading. P An SQL error was detected while Q Apply was applying messages from a spill queue and the Q subscription was in F state. The Q Apply agent thread that was processing the spill queue stops. Future changes for the Q subscription are placed in the same spill queue until the resumesub parameter of the MODIFY or asnqacmd commands is issued. 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 Apply server of the last state change for this Q subscription. 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_APPLYTRACE table or the Q Apply diagnostic log.
SUBTYPE	Data type: CHAR(1); Nullable: No, with default A flag that indicates the type of replication that the Q subscription is involved in. U (default) Unidirectional replication. B Bidirectional replication. P Peer-to-peer replication.
CONFLICT_RULE	Data type: CHAR(1); Nullable: No, with default A flag that tells the Q Apply program how to look for conflicting changes to the target table. Inserts are always checked using the K (check only keys) rule because there are no before values and keys must be used to detect conflicts. K (default) Check only keys. The Q Apply program looks for conflicts by comparing the current value of the primary key in the target table with the old key value sent from the source table. C Check changed columns. Before updating target columns, the Q Apply program makes sure their current value matches the before values in the source columns. For deletes, the Q Apply program checks all columns. A Check all columns. Before updating or deleting a row, the Q Apply program makes sure that the current values in all columns match the old values in the source table. V Check version. In peer-to-peer replication, the Q Apply program checks the version column before applying a row.
CONFLICT_ACTION	Data type: CHAR(1); Nullable: No, with default A flag that tells the Q Apply program what to do when a row change conflicts: I (default) The Q Apply program does not apply the conflicting row but applies other rows in the transaction. F The Q Apply program tries to force the change. This requires that the Q Capture program send all columns, so the CHANGED_COLS_ONLY value must be set to N (no) in the IBMQREP_SUBS table. This is the default value while a target table is being loaded. D The Q Apply program does not apply the conflicting row but applies other rows in the transaction. Then it disables the Q subscription, stops applying transactions to the target, and sends an error report to the Q Capture program on the administration queue. S The Q Apply program rolls back the transaction, commits, and then stops. Q The Q Apply program stops reading from the queue. All conflicting rows are inserted into the IBMQREP_EXCEPTIONS table.
ERROR_ACTION	Data type: CHAR(1); Nullable: No, with default A flag that tells the Q Apply program what to do in case of an error such as an SQL error (other than a conflict) that prevent it from applying a row change. This flag does not affect Q Apply behavior for errors that are not related to applying a row change, for example IBM® MQ errors that are related to reading from a queue. Q (default) The Q Apply program stops reading from the queue. D The Q Apply program does not apply the conflicting row but applies other rows in the transaction. Then it disables the Q subscription, stops applying transactions to the target, and sends an error report to the Q Capture program on the administration queue. S The Q Apply program rolls back the transaction, commits, and then stops. B The Q Apply program starts putting change messages for the Q subscription in a temporary spill queue while the SQL error is being fixed. Use the resumesub parameter of the MODIFY command or asnqacmd command to prompt Q Apply to start applying messages from the spill queue to targets. To use this error action you must specify a model queue for the Q subscription when you create or change it with the replication administration tools. This error action is not supported for tables with referential integrity constraints. All conflicting rows are inserted into the IBMQREP_EXCEPTIONS table.
SPILLQ	Data type: VARCHAR(48); Nullable: Yes, with default The name of the temporary spill queue that the Q Apply program creates when it loads targets. Default: NULL
OKSQLSTATES	Data type: VARCHAR(128); Nullable: Yes, with default A list of space-separated SQLSTATE values that the Q Apply program does not consider as errors. You specify these values when you define a Q subscription. Values that are entered for OKSQLSTATES prompt the Q Apply program to bypass the error action that is specified for the Q subscription. OKSQLSTATES values do not affect conflicts such as duplicates and row-not-found errors, which are handled by the conflict action that is specified for the Q subscription. Restriction: The OKSQLSTATES feature is not supported when all of these conditions are true: The Q subscription uses expressions. At least one key column is used in the expression. The evaluation of the expression fails because of bad data or another reason. OKSQLSTATES is also not supported for DDL replication, such as ALTER TABLE. Default: NULL
SUBGROUP	Data type: VARCHAR(30); Nullable: Yes, with default The name of the peer-to-peer replication group that includes this Q subscription. Default: NULL
SOURCE_NODE	Data type: SMALLINT; Nullable: No, with default An identifying number for the source server in a peer-to-peer Q subscription. Default: 0
TARGET_NODE	Data type: SMALLINT; Nullable: No, with default An identifying number for the target server in a peer-to-peer Q subscription. Default: 0
GROUP_INIT_ROLE	Data type: CHAR(1); Nullable: Yes, with default The role of this target server in the process of initializing a peer-to-peer Q subscription. I The initiator of the peer-to-peer group, where the CAPSTART signal is entered into the IBMQREP_SIGNAL table to initialize the subscription. M A server in the peer-to-peer group that is not used to initialize the subscription. N A new server that is in the process of joining the peer-to-peer group. Default: NULL
HAS_LOADPHASE	Data type: CHAR(1); Nullable: No, with default A flag that indicates whether the target table will be loaded with data from the source. N (default) The target will not be loaded. I An automatic load. The Q Apply program loads the target table. E A manual load. An application other than the Q Apply program loads the target table.
LOAD_TYPE	Data type: SMALLINT; Nullable: No, with default A flag to indicate which utility is called to load the target table when HAS_LOADPHASE is I (automatic load). 0 (default) The Q Apply program selects the load utility from among the options below. If the source is a column-organized table, Q Apply uses an external table load with the REMOTESOURCE option, even if the target is a row-organized table. 1 Use the LOAD from CURSOR utility. The utility is invoked with an option to delete all data in the target table before replacing it with data from the source (this is called the replace option). This option is not supported for XML columns. Note: A nickname that refers to the source table is required for LOAD from CURSOR when the Q Apply program is on Linux®, UNIX, or Windows in the following cases: Q Apply is at Version 9.7 Fix Pack 3 or older and is remote from the Q Capture server. Q Apply is at Version 9.7 Fix Pack 4 or newer but the Q subscription includes XML columns. In all other cases, when Q Apply is on Linux, UNIX, or Windows it calls the LOAD from CURSOR utility by using a Db2 alias for a cataloged database rather than a nickname. You must add the Db2 alias to a password file that is created by the asnpwd utility. 101 Use the LOAD from CURSOR utility. The utility is invoked with an option to append source data to the target table without deleting target table contents. This is called the resume option on z/OS targets and the insert option on Linux, UNIX, and Windows targets. 2 Use the EXPORT and IMPORT utilities. The utilities are invoked with an option to delete all data in the target table before replacing it with data from the source (this is called the replace option). 102 Linux, UNIX, and Windows targets: Use the EXPORT and IMPORT utilities. The IMPORT utility is invoked with an option to append source data to the target table without deleting target table contents (this is called the insert option). 3 Use the EXPORT and LOAD utilities. The utilities are invoked with an option to delete all data in the target table before replacing it with data from the source (this is called the replace option). 103 Linux, UNIX, and Windows targets: Use the EXPORT and LOAD utilities. The LOAD utility is invoked with an option to append source data to the target table without deleting target table contents (this is called the insert option). 4 Select from a replication source and use the LOAD utility, or for Oracle targets use the SQLLoader utility (unidirectional replication only). The utilities are invoked with an option to delete all data in the target table before replacing it with data from the source (replace option). Oracle targets: To use SQLLoader, you must create a password file by using the asnpwd command in the directory that is specified by the apply_path parameter or the directory from which Q Apply is invoked with the following values for these keywords: alias: The Oracle tnsnames.ora entry that refers to the Oracle server (the same name that is used for the NODE option of the CREATE SERVER command for setting up federation). id: The remote user ID for connecting to Oracle. password: The password for connecting to Oracle. The file must have the default name asnpwd.aut. Before starting the Q subscription, you should test connectivity with this command: `$> sqlplus id/password@alias`. 104 Select from a replication source and use the LOAD utility, or for Oracle targets use the SQLLoader utility. The utilities are invoked with an option to append source data to the target table without deleting target table contents (resume or insert option). To use SQLLoader, follow the instructions in the entry for LOAD_TYPE 4 above. 5 Linux, UNIX, and Windows targets: Select from a replication source and use the IMPORT utility. The utility is invoked with an option to delete all data in the target table before replacing it with data from the source (this is called the replace option). 105 Linux, UNIX, and Windows targets: Select from a replication source and use the IMPORT utility. The utility is invoked with an option to append source data to the target table without deleting target table contents (this is called the insert option). 6 z/OS targets: Select from a replication source and use the LOAD utility with the REPLACE option. The Q Apply program calls the DB2 DSNUTILU stored procedure to invoke the utility. 106 z/OS targets: Select from a replication source and use the LOAD utility with the RESUME option. The Q Apply program calls the DB2 DSNUTILU stored procedure to invoke the utility. To use LOAD_TYPE 6 or 106 you must do these things first: Install DB2 APAR PI67793 Create the SYSPROC.DSNUTILU stored procedure with Workload Manager (WLM) running with NUMTCB=1 Bind DSNUTILU Add the collection ID for DSNUTILU to the Q Apply plan 7 The Q Apply program uses an external table load with the REMOTESOURCE option. Q Apply starts two threads to select the data from the external table into a pipe that is read by the second thread for insertion into the target table.
DESCRIPTION	Data type: VARCHAR(254); Nullable: Yes A user-supplied description of the Q subscription.
SEARCH_CONDITION	Data type: VARCHAR(2048); Nullable: Yes, with default The search condition that is used to filter rows for the Q subscription. This must be an annotated select WHERE clause, with a single colon directly in front of the names of the source columns. The Q Apply program also uses the search condition to filter rows when it loads target tables. Default: NULL
MODELQ	Data type: VARCHAR(36); Nullable: Yes, with default The name of the model queue that the Q Apply program uses to create spill queues during the target loading process. Default: IBMQREP.SPILL.MODELQ
CCD_CONDENSED	Data type: CHAR(1); Nullable: Yes, with default A flag that indicates whether a CCD target table is condensed or noncondensed. Y (default) The CCD table is condensed, which means that it contains one row for every key value in the source table and contains only the latest value for the row. N The CCD table is noncondensed, which means that it contains multiple rows with the same key value, one row for every change that occurs to the source table.
CCD_COMPLETE	Data type: CHAR(1); Nullable: Yes, with default A flag that indicates whether a CCD target table is complete or noncomplete. Y (default) The CCD table is complete, which means that it contains every row of interest from the source table and is initialized with a full set of source data. N The CCD table is noncomplete, which means that it contains only changes to the source table and starts with no data.
SOURCE_TYPE	Data type: CHAR(1); Nullable: Yes, with default The type of data source for the Q subscription. D Db2 Default: ' '
SCHEMA_SUBNAME	Data type: VARCHAR(64); Nullable: Yes The name of the source schema-level Q subscription if this table-level Q subscription was created by the Q Apply program or ASNCLP command-line program. The value is null otherwise.
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 "asnqapp" (the Q Apply program). When the Q Apply control tables are migrated from a release before Version 10 on Linux, UNIX, and Windows, the column value is NULL. Default: NULL
REPL_RENAME_COL	Data type: CHAR(1); Nullable: Yes, with default A flag that indicates whether the Q Apply program, in response to a RENAME COLUMN operation at the source table, renames the corresponding column in the target table. Note: This column is optional. Y (default) If the target column has the same name as the old column name at the source, Q Apply renames the target column to match the new source column name. N Q Apply does not rename the target column.
REPL_DROP_COL	Data type: CHAR(1); Nullable: Yes, with default A flag that indicates whether the Q Apply program, in response to a DROP COLUMN operation at the source table, drops the corresponding column in the target table: Note: This column is optional. Y (default) Q Apply drops the target column. N Q Apply does not drop the target column.
REPL_ALTER_COL	Data type: CHAR(1); Nullable: Yes, with default A flag that indicates whether the Q Apply program, in response to an ALTER COLUMN operation at the source table, alters the corresponding column in the target table: Y (default) Q Apply changes the column at the target table to match the change at the source column. N Q Apply does not replicate ALTER COLUMN changes from the source, and tolerates column data type or length mismatches as long as the mismatch is within the range of Db2 supported column alterations. Q Apply issues a warning for each column with an incompatible data type, length, scale, or precision, but proceeds with Q subscription activation.
SRC_LOCATION_ALIAS	Data type: VARCHAR(16); Nullable: Yes, with default The alias for the Db2 subsystem location that is named in the SOURCE_SERVER column and that contains the source table for this Q subscription. If this column contains a valid value, Q Apply overrides the SOURCE_SERVER value and instead uses the SRC_LOCATION_ALIAS value in the SQL SELECT for internal loads. You can use this option to avoid issues when the source and target servers have the same location name.
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.
CODEPAGE_EXPAND_FACTOR	Data type: SMALLINT; Nullable: Yes, with default A multiplying factor that the Q Apply program uses to increase the data length of a target column to account for data expansion when replicating from a source table with a fixed-size code page to a target table with a variable-size code page. The value that you specify lets Q Apply create or alter target columns with different lengths using the source column length multiplied by CODEPAGE_EXPAND_FACTOR. Q Apply uses this factor during DDL replication of ALTER ADD COLUMN or ALTER DATA TYPE operations. CODEPAGE_EXPAND_FACTOR is applied only when the source column length changes. Q Apply multiplies the new source column length times CODEPAGE_EXPAND_FACTOR to determine the new length of the corresponding target column. This parameter is applicable only for Db2 CHAR, VARCHAR, or CLOB columns and Oracle CHAR or VARCHAR2 columns. Allowed values are 1, 2, 3, and 4. The default value is 1.
CCD_ALLUPD_AS_DELINS	Data type: CHAR(1); Nullable: Yes with default When the target is a consistent-change data (CCD) table, specifies whether all updates at the source are changed to a delete and an insert at the target. N (default) After any update at the source, the Q Apply program updates the target row and sets the OPERATION column in the CCD table to U. Y After an update, Q Apply inserts a new row with the updated value. The result differs depending on whether the CCD table is condensed or noncondensed: Condensed The existing CCD table row is updated to mark IBMSNAP_OPERATION as D. A new row is inserted with the after values and with IBMSNAP_OPERATION set to I. Noncondensed Two rows are added to the CCD table, one with IBMSNAP_OPERATION set to D and including the before value of the update, and one set to I with the after values. For both CCD table types, the row changes are made in the same commit scope, and if the insert fails, the delete is rolled back.
CCD_KEYUPD_AS_DELINS	Data type: CHAR(1); Nullable: Yes with default When the target is a consistent-change data (CCD) table, specifies whether updates to key column values at the source are changed to a delete and an insert at the target. N (default) When an update at the source changes a key column value, the Q Apply program updates the target row and sets the OPERATION column in the CCD table to U. Y After a key update, Q Apply inserts a new row with the updated key value. This method is useful for targets that do not support updating the unique key. The result differs depending on whether the CCD table is condensed or noncondensed: Condensed The existing CCD table row is updated to mark IBMSNAP_OPERATION as D. A new row is inserted with the after values and with IBMSNAP_OPERATION set to I. Noncondensed Two rows are added to the CCD table, one with IBMSNAP_OPERATION set to D and including the before value of the update, and one set to I with the after values. For both CCD table types, the row changes are made in the same commit scope, and if the insert fails, the delete is rolled back.