IBMQREP_TARGETS table (native Oracle targets)

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: Oracle database

Default schema: ASN

Unique index: SUBNAME ASC, RECVQ ASC

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

Non-unique index: RECVQ ASC, SUB_ID ASC

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 for native Oracle targets
Column name Description
SUBNAME Data type: VARCHAR2(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: VARCHAR2(48); Nullable: No

The name of the receive queue used for this Q subscription.
SUB_ID Data type: NUMBER(10); 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: VARCHAR2(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: VARCHAR2(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: VARCHAR2(128); Nullable: No

The schema name or high-level qualifier of the source table for this Q subscription.
SOURCE_NAME Data type: VARCHAR2(128); Nullable: No

The name of the source table for this Q subscription.
TARGET_OWNER Data type: VARCHAR2(128); Nullable: No

The schema name of the target table for this Q subscription.
TARGET_NAME Data type: VARCHAR2(128); Nullable: No

The name of the target table for this Q subscription.
TARGET_TYPE Data type: NUMBER(10); Nullable: No, with default

A flag that indicates the type of replication target.

1 (default)
User table
2
Reserved.
3
Reserved.
4
Reserved.
STATE Data type: CHARACTER(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.
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 Apply program created this Q subscription, and the Q subscription has not been activated yet.
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: CHARACTER(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: CHARACTER(1); Nullable: No, with default

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

U (default)
Unidirectional replication.
CONFLICT_RULE Data type: CHARACTER(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: CHARACTER(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: CHARACTER(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 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: VARCHAR2(48); Nullable: Yes, with default

The name of the temporary spill queue that the Q Apply program creates when it loads targets. Default: NULL
OK_ERROR_CODES Data type: VARCHAR2(128); Nullable: Yes, with default

A list of space-separated Oracle error code 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 OK_ERROR_CODES prompt the Q Apply program to bypass the error action that is specified for the Q subscription. OK_ERROR_CODES 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 OK_ERROR_CODES 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.
Default: NULL
HAS_LOADPHASE Data type: CHARACTER(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
0 (default)
The Q Apply program selects the load utility.
4
Use the SQL*Loader utility. The utility is invoked with an option to delete all data in the target table before replacing it with data from the source (replace option). Q Apply also sets the SQL*Loader ERRORS option (maximum number of errors to allow before terminating the load) to 0 to prevent data loss at the target.
DESCRIPTION Data type: VARCHAR2(254); Nullable: Yes

A user-supplied description of the Q subscription.
SEARCH_CONDITION Data type: VARCHAR2(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. Default: NULL
MODELQ Data type: VARCHAR2(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: CHARACTER(1); Nullable: Yes, with default

Reserved. Default: N
CCD_COMPLETE Data type: CHARACTER(1); Nullable: Yes, with default

Reserved. Default: N
SOURCE_TYPE Data type: CHARACTER(1); Nullable: Yes, with default

The type of data source for the Q subscription.

D
Db2
Default: NULL
SCHEMA_SUBNAME Data type: VARCHAR(64); Nullable: Yes

Reserved.
SUB_CREATOR Data type: VARCHAR(12); Nullable: Yes, with default

Identifies how this Q subscription was created. Default: NULL