Use the CREATE QSUB command to create one or more paired sets of Q subscriptions
for logical tables that participate in peer-to-peer replication.
Syntax
>>-CREATE QSUB--SUBTYPE P--+----------------------------+------><
+-SOURCE--| source-clause |-+
+-TARGET--| target-clause |-+
'-| for-tables-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
|--+-----------------------+--+---------------------+----------->
| .-I-. | | .-W-. |
'-HAS LOAD PHASE--+-E-+-' '-CAPTURE_LOAD--+-R-+-'
'-N-'
>--+------------------------------+-----------------------------|
| .-YES-. |
'-START AUTOMATICALLY--+-NO--+-'
target-clause
|--+---------------------+-------------------------------------->
| .-Q-. |
'-ERROR ACTION--+-D-+-'
+-S-+
'-B-'
>--+--------------------------------------------+--------------->
| .-0---------------------------. |
'-LOAD TYPE--+-1--+----------------------+-+-'
| '-| nickname-options |-' |
'-3---------------------------'
>--+--------------------------+--| period-clause |--------------|
'-OKSQLSTATES--"sqlstates"-'
nickname-options
|--+-----------------------------------------------------------------+--|
+-NICKNAME--+-owner.nickname--------+-----------------------------+
| '-NAMING PREFIX--prefix-' |
'-NEW NICKNAME RMT SERVERNAME--srvname--+-owner.nickname--------+-'
'-NAMING PREFIX--prefix-'
Parameters
- SUBTYPE P
- Specifies Q subscriptions for peer-to-peer replication.
for-tables-clause:
Use
this clause to specify one or more logical tables for which to create
paired sets of Q subscriptions between the peer servers. 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 peer-to-peer 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. If replication
creates the target table, the specified columns are excluded. 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. If
the target table exists, then the specified columns are included in
the table. The column names in the source table and target table must
be the same.
source-clause:
- HAS LOAD PHASE
- Specifies whether the tables that are specified in the Q subscriptions
will be loaded with data from one of the peer copies of the table.
- 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 (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.
- CAPTURE_LOAD
- For peer-to-peer replication with two servers only: 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.
- 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:
- ERROR ACTION
- Specifies what action to take if an error occurs.
- Q
- Stop reading from the receive queue.
- D
- Disable 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.
- LOAD TYPE
- Specifies a type of load.
- 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.
- OKSQLSTATES "sqlstates"
- Specifies a list of SQL statements within double quotation marks
that are not to be considered as error when applying changes to this
table.
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 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
- Convergence columns and triggers will be created on the tables
that participate in the peer-to-peer replication setup.
- For peer-to-peer replication with convergence, only the attributes
shown in Table 1 are allowed
(and are implicitly assigned).
Table 1. Attributes for peer-to-peer replication
with convergence| Conflict Rule |
Conflict Action |
Before Values |
Changed Columns Only |
|---|
| V |
F |
N |
N |
Example
The following script creates Q subscriptions
for the STAFF table at SAMPLE, SAMPLE2, and SAMPLE3 in a peer-to-peer
configuration with three servers. The Q subscriptions specify no load
phase and an error action that prompts the Q Apply program to stop
reading from the receive queue if an error occurs.
To identify the
Q subscriptions, the first commands identify the subgroup, the servers
and schemas in the subgroup, and the replication queue maps. The SET
TABLES command specifies GRAY.STAFF at the SAMPLE database, which
will generate SQL statements to create matching tables at the other
two servers.
SET SUBGROUP "p2p3group";
SET PEER NODE 1 SERVER DBALIAS SAMPLE SCHEMA GRAY;
SET PEER NODE 2 SERVER DBALIAS SAMPLE2 SCHEMA BROWN;
SET PEER NODE 3 SERVER DBALIAS SAMPLE3 SCHEMA YELLOW;
SET CONNECTION SOURCE "SAMPLE".GRAY TARGET SAMPLE2.BROWN REPLQMAP
"SAMPLE_GRAY_TO_SAMPLE2_BROWN";
SET CONNECTION SOURCE "SAMPLE".GRAY TARGET SAMPLE3.YELLOW REPLQMAP
"SAMPLE_GRAY_TO_SAMPLE3_YELLOW";
SET CONNECTION SOURCE SAMPLE2.BROWN TARGET SAMPLE.GRAY REPLQMAP
"SAMPLE2_BROWN_TO_SAMPLE_GRAY";
SET CONNECTION SOURCE SAMPLE2.BROWN TARGET SAMPLE3.YELLOW REPLQMAP
"SAMPLE2_BROWN_TO_SAMPLE3_YELLOW";
SET CONNECTION SOURCE SAMPLE3.YELLOW TARGET SAMPLE.GRAY REPLQMAP
"SAMPLE3_YELLOW_TO_SAMPLE_GRAY";
SET CONNECTION SOURCE SAMPLE3.YELLOW TARGET SAMPLE2.BROWN REPLQMAP
"SAMPLE3_YELLOW_TO_SAMPLE2_BROWN";
SET TABLES (SAMPLE.GRAY.GRAY.STAFF);
CREATE QSUB SUBTYPE P SOURCE HAS LOAD PHASE N TARGET ERROR ACTION Q;
Example: Subsetting
columns - exclude
The following command creates a peer-to-peer
Q subscription that excludes columns C1 and cC2:
CREATE QSUB SUBTYPE P COLS EXCLUDE (C1,C2)
Example: Subsetting
columns - include
The following command creates a peer-to-peer
Q subscription that includes columns C1, C2, and C3:
CREATE QSUB SUBTYPE P SOURCE HAS LOAD PHASE I COLS INCLUDE (C1,C2,C3)
Example: Using LOAD
from CURSOR
The following commands set the environment and
then create a peer-to-peer 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 PEER NODE 1 SERVER DBALIAS REDDB;
SET PEER NODE 2 SERVER DBALIAS BLUEDB;
SET TABLES (REDDB.ASN.HR.TABLE1);
CREATE QSUB SUBTYPE P 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 peer-to-peer 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 PEER NODE 1 SERVER DBALIAS REDDB;
SET PEER NODE 2 SERVER DBALIAS BLUEDB;
SET TABLES (NODE 1 SRC OWNER LIKE "HR%");
CREATE QSUB SUBTYPE P 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 peer-to-peer 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 PEER NODE 1 SERVER DBALIAS REDDB;
SET PEER NODE 2 SERVER DBALIAS BLUEDB;
SET TABLES (SAMPLE.ASN.HR.EMPLOYEE);
CREATE QSUB SUBTYPE P 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,
p2plist, 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 PEER NODE 1 SERVER DBALIAS SAMPLE1;
SET PEER NODE 2 SERVER DBALIAS SAMPLE2;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
CREATE SUBSCRIPTION OPTIONS p2plist
HAS LOAD PHASE E;
CREATE QSUB SUBTYPE P FOR TABLES
(NODE 1 OWNER LIKE “AIRUKU%”
TARGET EXISTS VALIDATE NO
OPTIONS p2plist);
Q Replication and Event Publishing