Q Subscription Maintenance in IBM Infosphere

Exclusive offer: get 50% off this eBook here
IBM InfoSphere Replication Server and Data Event Publisher

IBM InfoSphere Replication Server and Data Event Publisher — Save 50%

Design, implement, and monitor a successful Q replication and Event Publishing project with IBM InfoSphere Replication Server and Data Event Publisher using this book and eBook

$35.99    $18.00
by Pav Kumar-Chatterjee | November 2010 | Enterprise Articles IBM

In this article by Pav Kumar-Chatterjee, author of IBM InfoSphere Replication Server and Data Event Publisher, we will cover the following topics:

  • Checking the state of a Q subscription
  • Stop, drop, alter or start a Q subscription
  • Sending a signal using ASNCLP

 

IBM InfoSphere Replication Server and Data Event Publisher

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

 

Appendix 

        Read more about this book      

(For more resources on IBM, see here.)

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:

Parameter Uni Bi P2P
Source table:
ALL CHANGED ROWS [N | Y] Y Y  
HAS LOAD PHASE [N | I |E] Y Y Y
Target table:
CONFLICT RULE [K | C | A]   Y  
CONFLICT ACTION [I | F | D | S | Q]   Y  
ERROR ACTION [Q | D | S] Y Y Y
LOAD TYPE [0 | 2 | 3 | 4 | 104 | 5 | 105] Y Y Y
OKSQLSTATES ["sqlstates"] Y Y Y

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:


IBM InfoSphere Replication Server and Data Event Publisher Design, implement, and monitor a successful Q replication and Event Publishing project with IBM InfoSphere Replication Server and Data Event Publisher using this book and eBook
Published: August 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Pav Kumar-Chatterjee

Pav Kumar-Chatterjee (Eur Ing, CENG, MBCS) has been involved in DB2 support on the mainframe platform since 1991, and on midrange platforms since 2000. Before joining IBM he worked as a database administrator in the airline industry as well as various financial institutions in the UK and Europe. He has held various positions during his time at IBM, including in the Software Business Services team and the global BetaWorks organization. His current position is a DB2 technical specialist in the Software Business. He was involved with Information Integrator (the forerunner of Replication Server) since its inception, and has helped numerous customers design and implement Q replication solutions, as well as speaking about Q replication at various conferences.
Pav Kumar-Chatterjee has co-authored the “DB2 pureXML Cookbook” (978-0-13-815047-1) published in August 2009.

Books From Packt


IBM Lotus Notes 8.5 User Guide
IBM Lotus Notes 8.5 User Guide

Application Development for IBM WebSphere Process Server 7 and Enterprise Service Bus 7
Application Development for IBM WebSphere Process Server 7 and Enterprise Service Bus 7

IBM Cognos 8 Report Studio Cookbook
IBM Cognos 8 Report Studio Cookbook

IBM Lotus Notes and Domino 8.5.1
IBM Lotus Notes and Domino 8.5.1

IBM WebSphere eXtreme Scale 6
IBM WebSphere eXtreme Scale 6

WebSphere Application Server 7.0 Administration Guide
WebSphere Application Server 7.0 Administration Guide

IBM Cognos 8 Planning
IBM Cognos 8 Planning

Domino 7 Lotus Notes Application Development
Domino 7 Lotus Notes Application Development


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
c
N
2
g
h
S
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software