| 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 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: 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 WebSphere® 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: 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.
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.
- 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).
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 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).
- 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 SQL*Loader 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 SQL*Loader, 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 SQL*Loader 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 SQL*Loader, 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).
|
| 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.
- C
- Classic
- 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
|
Q Replication and Event Publishing