Stop Q Replication in a Right Way

Everyone must understand the importance of proper way to stop Q replication.
I bet you that your setup will not work properly if you don't do that right way, after many tests personally i prefer the below way to my stop my replication safely.

steps:

A) stop the capture and apply programs that are running

asnqccmd capture_server=TESTA capture_schema=ASNUA
asnqacmd apply_server=TESTB apply_schema=ASNUB

B) stop the listener and channels

We need to login into queue manager to stop the listener and channels

db2inst1@sairam:~/setup$ runmqsc QMUA
5724-H72 (C) Copyright IBM Corp. 1994, 2011.  ALL RIGHTS RESERVED.
Starting MQSC for queue manager QMUA.
stop listener(REPL_LSTR)
     1 : stop listener(REPL_LSTR)
AMQ8706: Request to stop WebSphere MQ Listener accepted.
stop channel(QMUA_TO_QMUB)
     2 : stop channel(QMUA_TO_QMUB)
AMQ8019: Stop WebSphere MQ channel accepted.
stop channel(QMUB_TO_QMUA)
     3 : stop channel(QMUB_TO_QMUA)
AMQ8019: Stop WebSphere MQ channel accepted.
end
     4 : end
4 MQSC commands read.
No commands have a syntax error.
All valid MQSC commands were processed.

db2inst1@sairam:~/setup$ runmqsc QMUB
5724-H72 (C) Copyright IBM Corp. 1994, 2011.  ALL RIGHTS RESERVED.
Starting MQSC for queue manager QMUB.
stop listener(REPL_LSTR)
     1 : stop listener(REPL_LSTR)
AMQ8706: Request to stop WebSphere MQ Listener accepted.
stop channel(QMUA_TO_QMUB)
     2 : stop channel(QMUA_TO_QMUB)
AMQ9533: Channel 'QMUA_TO_QMUB' is not currently active.
stop channel(QMUB_TO_QMUA)
     3 : stop channel(QMUB_TO_QMUA)
AMQ8019: Stop WebSphere MQ channel accepted.
end
     4 : end
4 MQSC commands read.
No commands have a syntax error.
All valid MQSC commands were processed.

C) stop the queue managers

db2inst1@sairam:~/setup$ endmqm QMUA
Quiesce request accepted. The queue manager will stop when all outstanding work
is complete.
db2inst1@sairam:~/setup$ endmqm QMUB
Quiesce request accepted. The queue manager will stop when all outstanding work
is complete.

Thinking complicated ? I do this every time and everyday but i prepared a shell script to do all these. Same way i have a script to start the replication...  feeling easy :)

########## START ###############
#input file(inputs) for the stop script(stop_qm)
stop listener(REPL_LSTR)
stop channel(QMUA_TO_QMUB)
stop channel(QMUB_TO_QMUA)
######### END #################

########## START #############################
#Put below lines a script and execute it from normal command line as a shell script
#to stop capture and apply programs
asnqccmd capture_server=TESTA capture_schema=ASNUA
asnqacmd apply_server=TESTB apply_schema=ASNUB
#allow capture and apply programs to go down properly and execute next command i will SLEEP FOR 10 secs
sleep 10
#to stop listener and channels use a input file(inputs) to the queue manager
runmqsc QMUA < $1
runmqsc QMUB < $1
#stop queue managers
endmqm QMUA
endmqm QMUB
########## END ###############################


Now let us execute the script

sh stop_qm inputs

Quick setup of Unidirectional Q Replication between two DB2 databases

Its been many days that i blogged, and today I have enough time to write on DB2 Unidirectional Q Replication between two DB2 databases.

Q Replication is high availability concept in DB2 , used to replicate data from one database to another database.Today i will show a practical demonstration on how to setup DB2 Unidirectional replication between 2 DB2 databases. Unidirectional replication is nothing but replicating data only in ONE direction. High level picture of my setup is as below.

Required:

Test box installed with DB2 V9.7 and Websphere MQ V7.1 and 2 databases created TESTA, TESTB

High level steps:

1) Create necessary Websphere MQ objects to setup unidirectional replication

2) Create control tables needed for capture and apply servers

3) Create Replication Queue map

4) Create a subscription and start capture and apply programs and populate the data to observe replication setup

Create Websphere MQ objects : We need certain objects to enable us to setup replication in unidirectional between two databases, check the script below and make necessary changes according the machine details

ASNCLP SESSION SET TO Q REPLICATION;

CREATE MQ SCRIPT CONFIG TYPE U

MQSERVER 1 NAME TESTA MQHOST "sairam"  MQPORT 2510 QMANAGER QMUA QNAME_QUAL ASNUA,

MQSERVER 2 NAME TESTB MQHOST "sairam" MQPORT 2511 QMANAGER QMUB QNAME_QUAL ASNUB;

Put in a file and run it with asnclp command line as below it will create 2 batch files and 2 script files we shall use scripts as were are in linux environment, grant execute permission for the scripts and execute them

asnclp -f create_mqobjects

