Use the CREATE QSUB command to create one or more paired sets of Q subscriptions
for bidirectional replication. For high availability and disaster
recovery scenarios, you can use this command to create paired sets
of Q subscriptions for every table in every schema on both servers.
Syntax
>>-CREATE QSUB SUBTYPE B--+-----------------+--+-----------------------+-><
'-INCLUDE HISTORY-' +-| from-node-clause |--+
'-| for-tables-clause |-'
from-node-clause
|--+-------------------------------------------------------------------------------------------+-->
'-FROM NODE -servername.schemaname - SOURCE--| source-clause |--TARGET--| target-clause |-'
>--+-------------------------------------------------------------------------------------------+--|
'-FROM NODE -servername.schemaname - SOURCE--| source-clause |--TARGET--| target-clause |-'
for-tables-clause
|--FOR TABLES--(--NODE--node_number--| node-option |--)---------|
node-option
|--+-+--------------+--.--source_name-+-------------------------|
| '-source_owner-' |
'-| source-predicate |-------------'
source-predicate
|--+-OWNER LIKE--predicate1--+-----------------------+-+-------->
| '-NAME LIKE--predicate2-' |
+-+----------------------+--------------------------+
| '-NAME LIKE--predicate-' |
'-ALL-----------------------------------------------'
>--+---------------------------+--OPTIONS--options_list_name---->
'-TARGET EXISTS VALIDATE NO-'
>--COLS--+-ALL-------------------------+------------------------|
| .-,----------. |
| V | |
+-EXCLUDE---- (--column-+-- )-+
| .-,----------. |
| V | |
'-INCLUDE---- (--column-+-- )-'
source-clause
|--+-------------------------+--+-----------------------+------->
| .-N-. | | .-I-. |
'-ALL CHANGED ROWS--+-Y-+-' '-HAS LOAD PHASE--+-E-+-'
'-N-'
>--+---------------------+--+------------------------------+----|
| .-W-. | | .-YES-. |
'-CAPTURE_LOAD--+-R-+-' '-START AUTOMATICALLY--+-NO--+-'
target-clause
|--+----------------------+--+------------------------+--------->
| .-K-. | | .-I-. |
'-CONFLICT RULE--+-C-+-' '-CONFLICT ACTION--+-F-+-'
'-A-' +-D-+
+-S-+
'-Q-'
>--+---------------------+-------------------------------------->
| .-Q-. |
'-ERROR ACTION--+-D-+-'
+-S-+
'-B-'
>--+--------------------------------------------+--------------->
| .-0---------------------------. |
'-LOAD TYPE--+-1--+----------------------+-+-'
| '-| nickname-options |-' |
'-3---------------------------'
>--+--------------------------+---------------------------------|
'-OKSQLSTATES--"sqlstates"-'
nickname-options
|--+-----------------------------------------------------------------+--|
+-NICKNAME--+-owner.nickname--------+-----------------------------+
| '-NAMING PREFIX--prefix-' |
'-NEW NICKNAME RMT SERVERNAME--srvname--+-owner.nickname--------+-'
'-NAMING PREFIX--prefix-'
Parameters
- SUBTYPE B
- Specifies bidirectional Q subscriptions.
- INCLUDE HISTORY
- Specifies that the source table is a temporal table with versioning
and that you want to create a corresponding Q subscription for the
history table.
from-node-clause:
- FROM NODE servername.schemaname
- Use this option if you are creating only one paired set of Q subscriptions.
In the FROM NODE statement, you specify a server name and schema to
identify the location of the logical table that is the source for
the Q subscription. A FROM NODE statement is required if you want
to specify options for one or both of the Q subscriptions. If you
omit FROM NODE, both Q subscriptions will be created with the following
default options:
- ALL_CHANGED_ROWS=N
- BEFORE_VALUES=N
- CHANGED_COLS_ONLY=Y
- HAS_LOADPHASE=I
- CONFLICT_ACTION=I
- CONFLICT_RULE=K
- ERROR_ACTION=Q
for-tables-clause:
Use
this clause to specify one or more logical tables for which to create
paired sets of Q subscriptions. If you use this clause:
- A SET TABLES command is not needed before the CREATE QSUB command
in the ASNCLP script.
- You use a Q subscription profile to specify options for the Q
subscriptions by using the CREATE SUBSCRIPTION OPTIONS command. With
FOR TABLES, you do not use the COLS keyword to specify a subset of
columns or rows, and you do not use the source-clause and target-clause
to specify Q subscription options.
- NODE
- Specifies which server in the bidirectional configuration should
be used to locate the logical table on which the Q subscription is
based.
node-option
Use these options to select one or more
tables for which to create Q subscriptions.
- source_owner
- Specifies the schema of a single source table.
- source_name
- Specifies the name of a single source table.
source-predicate
Use these options to specify multiple source
tables for which to create Q subscriptions.
- OWNER LIKE
- Specifies a single database schema or schema pattern that uses
the percentage sign (%) as a wild card.
- NAME LIKE
- Specifies a single table name or table-naming pattern that uses
the percentage sign (%) as a wild card.
- ALL
- Specifies that you want to create Q subscriptions for all schemas
and all tables within those schemas.
- TARGET EXISTS VALIDATE NO
- Specifies that the target table exists and no validation is required
for Q subscriptions. This option shortens processing time with very
large tables.
Important: If you use these keywords, the
ASNCLP program assumes that the target table matches exactly with
the source table.
- OPTIONS
- Specifies the name of a profile (list of options) for creating
Q subscriptions. You create the profile by using the CREATE SUBSCRIPTION
OPTIONS command. The OPTIONS clause takes precedence over any previously
provided SET PROFILE command.
- COLS
- Specifies columns to be selected.
- ALL
- Select all columns in the Q subscription. This is the default.
- EXCLUDE (column )
- Exclude the specified columns from the Q subscription. The specified
columns are excluded from the target table when it is created. If
the target exists, the column names in the source table and target
table must be the same. Excluded columns must be nullable, or if defined
as NOT NULL then they must have a default value.
- INCLUDE (column )
- Include the specified columns in the Q subscription. If the target
table is new, the table is created with the specified columns. The
column names in the source table and target table must be the same.
source-clause:
- ALL CHANGED ROWS
- Specifies the data sending option.
- N
- Send a row only if a subscribed column in the source table changes.
- Y
- Send a row when any column in the source table changes.
- HAS LOAD PHASE
- Specifies whether the target table for the Q subscription will
be loaded with data from the source.
- I (default)
- Specifies an automatic load. The
Q Apply program calls the LOAD from CURSOR utility or EXPORT and LOAD
utilities, depending on the type of load that is specified in
the LOAD_TYPE keyword and on the platform of the
Q Apply server and Q Capture server.
- E
- Specifies a manual load. An application other than the Q Apply
program loads the target table. In this case, you insert the LOADDONE
signal (by using the LOADDONE command) into the
IBMQREP_SIGNAL table at the Q Capture server to inform the Q Capture
program that the application is done loading.
- N
- No load phase at the target.
- CAPTURE_LOAD
- Specifies the action that the Q Capture program takes when the
recovery log shows that a load operation that uses the DB2 LOAD utility
occurred at the source table. This parameter is only valid when the
HAS LOAD PHASE option is I.
- W (default)
- Q Capture issues a warning message after the load completes.
- R
- Q Capture 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.
- START AUTOMATICALLY
- Specifies how to start the Q subscription, which is represented
by the State column in the IBMQREP_SUBS table. The State column controls
whether the Q subscription is automatically started after starting
or reinitializing the Q Capture program (subscription state N), or
that the Q subscription must be started manually by inserting a command
in the IBMQREP_SIGNAL table (subscription state I).
- YES
- The Q subscription is started automatically (subscription state
value of N). This is the default.
- NO
- The Q subscription must be started manually (subscription state
value of I).
target-clause:
- CONFLICT RULE
-
- K
- Check only key values.
- C
- Check changed nonkey values and key values.
- A
- Check all values for updates.
- CONFLICT ACTION
-
- I
- Ignore.
- 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.
- D
- Disable the Q subscription.
- S
- Stop the Q Apply program.
- Q
- Stop reading from the receive queue.
- ERROR ACTION
- Specifies what action to take if an error occurs.
- Q
- Stop reading from the receive queue.
- D
- Disable the Q subscription and notify the Q Capture program.
- S
- Stop the Q Apply program without applying the transaction.
- B
- When an error occurs, spill change messages for the Q subscription
to a temporary spill queue until you use the resumesub parameter
of the MODIFY or asnqacmd command
to prompt Q Apply to begin applying the messages.
- OKSQLSTATES "sqlstates"
- Specifies a list of SQL statements within double quotation marks
that are not to be considered as errors when applying changes to this
table.
- LOAD TYPE
- Specifies the utilities that the Q Apply program uses to load
the target.
- 0
- Choose the best type automatically.
- 1
- Use LOAD from CURSOR only. Specify
this option if the source and target servers are on z/OS.
Note: If
the Q Apply program is at Version 9.7 Fix Pack 4 or newer, you do
not need to provide nickname information for this load option unless
the Q subscription includes XML columns. Q Apply calls LOAD from CURSOR
by specifying a cataloged DB2 alias for the source database instead
of by using a nickname. You must include the DB2 alias in a password
file that is created by the asnpwd utility.
- 3
- Use EXPORT and LOAD only.
nickname-options:
- NICKNAME
- Specifies an existing nickname for the Q Apply program to use
to load rows into the target table with the LOAD from CURSOR utility.
- owner.nickname
- Specifies the owner and name of an existing nickname.
- NAMING PREFIX prefix
- Use these keywords if you are creating multiple Q subscriptions
and nicknames already exist for LOAD from CURSOR. The variable prefix specifies
a character string that is used in naming all of the nicknames, and
that the ASNCLP program can use to find the nicknames. For example,
if you had 10 source tables named HR.T1 through HR.T10 and 10 nicknames
that reference these tables named HR.SRCNKT1 through HR.SRCNKT10,
you could use the string SRCNK to enable the ASNCLP program to find
the nicknames and use them in the Q subscription definitions.
- NEW NICKNAME RMT SERVERNAME srvname
- Specifies the name of the remote server if the ASNCLP program
creates the nickname for loading.
- NAMING PREFIX prefix
- Specifies a character string that the ASNCLP program can use to
generate one or more new nicknames for loading.
Usage notes
Q
subscriptions for tables that have referential integrity relationships
with each other should be created at the same time (in the same CREATE
QSUB command).
Table 1 shows the permitted combinations for BEFORE_VALUES
and CHANGE_COLS_ONLY depending on the values of CONFLICT_RULE and
CONFLICT_ACTION.
Recommendation: Always
use the ASNCLP or Replication Center to change the value of CONFLICT_RULE
and CONFLICT_ACTION. The administration tools automatically set the
correct value for BEFORE_VALUES and CHANGE_COLS_ONLY. Neither of these
attributes can be set explicitly by using the administration tools.
Excluded
columns from either source or target must be defined as nullable or
not null with default columns.
Columns defined with data types
ROWID and GENERATED ALWAYS are excluded automatically.
Table 1. Required attributes for BEFORE_VALUES and
CHANGE_COLS_ONLY depending on the values of CONFLICT_RULE and CONFLICT_ACTION| CONFLICT RULE |
CONFLICT ACTION |
BEFORE VALUES |
CHANGE COLS ONLY |
|---|
| K |
I, S, D, or Q |
N |
Y |
| K |
F |
N |
N |
| C |
I, S, D, or Q |
Y |
Y |
| C |
F |
Y |
N |
| A |
I, S, D, or Q |
Y |
N |
Example
The following commands create two Q
subscriptions for bidirectional replication between the SAMPLE and
SAMPLE2 servers. The commands specify an automatic load at both servers.
At SAMPLE, a CONFLICT_RULE of C (check changed key and non-key values)
and a CONFLICT_ACTION of F (force the change) are specified. At SAMPLE2,
a CONFLICT_RULE of C and a CONFLICT_ACTION of I (ignore) are specified.
To
identify the Q subscriptions, the first commands identify the subgroup,
the servers and schemas in the subgroup, and the two replication queue
maps. The SET TABLES command specifies the RED.EMPLOYEE table at the
SAMPLE database. The command generates statements to create a matching
table at SAMPLE2.
SET SUBGROUP "bidirgroup"
SET BIDI NODE 1 SERVER DBALIAS SAMPLE SCHEMA RED;
SET BIDI NODE 2 SERVER DBALIAS SAMPLE2 SCHEMA BLUE;
SET CONNECTION SOURCE "SAMPLE".RED TARGET "SAMPLE2".BLUE REPLQMAP
"SAMPLE_RED_TO_SAMPLE2_BLUE";
SET CONNECTION SOURCE "SAMPLE2".BLUE TARGET "SAMPLE".RED REPLQMAP
"SAMPLE2_BLUE_TO_SAMPLE_RED";
SET TABLES (SAMPLE.RED.RED.EMPLOYEE);
CREATE QSUB SUBTYPE B
FROM NODE SAMPLE.RED SOURCE HAS LOAD PHASE I
TARGET CONFLICT RULE C CONFLICT ACTION F
FROM NODE SAMPLE2.BLUE SOURCE HAS LOAD PHASE I
TARGET CONFLICT RULE C CONFLICT ACTION I
Example: Subsetting
columns - include
The following command creates a bidirectional
Q subscription that includes only columns c1, c2, c3, and c4:
CREATE QSUB SUBTYPE B COLS INCLUDE (C1,C2,C3,C4)
Example: Subsetting
columns - exclude
The following command creates a bidirectional
Q subscription that excludes columns C1, C2, and C3:
CREATE QSUB SUBTYPE B SOURCE HAS LOAD PHASE I COLS EXCLUDE (C1,C2,C3)
Example: Using LOAD
from CURSOR
The following commands set the environment and
then create a bidirectional Q subscription for a single table that
specifies the LOAD from CURSOR utility (LOAD TYPE 1) over a nickname.
Q Apply will create the nickname. The remote server name is SRCSVR1,
the source table is HR.TABLE1, and the nickname that references the
source table is HR.SRCNKTABLE1:
ASNCLP SESSION SET TO Q REPLICATION;
SET BIDI NODE 1 SERVER DBALIAS REDDB;
SET BIDI NODE 2 SERVER DBALIAS BLUEDB;
SET TABLES (REDDB.ASN.HR.TABLE1);
CREATE QSUB SUBTYPE B FROM NODE REDDB.ASN SOURCE HAS LOAD PHASE I
TARGET LOAD TYPE 1 NEW NICKNAME RMTSERVERNAME SRCSVR1 HR.SRCNKTABLE1;
Example: Multiple
tables with LOAD from CURSOR
The following commands set the
environment and then create bidirectional Q subscriptions for all
source tables with the schema "HR." The Q subscriptions specify the
LOAD from CURSOR utility over a nickname. The ASNCLP program will
create a nickname for each source table and use the naming prefix
"SRCNK" to generate the nickname names. The remote server name is
SRCSVR1:
ASNCLP SESSION SET TO Q REPLICATION;
SET BIDI NODE 1 SERVER DBALIAS REDDB;
SET BIDI NODE 2 SERVER DBALIAS BLUEDB;
SET TABLES (NODE 1 SRC OWNER LIKE "HR%");
CREATE QSUB SUBTYPE B FROM NODE REDDB.ASN SOURCE HAS LOAD PHASE I
TARGET LOAD TYPE 1 NEW NICKNAME RMTSERVERNAME SRCSVR1 NAMING PREFIX SRCNK;
LOAD from CURSOR
with an existing nickname
The following commands set the environment
and then create a bidirectional Q subscription for the source table
HR.EMPLOYEE, specifying that the existing nickname HR.SOURCENICK should
be used by the LOAD from CURSOR utility:
ASNCLP SESSION SET TO Q REPLICATION;
SET BIDI NODE 1 SERVER DBALIAS REDDB;
SET BIDI NODE 2 SERVER DBALIAS BLUEDB;
SET TABLES (SAMPLE.ASN.HR.EMPLOYEE);
CREATE QSUB SUBTYPE B FROM NODE REDDB.ASN SOURCE HAS LOAD PHASE I
TARGET LOAD TYPE 1 NICKNAME HR.SOURCENICK;
Using FOR TABLES clause
to create multiple Q subscriptions
The following example
uses the CREATE SUBSCRIPTION OPTIONS command to create a profile,
bidilist, for specifying options for multiple Q subscriptions. To
use the profile, the FOR TABLES keywords are specified. The OWNER
LIKE keywords prompt the ASNCLP program to create Q subscriptions
for all tables within schemas on the SAMPLE1 server (NODE 1) that
begin with the letters AIRUKU. Because FOR TABLES is used, no SET
TABLES commands are required.
SET BIDI NODE 1 SERVER DBALIAS SAMPLE1;
SET BIDI NODE 2 SERVER DBALIAS SAMPLE2;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
CREATE SUBSCRIPTION OPTIONS bidilist
HAS LOAD PHASE E
CONFLICT ACTION F;
CREATE QSUB SUBTYPE B FOR TABLES
(NODE 1 OWNER LIKE “AIRUKU%”
TARGET EXISTS VALIDATE NO
OPTIONS bidilist);
Q Replication and Event Publishing