IBM InfoSphere Replication Server and Data Event Publisher
Design, implement, and monitor a successful Q replication and Event Publishing project
Covers the toolsets needed to implement a successful Q replication project
Aimed at the Linux, Unix, and Windows operating systems, with many concepts common to z/OS as well
A chapter dedicated exclusively to WebSphere MQ for the DB2 DBA
Detailed step-by-step instructions for 13 Q replication scenarios with troubleshooting and monitoring tips
Written in a conversational and easy to follow manner
Checking the state of a Q subscription
The state of a Q subscription is recorded in the IBMQREP_SUBS table, and can be queried as follows:
db2 "SELECT SUBSTR(subname,1,10) AS subname,
state FROM asn.ibmqrep_subs"
SUBNAME STATE
-------- -----
DEPT0001 A
XEMP0001 A
Stopping a Q subscription
The command to stop a Q subscription is STOP QSUB SUBNAME <qsubname>. Note that if Q Capture is not running, then the command will not take effect until Q Capture is started, because the STOP QSUB command generates an INSERT command into the IBMQREP_SIGNAL table:
INSERT INTO ASN.IBMQREP_SIGNAL
(signal_type, signal_subtype, signal_input_in)
VALUES
('CMD', 'CAPSTOP', 'T10001');
In a unidirectional setup, to stop a Q subscription called T10001 where the Q Capture and Q Apply control tables have a schema of ASN, create a text file called SYSA_qsub_stop_uni.asnclp containing the following ASNCLP commands:
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2B;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
stop qsub subname T10001;
In bidirectional or peer-to-peer two-way replication, we have to specify both Q subscriptions (T10001 and T10002) for the subscription group:
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2B;
stop qsub subname T10001;
SET SERVER CAPTURE TO DB DB2B;
SET SERVER TARGET TO DB DB2A;
stop qsub subname T10002;
In a Peer-to-peer four-way setup, the commands would be in a file called qsub_stop_p2p4w.asnclp containing the following ASNCLP commands:
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2B;
stop qsub subname T10001;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2C;
stop qsub subname T10002;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2D;
stop qsub subname T10003;
Dropping a Q subscription
The ASNCLP command to drop a Q subscription is:
DROP QSUB (SUBNAME <qsubname> USING REPLQMAP <repqmapname>);
In a unidirectional setup, to drop a Q subscription called T10001, which uses a Replication Queue Map called RQMA2B, create a file called drop_qsub_uni.asnclp containing the following ASNCLP commands:
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2B;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
drop qsub (subname TAB1 using replqmap RQMA2B);
We can use the SET DROP command to specify whether for unidirectional replication the target table and its table space are dropped when a Q subscription is deleted:
SET DROP TARGET [NEVER|ALWAYS]
The default is not to drop the target table.
In a multi-directional setup, there are three methods we can use:
In the first method, we need to issue the DROP QSUB command twice, once for the Q subscription from DB2A to DB2B and once for the Q subscription from DB2B to DB2A. In this method, we need to know the Q subscription and Replication Queue Map names, which is shown in the qsub_drop_bidi0.asnclp file:
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2B;
drop qsub (subname T10001 using replqmap RQMA2B);
SET SERVER CAPTURE TO DB DB2B;
SET SERVER TARGET TO DB DB2A;
drop qsub (subname T10002 using replqmap RQMB2A);
In the second method, we use the DROP SUBTYPE command, which is used to delete the multi-directional Q subscriptions for a single logical table. We use the DROP SUBTYPE command with the SET REFERENCE TABLE construct, which identifies a Q subscription for multi-directional replication. An example of using these two is shown in the following content file, which drops all the Q subscriptions for the source table eric.t1. This content file needs to be called from a load script file.
SET SUBGROUP "TABT1";
SET SERVER MULTIDIR TO DB "DB2A";
SET SERVER MULTIDIR TO DB "DB2B";
SET REFERENCE TABLE USING SCHEMA "DB2A".ASN
USES TABLE eric.t1;
DROP SUBTYPE B QSUBS;
The USING SCHEMA part of the SET REFERENCE TABLE command identifies the server that contains the table (DB2A) and the schema (ASN) of the control tables in which this table is specified as a source and target. The USES TABLE part specifies the table schema (eric) and table name (t1) to which the Q subscription applies.
When we use this command, no tables or table spaces are ever dropped.
The SUBGROUP name must be the valid for the tables whose Q subscriptions we want to drop. We can find the SUBGROUP name for a table using the following query:
db2 "SELECT SUBSTR(subgroup,1,10) AS subsgroup, SUBSTR(source_
owner,1,10) as schema, SUBSTR(source_name,1,10) as name FROM asn.
ibmqrep_subs"
SUBSGROUP SCHEMA NAME
------ ------- ------
TABT2 DB2ADMIN DEPT
TABT2 DB2ADMIN XEMP
The preceding ASNCLP command generates the following SQL:
-- CONNECT TO DB2B USER XXXX using XXXX;
DELETE FROM ASN.IBMQREP_TRG_COLS WHERE subname = 'T10001' AND
recvq =
'CAPA.TO.APPB.RECVQ';
DELETE FROM ASN.IBMQREP_TARGETS WHERE subname = 'T10001' AND recvq
=
'CAPA.TO.APPB.RECVQ';
DELETE FROM ASN.IBMQREP_SRC_COLS WHERE subname = 'T10002';
DELETE FROM ASN.IBMQREP_SUBS WHERE subname = 'T10002';
-- CONNECT TO DB2A USER XXXX using XXXX;
DELETE FROM ASN.IBMQREP_SRC_COLS WHERE subname = 'T10001';
DELETE FROM ASN.IBMQREP_SUBS WHERE subname = 'T10001';
DELETE FROM ASN.IBMQREP_TRG_COLS WHERE subname = 'T10002' AND
recvq =
'CAPB.TO.APPA.RECVQ';
DELETE FROM ASN.IBMQREP_TARGETS WHERE subname = 'T10002' AND recvq
=
'CAPB.TO.APPA.RECVQ';
A third method uses the DROP SUBGROUP command, as shown:
SET SUBGROUP "TABT2";
SET SERVER MULTIDIR TO DB "DB2A";
SET SERVER MULTIDIR TO DB "DB2B";
SET MULTIDIR SCHEMA "DB2A".ASN ;
DROP SUBGROUP;
With this command, we just need to specify the Q subscription group name (SUBGROUP).
The preceding ASNCLP command generates the following SQL:
-- CONNECT TO DB2A USER XXXX using XXXX;
DELETE FROM ASN.IBMQREP_TRG_COLS WHERE subname = 'T10002' AND
recvq =
'CAPB.TO.APPA.RECVQ';
DELETE FROM ASN.IBMQREP_TARGETS WHERE subname = 'T10002' AND recvq
=
'CAPB.TO.APPA.RECVQ';
DELETE FROM ASN.IBMQREP_SRC_COLS WHERE subname = 'T10001';
DELETE FROM ASN.IBMQREP_SUBS WHERE subname = 'T10001';
-- CONNECT TO DB2B USER XXXX using XXXX;
DELETE FROM ASN.IBMQREP_SRC_COLS WHERE subname = 'T10002';
DELETE FROM ASN.IBMQREP_SUBS WHERE subname = 'T10002';
DELETE FROM ASN.IBMQREP_TRG_COLS WHERE subname = 'T10001' AND
recvq =
'CAPA.TO.APPB.RECVQ';
DELETE FROM ASN.IBMQREP_TARGETS WHERE subname = 'T10001' AND recvq
=
'CAPA.TO.APPB.RECVQ';
In a peer-to-peer three-way scenario, we would add a third SET SERVER MULTIDIR TO DB line pointing to the third server.
If we use the second or third method, then we do not need to know the Q subscription names, just the table name in the second method and the Q subscription group name in the third method.
Altering a Q subscription
We can only alter Q subscriptions which are inactive. The following query shows the state of all Q subscriptions:
db2 "SELECT SUBSTR(subname,1,10) AS subname, state FROM asn.ibmqrep_subs"
SUBNAME STATE
---------- -----
DEPT0001 I
At the time of writing, if we try and alter an active Q subscription, we will get the following error when we run the ASNCLP commands:
ErrorReport :
ASN2003I The action "Alter Subscription" started at "Friday, 22
January 2010 12:53:16 o'clock GMT". Q subscription name: "DEPT0001".
Q Capture server: "DB2A". Q Capture schema: "ASN". Q Apply server:
"DB2B". Q Apply schema: "ASN". The source table is "DB2ADMIN.DEPT".
The target table or stored procedure is "DB2ADMIN.DEPT".
ASN0999E "The attribute "erroraction" cannot be updated." : "The
Subscription cannot be updated because it is in active state" : Error
condition "*", error code(s): "*", "*", "*".
This should be resolved in a future release.
So now let's move on and look at the command to alter a Q subscription.
To alter a Q subscription, we use the ALTER QSUB ASNCLP command.
The parameters for the command depend on whether we are running unidirectional or multi-directional replication. We can change attributes for both the source and target tables, but what we can change depends on the type of replication (unidirectional, bidirectional, or peer-to-peer), as shown in the following table:
ParameterUniBiP2PSource table:ALL CHANGED ROWS [N | Y]YY HAS LOAD PHASE [N | I |E]YYYTarget table:CONFLICT RULE [K | C | A] Y CONFLICT ACTION [I | F | D | S | Q] Y ERROR ACTION [Q | D | S]YYYLOAD TYPE [0 | 2 | 3 | 4 | 104 | 5 | 105]YYYOKSQLSTATES ["sqlstates"]YYY
For unidirectional replication, the format of the command is:
ALTER QSUB <subname> REPLQMAP <mapname>
USING REPLQMAP <mapname> DESC <description>
MANAGE TARGET CCD [CREATE SQL REGISTRATION|DROP SQL REGISTRATION|ALTER
SQL REGISTRATION FOR Q REPLICATION]
USING OPTIONS [other-opt-clause|add-cols-clause]
other-opt-clause:
SEARCH CONDITION "<search_condition>"
ALL CHANGED ROWS [N|Y]
HAS LOAD PHASE-- [N|I|E]
SUPPRESS DELETES [N|Y]
CONFLICT ACTION [I|F|D|S|Q]
ERROR ACTION [S|D|Q]
OKSQLSTATES "<sqlstates>"
LOAD TYPE [0|1|2|3|4|104|5|105]
add-cols-clause:
ADD COLS (<trgcolname1> <srccolname1>,<trgcolname2> <srccolname2>)
An example of altering a Q subscription to add a search condition is:
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2B;
ALTER QSUB tab1 REPLQMAP rqma2b
USING OPTIONS
SEARCH CONDITION
"WHERE :c1 > 1000" ;
In multi-directional replication, the format of the command is:
ALTER QSUB SUBTYPE B
FROM NODE <svn.schema> SOURCE [src-clause] TARGET [trg-clause]
FROM NODE <svn.schema> SOURCE [src-clause] TARGET [trg-clause]
src-clause:
ALL CHANGED ROWS [N/Y] HAS LOAD PHASE [N/I/E]
trg-clause:
CONFLICT RULE [K/C/A] +-' '-CONFLICT ACTION [I/F/D/S/Q]
ERROR ACTION [Q/D/S] LOAD TYPE [0/2/3]
OKSQLSTATES <"sqlstates">
If we are altering a Q subscription in a multi-directional environment, then we can use the SET REFERENCE TABLE construct. We need to specify the SUBTYPE parameter as follows:
Bidirectional replication: ALTER QSUB SUBTYPE B
Peer-to-peer replication: ALTER QSUB SUBTYPE P
Let's look at a bidirectional replication example, where we want to change the ERROR ACTION to D for a Q subscription where the source table name is db2admin.dept. The content file (SYSA_cont_alter02.txt) will contain:
SET SUBGROUP "TABT2";
SET SERVER MULTIDIR TO DB "DB2A";
SET SERVER MULTIDIR TO DB "DB2B";
SET REFERENCE TABLE USING SCHEMA "DB2A".ASN
USES TABLE db2admin.dept;
ALTER QSUB SUBTYPE B
FROM NODE DB2A.ASN SOURCE TARGET ERROR ACTION D
FROM NODE DB2B.ASN SOURCE TARGET ERROR ACTION D;
We have to specify the SOURCE keyword even though we are only changing the target attributes.
The ALTER QSUB statement spans the three last lines of the file.
Starting a Q subscription
An example of the ASNCLP command START QSUB to start a Q subscription can be found in the SYSA_qsub_start_db2ac.asnclp file. We just have to plug in the Q subscription name (T10002 in our example).
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2C;
START QSUB SUBNAME T10002;
Run the file as:
asnclp -f SYSA_qsub_start_db2ac.asnclp
We cannot put two START QSUB statements in the same file (as shown), even if they have their own section.
So, we cannot code:
ASNCLP SESSION SET TO Q REPLICATION;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2D;
SET CAPTURE SCHEMA SOURCE ASN;
SET APPLY SCHEMA ASN;
START QSUB SUBNAME T10003;
SET SERVER CAPTURE TO DB DB2A;
SET SERVER TARGET TO DB DB2C;
START QSUB SUBNAME T10002;
Sending a signal using ASNCLP
For signals such as CAPSTART, CAPSTOP, and LOADDONE to be picked up, Q Capture needs to be running. Note that Q Capture does not have to be up for the signals to be issued, just picked up. As they are written to the DB2 log, Q Capture will see them when it reads the log and will action them in the order they were received.
Summary
In this article we took a look at how we can stop or drop or alter a Q subscription using ASNCLP commands and how we can issue a CAPSTART command.
Further resources on this subject:
Lotus Notes Domino 8: Upgrader's Guide [Book]
Q Replication Components in IBM Replication Server [Article]
IBM WebSphere MQ commands [Article]
WebSphere MQ Sample Programs [Article]
MQ Listener, Channel and Queue Management [Article]
Read more