db2inst1@sairam:~/setup$ ls -lrt

-rw-rw-r-- 1 db2inst1 db2inst1 3931 Jul 28 13:17 qrepl.testa.mq_windows.bat

-rw-rw-r-- 1 db2inst1 db2inst1 3922 Jul 28 13:17 qrepl.testa.mq_aixlinux.sh

-rw-rw-r-- 1 db2inst1 db2inst1 3976 Jul 28 13:17 qrepl.testb.mq_windows.bat

-rw-rw-r-- 1 db2inst1 db2inst1 3967 Jul 28 13:17 qrepl.testb.mq_aixlinux.sh

chmod 755 qrepl.testa.mq_aixlinux.sh qrepl.testb.mq_aixlinux.sh

db2inst1@sairam:~/setup$ ./qrepl.testa.mq_aixlinux.sh 

There are 19 days left in the trial period for this copy of WebSphere MQ.

WebSphere MQ queue manager created.

Directory '/var/mqm/qmgrs/QMUA' created.

The queue manager is associated with installation 'Installation1'.

Creating or replacing default objects for queue manager 'QMUA'.

Default objects statistics : 71 created. 0 replaced. 0 failed.

Completing setup.

Setup completed.

db2inst1@sairam:~/setup$ ./qrepl.testb.mq_aixlinux.sh 

There are 19 days left in the trial period for this copy of WebSphere MQ.

WebSphere MQ queue manager created.

Directory '/var/mqm/qmgrs/QMUB' created.

The queue manager is associated with installation 'Installation1'.

Creating or replacing default objects for queue manager 'QMUB'.

Default objects statistics : 71 created. 0 replaced. 0 failed.

Completing setup.

Setup completed.

By the end of this you will see two queue manager up and running, check using command

db2inst1@sairam:~/setup$ dspmq

QMNAME(QMUA)                                              STATUS(Running)

QMNAME(QMUB)                                              STATUS(Running)

Create control tables for capture and apply servers and Replication Queue Map : The control tables at the Q Capture server contain information about data sources, options for Q subscriptions or publications, operating parameters for the Q Capture program, Q Capture performance statistics, and other metadata. The control tables at the Q Apply server contain Q Apply operating parameters, Q subscription definitions, performance statistics, and other metadata. Replication Queue Map, which identifies the WebSphere MQ queues on both servers that are used to communicate between the servers.

I will add 2 and 3 steps in same script as show below to create control tables for capture and apply and replication queue map

asnclp session set to q replication;

set server capture to dbalias TESTA dbname TESTA id "db2inst1" password "db2inst1";

set server target to dbalias TESTB dbname TESTB id "db2inst1" password "db2inst1";

set capture schema source ASNUA;

set apply schema ASNUB;

set qmanager QMUA for capture schema;

set qmanager QMUB for apply schema;

set run script now stop on sql error on;

create control tables for capture server using mqdefaults;

create control tables for apply server ;

create replqmap ASNUA_TO_ASNUB using mqdefaults;

Put the commands in a file and run it with asnclp command line, please observe that all objects are created successfully, you should see a line after execute successful execution of command when you execute script

asnclp -f crt_rest

ASN1514I  The replication action ended at "Monday, 28 July, 2014 1:23:10 PM IST" with "1" successes, "0" errors, and "0" warnings.

<ClpInfo2Log:: The SQL command completed successfully.>

Create a subscription and start capture and apply programs and populate the data to observe replication setup:

create a table customer in db2inst1 schema in both database before hand and Use the below script that creates a unidirectional subscription for table DB2INST1.CUSTOMER

ASNCLP SESSION SET TO Q REPLICATION;

set server capture to dbalias TESTA dbname TESTA id "db2inst1" password "db2inst1";

set server target to dbalias TESTB dbname TESTB id "db2inst1" password "db2inst1";

set capture schema source ASNUA;

set apply schema ASNUB;

set qmanager QMUA for capture schema;

set qmanager QMUB for apply schema;

set run script later;

set output capture script newsubcap;

set output target script newsubapp;

create qsub subtype U using replqmap ASNUA_TO_ASNUB ( SUBNAME CUSTOMER-UNI DB2INST1.CUSTOMER EXIST TARGET NAME DB2INST1.CUSTOMER);  

Put above lines into a files and run it with asnclp command line to create one subscription

asnclp -f sub_customer

Now lets start the capture program and apply program:

nohup asnqcap capture_server=TESTA capture_schema=ASNUA &

nohup asnqapp apply_server=TESTB apply_schema=ASNUB &

Connect to source database TESTA and populate one row and check it on target database TESTB

db2inst1@sairam:~/setup$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9

 SQL authorization ID   = DB2INST1

 Local database alias   = TESTB

db2inst1@sairam:~/setup$ db2 "insert into customer values(1,'Murali','bhatiagardens')"

DB20000I  The SQL command completed successfully.

db2inst1@sairam:~/setup$ db2 "select * from customer"

ID         CUSTNAME             CUSTADD             

---------- -------------------- --------------------

1          Murali               bhatiagardens       

  1 record(s) selected.

db2inst1@sairam:~/setup$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9

 SQL authorization ID   = DB2INST1

 Local database alias   = TESTB

db2inst1@sairam:~/setup$ db2 "select * from customer"

ID         CUSTNAME             CUSTADD             

---------- -------------------- --------------------

1          Murali               bhatiagardens       

  1 record(s) selected.

We shall do a reverse test that is populate a row on target side and observe it don't replicate to source database. :p

db2inst1@sairam:~/setup$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9

 SQL authorization ID   = DB2INST1

 Local database alias   = TESTB

db2inst1@sairam:~/setup$ db2 "insert into customer values(2,'Rupesh','hyderabad')"

DB20000I  The SQL command completed successfully.

db2inst1@sairam:~/setup$ db2 "select * from customer"

ID         CUSTNAME             CUSTADD             

---------- -------------------- --------------------

1          Murali               bhatiagardens       

2          Rupesh               hyderabad           

  2 record(s) selected.

db2inst1@sairam:~/setup$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9

 SQL authorization ID   = DB2INST1

 Local database alias   = TESTA

db2inst1@sairam:~/setup$ db2 "select * from customer"

ID         CUSTNAME             CUSTADD             

---------- -------------------- --------------------

1          Murali               bhatiagardens       

  1 record(s) selected.

So that's it for the day and by the end of the post we have clearly defined source and target and created a setup and populated the data to show its working as expected. Readers please post your comments and doubts and any suggestions. I'm Happy to help you anytime. Good BYE

Please feel to write to me saimurali619@gmail.com for any queries and help.

Changing the data type of a column in replicated table in DB2 Q Replication

db2inst1@sairam:~$ db2 describe table task

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TASK_ID                         SYSIBM    INTEGER                      4     0 No    
TASK_INFO                       SYSIBM    VARCHAR                     10     0 Yes   
SALARY                          SYSIBM    VARCHAR                     10     0 Yes   

  3 record(s) selected.

We want to change SALARY column datatype from varchar(10) to char(20) 

Steps to change the data type:

1) Stop the subscription by passing capstop signal
2) stop the data capture changes to none on both databases
3) alter the column with the new data type on both sides
4) start the data capture changes to yes on both databaes
5) Start the subscription by passing capstart signal

1) db2inst1@sairam:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@sairam:~$ db2 "insert into ASNTB.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTOP','TASK0002')"
DB20000I  The SQL command completed successfully.

2) db2inst1@sairam:~$ db2 alter table task data capture none      - ( on both databases )
DB20000I  The SQL command completed successfully.

3) db2inst1@sairam:~$ db2 "alter table task alter column task_info set data type char(20)"     - ( on both databases )
DB20000I  The SQL command completed successfully.

4) db2inst1@sairam:~$ db2 alter table task data capture changes
DB20000I  The SQL command completed successfully.

5) db2inst1@sairam:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTART','TASK0001')"   - ( on one side )
DB20000I  The SQL command completed successfully.

Resolving conflicts due to particular sqlstate in DB2 Q Replication

I had a situation when i tried to insert a row in source which was detected as conflict at target so Q Apply program stopped processing, so now if we want that transaction to be ignored we need to tell q apply program to ignore it .

2014-06-08-12.47.57.547884 ASN0552E  "Q Apply" : "ASNTA" : "BR00000AG012" : The program encountered an SQL error. The server name is "". The SQL request is "EXEC". The table name is "run SQL". The SQLCODE is "-668". The SQLSTATE is "57016". The SQLERRMC is "". The SQLERRP is "SQLDTBLR".
2014-06-08-12.52.03.789464 <brwzMain> ASN7524E  "Q Apply" : "ASNTA" : "BR00000" : The Q Apply program encountered an error or conflict for Q subscription  "STAFF0002" (receive queue  "ASNTB.TESTB_TO_ASNTA.TESTA.DATA", replication queue map "TESTB.ASNTB_TO_TESTA.ASNTA"). It stopped reading from the receive queue. The error occurred while applying a row from the transaction corresponding to the LSN "0000:0000:0000:04d6:82d3".

Below are steps the pass to ibmqrep_targets to ignore that transaction for particular sql state.

db2inst1@sairam:~$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

db2inst1@sairam:~$ db2 "update asnta.ibmqrep_targets set oksqlstates='57016' where subname='STAFF0002'"
DB20000I  The SQL command completed successfully.

Then it had successfully ignored that transaction alone and q apply started processing the Receive queue.

Resetting Q subscriptions that are in inconsistent states in DB2 Q Replication

Q subscriptions are considered to have inconsistent states when the state (active, inactive, new) is different between the source and target servers. In such cases we can't activate the subscriptions without bringing them into same state(inactive, new). I was able to propagate a situation where there is a table "STAFF" for which the state between source and targets was different. 

Below are the subnames and their state from both databases we can observe the state as G for subname STAFF0001 in TESTA database, where as state as I for subname STAFF0002 in TESTB database 

db2inst1@sairam:~$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001                                                                                                                              A    
STAFF0001                                                                                                                            G    

  2 record(s) selected.

db2inst1@sairam:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002                                                                                                                              A    
STAFF0002                                                                                                                            I    

  2 record(s) selected.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Steps to reset the incosistent states in Q Replication

1) Stop capture and apply processes this is important before you update the state of subs and targets

2) UPDATE ASNTA.IBMQREP_SUBS SET STATE='I', STATE_TRANSITION=NULL WHERE SUBNAME='STAFF0001';
UPDATE ASNTA.IBMQREP_TARGETS SET STATE='I' WHERE SUBNAME='STAFF0002';

3) Start the capture and apply process now. We have state as INACTIVE in both sides so lets activate the subscription in the next step

4) INSERT INTO ASNTA.IBMQREP_SIGNAL (SIGNAL_TYPE, SIGNAL_SUBTYPE, SIGNAL_INPUT_IN) VALUES ('CMD','CAPSTART','STAFF0001');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

db2inst1@sairam:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002                                                                                                                              A  
STAFF0002                                                                                                                            A  

  2 record(s) selected.

db2inst1@sairam:~$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001                                                                                                                              A  
STAFF0001                                                                                                                            A  

  2 record(s) selected.

Using ASNTDIFF and ASNTREP utilities to sync the data between source and target tables in DB2 Q replication

When you observe that there are some data differences between source and target tables in DB2 Q replication, we can use these 2 utilities( asntdiff, asntrep ) which can compare the source and target and repair them based on our requirement to synchronize the data.

ASNTDIFF : asntdiff utility which can compare two relational tables and generate a list of differences between the two.

ASNTREP  : asntrep utility will syncronize a source and target table by repairing differences between the two tables.

Important note : Its always good to synchronize the tables when there is less amount of activity or no activity going at source so that we can have perfectly synchronized table.    

Below tutorial demonstrates the use of asntdiff to compare the source and target and asntrep to repair the target table based on differences generated by asntdiff. Below is the data populated on source and target without replication enabled. Then i have created the subscription for the table AUCTION with target having no load option so that it wont try to reload the data using source as we are in process syncing the tables

1) In this first step i have created a table with same definition in source and target and loaded data with some differences in both of them

db2inst1@murali:~$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

db2inst1@murali:~$ db2 "select * from auction"

ITEMID     CATEGORY   PRICE    
---------- ---------- -----------
A0001      HOME                54
A0002      GENERAL             17
A0003      SPARES             120
A0004      GENERAL             32
A0006      GENERAL             40

  5 record(s) selected.

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@murali:~$ db2 "select * from auction"

ITEMID     CATEGORY   PRICE    
---------- ---------- -----------
A0001      HOME                15
A0002      HOME                12
A0003      HOME                35
A0006      HOME                50

  4 record(s) selected.

2) I'm here considering TESTA as source database/correct data and TESTB is having wrong data which i want to synchronize. Let us run the asntdiff utility to find the differences between the source and target tables. As showing below in the ending line we can see that it has given us the number of differences found between the source and target table: "5". These details can be found in database "TESTA", and it has stored the diff values in the difference table DIFFSCHEMA1.DIFF_AUCTION

db2inst1@murali:~$ asntdiff DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION  where="SUBNAME = 'AUCTION0001'"

2014-05-17-17.12.21.904722 ASN0600I  "AsnTDiff" : "" : "Initial" : Program "asntdiff 9.7.9" is starting.
2014-05-17-17.12.22.005751 ASN4012I  "AsnTDiff" : "ASNTA" : "Initial" : The program is comparing tables using the list of parameters following this message.
 TDIFF   TABLE = "DIFFSCHEMA1"."DIFF_AUCTION" ;
 MAXIMUM COUNT = 100000 ;

  CONNECT TO TESTA ;

  SELECT "CATEGORY" AS "CATEGORY",    "PRICE" AS "PRICE",    "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION"  ORDER BY 3 ;

  CONNECT TO TESTB ;

  SELECT "CATEGORY",    "PRICE",    "ITEMID" FROM "DB2INST1"."AUCTION"  ORDER BY 3 ;

  CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION" (
    "DIFF "         CHAR(4),
    "ITEMID"           VARCHAR(10)
  ) ;

2014-05-17-17.12.22.416439 ASN4006I  "AsnTDiff" : "ASNTA" : "Initial" : Between the source table and the target table, there are "0" common rows, "5" rows that are unique to the source table, and "4" rows that are unique to the target table.
2014-05-17-17.12.22.438578 ASN4010I  "AsnTDiff" : "ASNTA" : "Initial" : Number of differences found between the source and target table: "5". The details can be found in database "TESTA", difference table ""DIFFSCHEMA1"."DIFF_AUCTION"".
----------------------------------------------------------------------

3) Let us see what differences it has identified between source and target and have stored in the difference table. As show below we can see it contains 4 UPDATES and 1 INSERT

db2inst1@murali:~$ db2 "select * from "DIFFSCHEMA1"."DIFF_AUCTION""

DIFF  ITEMID  
----- ----------
U  2  A0001  
U  2  A0002  
U  2  A0003  
U  2  A0006  
I  2  A0004  

  5 record(s) selected.

4) Now to repair the table at target we are running asntrep using the same difference table, that was created in earlier step. So it should perform 4 updates and 1 insert in target side. As shown in the ending line we can see The program applied the rows from the difference table to the target table as follows: "1" rows were inserted, "4" rows were updated, and "0" rows were deleted.

db2inst1@murali:~$ asntrep DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION WHERE="SUBNAME = 'AUCTION0001'"

2014-05-17-17.13.49.984558 ASN0600I  "AsnTRep" : "" : "Initial" : Program "asntrep 9.7.9" is starting.

CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION-" (
    "DIFF "            CHAR(4),
    "ITEMID"           VARCHAR(10)
)  ;

DELETE
FROM   "DB2INST1"."AUCTION" T
WHERE  EXISTS (

   SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION-" D

   WHERE  ( T."ITEMID" = D."ITEMID"  OR
            T."ITEMID" IS NULL AND D."ITEMID" IS NULL )
   AND      D."DIFF " LIKE 'D%' ) ;



INSERT INTO "DB2INST1"."AUCTION" (
   "CATEGORY",
   "PRICE",
   "ITEMID" ) VALUES(  ?,  ?,  ? ) ;


SELECT T.*
FROM ( SELECT "CATEGORY" AS "CATEGORY",
   "PRICE" AS "PRICE",
   "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION"
) AS T

WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D
   WHERE  ( T."ITEMID" = D."ITEMID"  OR
            T."ITEMID" IS NULL AND D."ITEMID" IS NULL )
   AND      D."DIFF " LIKE 'I%' ) FOR READ ONLY ;

UPDATE "DB2INST1"."AUCTION"
SET
 "CATEGORY" = ?,
 "PRICE" = ?
WHERE
 "ITEMID" = ?

   SELECT T.*  FROM ( SELECT "CATEGORY" AS "CATEGORY",    "PRICE" AS "PRICE",    "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION"  ) AS T    WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D     WHERE  ( T."ITEMID" = D."ITEMID"  OR             T."ITEMID" IS NULL AND D."ITEMID" IS NULL )    AND      D."DIFF " LIKE 'U%' ) FOR READ ONLY ;

2014-05-17-17.13.50.321421 ASN4019I  "AsnTRep" : "ASNTA" : "Initial" : The program applied the rows from the difference table to the target table as follows: "1" rows were inserted, "4" rows were updated, and "0" rows were deleted.
----------------------------------------------------------------------

5) Now lets check the data in target to see if it really worked.

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@murali:~$ db2 "select * from auction"

ITEMID     CATEGORY   PRICE    
---------- ---------- -----------
A0001      HOME                54
A0002      GENERAL             17
A0003      SPARES             120
A0006      GENERAL             40
A0004      GENERAL             32

  5 record(s) selected.

NOTE:

From my experience when we need to synchronize big tables we need to specify the conditions on which asntdiff and asntrep should run, else it might take a lot of time to find the differences between source and target, also sometimes it also fails. Its always important to understand the business logic and use these utilities with utmost care to make sure we moving in right direction.

Adding a new column to replicated table in DB2 Q Replication

This tutorial will help you understand on how to add a new column to table which is already replicated, This tutorial applies for Q Replication with bidirectional setup ( active - active solution ) that i was using in my earlier posts. Description of table is below to which we shall add one new column, name it as ADDRESS VARCHAR(10)

db2inst1@murali:~$ db2 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  

  2 record(s) selected.


Basic steps are like this

1) Check the subscription of the table on source and target and see if it active before we add a new column
2) Add the column with normal alter table add column command to the source table
3) Pass the signal to replication so it can understand that we have added a new column to table
4) Check on the target side if the column is added or not

1)

db2inst1@murali:~$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

db2inst1@murali:~$ db2 "select subname,state from asnta.ibmqrep_subs where source_name='DBA'"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001                                                                                                                              A  

  1 record(s) selected.

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@murali:~$ db2 "select subname,state from asntb.ibmqrep_subs where source_name='DBA'"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002                                                                                                                              A  

  1 record(s) selected.


2)

db2inst1@murali:~$ db2 "alter table dba add column address varchar(10)"
DB20000I  The SQL command completed successfully.

3)

db2inst1@murali:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL(SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN,SIGNAL_STATE) values ( CURRENT TIMESTAMP,'CMD','ADDCOL','DBA0001;ADDRESS','P' )"
DB20000I  The SQL command completed successfully.

4)

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@murali:~$ db2 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  
ADDRESS                         SYSIBM    VARCHAR                     10     0 Yes

  3 record(s) selected.

All the commands used in this post are highlighted in ITALIC

Q capture program stops giving Websphere MQ error 2058 ASN0575E

Problem statement: Websphere MQ error 2058 ASN0575E while issuing the WebSphere MQ command "MQCONN" on object "*"

Solution: The above error is very common error for most of the users when a Queue manager tries to access a queue or when it tries to issue MQCONN call on the queues which are not belonging to it then it gives us an error.

ASNCLP SESSION SET TO Q REPLICATION;
CREATE MQ SCRIPT CONFIG TYPE B
MQSERVER 1 NAME TESTA MQHOST "fedorabox" MQPORT 2416 QMANAGER QMTA QNAME_QUAL ASNTA, 
MQSERVER 2 NAME TESTB MQHOST "fedorabox" MQPORT 2417 QMANAGER QMTB QNAME_QUAL ASNTB;
=======================================================================
In the above setup following are the configurations.

Database 1 : TESTA
Database 2 : TESTB
QManager name for TESTA database is: QMTA
QManager name for TESTB database is: QMTB
Schema that we are using for TESTA database is ASNTA under which control tables, replication queue maps and all replication related objects are created in this schema.
Schema that we are using for TESTB database is ASNTB under which control tables, replication queue maps and all replication related objects are created in this schema.

========================================================================
ASNCLP SESSION SET TO Q REPLICATION;
SET OUTPUT MULTIDIR;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;
SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;
CREATE CONTROL TABLES FOR NODE 1;
CREATE CONTROL TABLES FOR NODE 2;
CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);
CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);

Above script defines control tables for NODE1 and NODE2 and replication queue maps for NODE1 and NODE2
========================================================================

1) When i run the first set of commands using asnclp it will give scripts for BIDIRECTIONAL replication between two databases TESTA and TESTB and setup Queue managers QMTA for TESTA, QMTB for TESTB and sets replication schema ASNTA for TESTA and ASNTB for TESTB

2) When i run the second set of commands using asnclp it will give script for setting up control tables and replication queue maps between both the database viceversa

Everything ran successfully Queue managers QMTA and QMTB are up and running and all necessary objects are created. But when i start the Q capture program and when it tries to issue MQCONN call it wasnt able to identify the queues and gave me MQ error 2058, because in the above script i didnt specify queue manager name for NODE1 and NODE2.

SET QMANAGER QMTA FOR NODE 1;
SET QMANAGER QMTB FOR NODE 2;

When i added above two lines to second script that is setting the which qmanager to which node, then all the objects are created with the right Queue managers. Before running this script again i have dropped the schema ASNTA and ASNTB from both sides.

ASNCLP SESSION SET TO Q REPLICATION;
SET OUTPUT MULTIDIR;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;
SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;
SET QMANAGER QMTA FOR NODE 1;
SET QMANAGER QMTB FOR NODE 2;
CREATE CONTROL TABLES FOR NODE 1;
CREATE CONTROL TABLES FOR NODE 2;
CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);
CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);

Then i was able to start the capture program and apply program and was able to successfully start the replication. Below link has helped me diagnosing the error
http://www-01.ibm.com/support/docview.wss?uid=swg21166938

DB2 Q Replication setup for an active active solution PART 2

I'm back after my 5 day continuous night shifts.. Its Saturday today fresh day to start with PART 2 to quick setup of DB2 Q Replication for active active solution

We have installed DB2 on Fedora box in PART 1 , now let us install Websphere MQ on top of it.

Installation of Websphere MQ 7.1 :

1) Untar the installation files
tar -xvf WMQ_7.1.0.3_TRIAL_LNX_X86_64_ML.tar.gz

2) we need to accept the license before proceeding for installation of MQ
sudo ./mqlicense.sh -accept

3) Now lets install Websphere MQ using below command
rpm -ivh MQSeries*

4) After MQ installed we need to set the current installation as primary for that we need to run setmqinst command from installation path like below.
sudo ./mqsetinst -i -p /opt/mqm
After it executes we should see this messages like below.

[db2inst1@fedorabox bin]$ sudo ./setmqinst -i -p /opt/mqm/
114 of 114 tasks have been completed successfuly.

'Installation1' (/opt/mqm) set as the Primary Installation.

5) Run command dspmq to see installed version of MQ

[db2inst1@fedorabox bin]$ dspmqver

Name:        WebSphere MQ
Version:     7.1.0.3
Level:       p710-003-130724
BuildType:   IKAP - (Production)
Platform:    WebSphere MQ for Linux (x86-64 platform)
Mode:        64-bit
O/S:         Linux 3.6.10-4.fc18.x86_64
InstName:    Installation1
InstDesc:    
InstPath:    /opt/mqm
DataPath:    /var/mqm
Primary:     Yes
MaxCmdLevel: 711

If this MQ installation is not first time on your machine, you need to run crtmqpkg command to create unique set of packages to install in your system. In order to do this we need to install pax before running crtmqpkg command

sudo apt-get install pax
sudo ./crtmqpkg PROD
after the packages are created a path is displayed where those are created we need to go to that directory and install the available RPM's for MQ .
cd /var/tmp/mq_rpms/PROD/

sudo crtmqpkg test  
sudo rpm -ivh MQSeries*

================= End of installation Websphere MQ ===========================

A prerequisite for using Q Replication is that databases should be enabled for archive logging. use below script that creates a database , setup archive logging, and take an offline backup . see that you create the needed directories that used for archive log location, Put it everything in a file and run it.

create database testa;

create database testb;

update db cfg for testa using LOGARCHMETH1 DISK:/home/db2inst1/archivelogs/testa;

backup database testa to /dev/null;

update db cfg for testb using LOGARCHMETH2 DISK:/home/db2inst1/archivelogs/testb;

backup database testb to /dev/null;

db2 -stvf create.sql -z create.out

Now lets create Queue Manager and it objects. Use below script

ASNCLP SESSION SET TO Q REPLICATION;

CREATE MQ SCRIPT CONFIG TYPE B

MQSERVER 1 NAME TESTA MQHOST "fedorabox" MQPORT 2416 QMANAGER QMTA QNAME_QUAL ASNTA, 

MQSERVER 2 NAME TESTB MQHOST "fedorabox" MQPORT 2417 QMANAGER QMTB QNAME_QUAL ASNTB; 

Change the "fedorabox" hostname to your machine name. 

Important points to understand here:

CREATE MQ SCRIPT command will generate scripts for creating all of the WebSphere MQ objects that are needed for Q Replication. Lets run the script using asnclp command line, Once done we can see 4 files created 2 files ending with .sh is for unix/linux OS , 2 files ending with .bat is for windows OS.

[db2inst1@fedorabox scripts]$ asnclp -f create_mq_objects.asn

[db2inst1@fedorabox scripts]$ ls -lrt

total 40

-rw-rw-r--. 1 db2inst1 db2inst1  240 Apr 12 19:49 create_mq_objects.asn

-rw-rw-r--. 1 db2inst1 db2inst1 5329 Apr 12 19:50 qrepl.testa.mq_windows.bat

-rw-rw-r--. 1 db2inst1 db2inst1 5330 Apr 12 19:50 qrepl.testa.mq_aixlinux.sh

-rw-rw-r--. 1 db2inst1 db2inst1 4671 Apr 12 19:50 qrepl.testb.mq_windows.bat

-rw-rw-r--. 1 db2inst1 db2inst1 4666 Apr 12 19:50 qrepl.testb.mq_aixlinux.sh

-rw-rw-r--. 1 db2inst1 db2inst1  412 Apr 12 19:50 qreplmsg.log

Now switch to mq user and run the 2 files one for testa other for testb.

sh qrepl.testa.mq_aixlinux.sh
sh qrepl.testb.mq_aixlinux.sh

After running that we should be able to see there are 2 Queue managers that should up and running as show below.

[db2inst1@fedorabox mqm]$ dspmq
QMNAME(QMTA)                                              STATUS(Running)
QMNAME(QMTB)                                              STATUS(Running)

Lets create a table named "dba" with columns as below and run on both the databases TESTA and TESTB. 

[db2inst1@fedorabox scripts]$ db2 connect to testa
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA
[db2inst1@fedorabox scripts]$ db2 "create table dba(id num not null,name varchar(10), topic varchar(10))"
DB20000I  The SQL command completed successfully.
[db2inst1@fedorabox scripts]$ db2 "alter table dba add primary key(id)"
DB20000I  The SQL command completed successfully.

[db2inst1@fedorabox scripts]$ db2 connect to testb
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

[db2inst1@fedorabox scripts]$ db2 "create table dba(id num not null,name varchar(10), topic varchar(10))"
DB20000I  The SQL command completed successfully.
[db2inst1@fedorabox scripts]$ db2 "alter table dba add primary key(id)"
DB20000I  The SQL command completed successfully.

Now we use below script to create control tables,replication queue maps and a sample subscription for table DBA.

Important points to understand here:

create control tables statement will create control tables which store information about Q replication and its sources and targets.
create replqmap statement will create replication queue maps which are needed to identify websphere mq queues on both the servers that is source and target. One replication queue map defined from source to target and one for target to source.
create qsub statement will create a queue subscription that maps source table to target table.

ASNCLP SESSION SET TO Q REPLICATION;
SET OUTPUT MULTIDIR;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;
SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;
SET QMANAGER QMTA FOR NODE 1;
SET QMANAGER QMTB FOR NODE 2;
CREATE CONTROL TABLES FOR NODE 1;
CREATE CONTROL TABLES FOR NODE 2;
CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);
CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);
SET TABLES(NODE 1 "DBA");
CREATE QSUB SUBTYPE B FROM NODE TESTA.ASNTA SOURCE HAS LOAD PHASE I START AUTOMATICALLY YES TARGET LOAD TYPE 0;

Now run the script using asnclp command line 
asnclp -f replication_setup.asn

This will  create control tables, replication queue map and subscription for table DBA. Once done with above script now lets start the capture and apply programs on both sides.

Issue this commands using db2inst1 ID

nohup asnqcap capture_server=TESTA capture_schema=ASNTA &
nohup asnqcap capture_server=TESTB capture_schema=ASNTB &
nohup asnqapp apply_server=TESTA apply_schema=ASNTA &
nohup asnqapp apply_server=TESTB apply_schema=ASNTB &

Lets replicate the data now, Insert data from TESTA and look if its replicated to TESTB database.

[db2inst1@fedorabox mqm]$ db2 connect to TESTA
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

[db2inst1@fedorabox mqm]$ db2 "insert into dba values(1,'Murali','db2')"
DB20000I  The SQL command completed successfully.

[db2inst1@fedorabox mqm]$ db2 "select * from dba"
ID      NAME       TOPIC  
------- ---------- ----------
     1. Murali     db2    
  1 record(s) selected.

[db2inst1@fedorabox mqm]$ db2 connect to testb
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

[db2inst1@fedorabox mqm]$ db2 "select * from dba"
ID      NAME       TOPIC  
------- ---------- ----------
     1. Murali     db2    
  1 record(s) selected.

So the data is replicating and we just did it.... :) that's all with this tutorial on DB2 Q Replication.

In my next post i will post the common issues that i have encountered when did my first setup of Q Replication. People post your comments here ..... and lets start exploring more on Q Replication.

DB2 Q Replication setup for an active active solution PART 1

How to setup DB2 Q Replication

We are about a setup DB2 Q Replication that demonstrates active active solution. Active - Active  simply means changes happening at source should go to target and at same time changes happening at target should go to source.

High level steps:

1) Install DB2 9.7 on Fedora 18 OS - I'm Fedora OS as its free edition supported by REDHAT community.
2) Install Websphere MQ 7.1 for messaging system between 2 DB2 databases.
3) Create MQ objects needed for active active Q replication.
4) setup a replication for a sample table to demonstrate active active replication.

Below is the setup of two databases named TESTA and  TESTB for demonstration of active - active Q replication between two db2 databases. Below is how the setup look like.

Links to download the needed software's

1) Fedora 18 or above ( Free copy ) from here :- http://fedoraproject.org/en_GB/get-fedora-all

2) DB2 9.7 AESE ( trail 90 days ) from here https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_us&source=swg-dm-db297trial&S_CMP=web_dw_rt_swd

3) Websphere 7.1 (trail 90 days ) from here :- www.ibm.com/webspheremq/downloads

Installation procedure of DB2 9.7 on Fedora OS

After installing fedora and Vmware tools you will be able to copy the downloaded trails files to the host machine to proceed with installation. An advice here when you try to copy to host machine, Use the command with sudo access ( systemctl mask tmp.mount ), This will turn off any filesystem from being mounted on /tmp, forcing it to be on / instead. To turn it on again, run ( systemctl unmask tmp.mount )

1) Its always a best practice to do a prerequisite check before installing DB2

[db2inst1@fedorabox server]$ cd server/
[db2inst1@fedorabox server]$ sudo sh db2prereqcheck
[sudo] password for db2inst1:
WARNING:
DBT3534W  The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.
WARNING:
   The 32-bit library file libpam.so is not found on the system.

Above you can see that there is a warning that libpam.so is missing. Now lets install it from internet. run sudo yum install pam-devel.i686   in your terminal

Ok lets run the prereqcheck again and see.

[db2inst1@fedorabox server]$ sudo sh db2prereqcheck

[sudo] password for db2inst1: 

WARNING:

DBT3534W  The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.

above warning can be ignored.

2) now lets install the db2 using db2setup

[db2inst1@fedorabox server]$ sudo sh db2setup

we get a GUI prompt like ( shown below ) which will allow us to use various options for installation, Just go ahead proceed with all steps and at the end when you click finished , it will do the actual work of installing DB2 with the selected options 

When all the steps are completed lets set the db2profile in user home directory add below 3 lines to profile file of db2inst1 ( run vi .bash_profile )

if [ -f /home/db2inst1/sqllib/db2profile ]; then

        sh /home/db2inst1/sqllib/db2profile

fi

execute the profile file to take changes into effect 

run       . .bash_profile 

3) Now lets start the instance.

[db2inst1@fedorabox ~]$ db2start

04/07/2014 15:17:17     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

That's it with DB2 Installation on Fedora OS and we can proceed to step 2 which will be preceded in the next post as PART 2

DB2 Q Replication it made me stuck for more than a month

I'm back with fresh concepts and ideas in my mind, those were the things that made me stuck with issues for at least more than a month and was not able to blog anything until i learnt something practical out of it myself. So what is that interesting topic in DB2?

DB2 Q Replication and Event Publishing.

Since the time i started working on replication it made me so interested that i wanted to implement and see how it works.

DB2 Q Replication captures the changed data from source and send committed transactions as messages using a messaging system called IBM WebSphere Message Queues to the target.

Changes that happen at source are captured by Capture program by reading DB2 recovery logs and put them as messages on the WebSphere message queues where the apply program resides at target reads messages from queues and write back changes to respective targets. below figure explains it.

I'm my next post i will give the implementation of DB2 Q replication between two DB2 databases.