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.

Migration of db2 v9.7 to v10.1 / Upgrade of DB2 v9.7 to v10.1

Migration of db v9.7 to v10.1

1) First run db2ckupgrade to find out if we are allowed to migrate the instance to 10.1 level
2) After db2ckupgrade is successful, stop the instance and install the db2 v10.1 binaries
3) We now upgrade the instance code level to 10.1 using command db2iupgrade
4) once instance level has been upgraded we need to upgrade the databases under that instance

STEP 1

db2inst1@sairam:~/software/server$ ./db2ckupgrade -e -l /home/db2inst1/logfile -u db2inst1 -p db2inst1
DBT5508I  The db2ckupgrade utility completed successfully. The database or databases can be upgraded.

db2inst1@sairam:~/software/server$ more ~/logfile 
Version of DB2CKUPGRADE being run: VERSION "10.1"

Database: "SAMPLE"

DBT5537I  The db2ckupgrade utility has completed processing for database "SAMPLE".

DBT5508I  The db2ckupgrade utility completed successfully. The database or databases can be upgraded.

db2inst1@sairam:~/software/server$ db2ls

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.7                 9.7.0.9        9                            Tue Jul  1 16:48:02 2014 IST             0
db2inst1@sairam:~/software/server$
db2inst1@sairam:~/software/server$

db2inst1@sairam:~/software/server$ db2stop
01/07/2014 18:03:05     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

db2inst1@sairam:~/software/server$ ls -lrt
total 64
-r-xr-xr-x  1 db2inst1 db2inst1 5154 May 15 16:55 db2prereqcheck
-r-xr-xr-x  1 db2inst1 db2inst1 5154 May 15 16:55 db2setup
-r-xr-xr-x  1 db2inst1 db2inst1 5172 May 15 16:55 db2_install
-r-xr-xr-x  1 db2inst1 db2inst1 5136 May 15 16:55 db2ls
-r-xr-xr-x  1 db2inst1 db2inst1 5302 May 15 16:55 db2_deinstall
-r-xr-xr-x  1 db2inst1 db2inst1 5190 May 15 16:56 installFixPack
-r-xr-xr-x  1 db2inst1 db2inst1 5349 May 15 16:56 db2ckupgrade
drwxr-xr-x 28 db2inst1 db2inst1 4096 May 15 16:56 doc
drwxr-xr-x  6 db2inst1 db2inst1 4096 May 15 16:56 db2

STEP 2

db2inst1@sairam:~/software/server$ sudo ./db2_install 
............................................
......................................
.......................
The execution completed successfully.

For more information see the DB2 installation log at
"/tmp/db2_install.log.52793".

db2inst1@sairam:~/software/server$ db2ls

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.7                 9.7.0.9        9                            Tue Jul  1 16:48:02 2014 IST             0 
/opt/ibm/db2/V10.1               10.1.0.4        4                            Tue Jul  1 18:08:00 2014 IST             0 

STEP 3

db2inst1@sairam:/opt/ibm/db2/V10.1/instance$ sudo ./db2iupgrade db2inst1
DBI1446I  The db2iupgrade command is running.

DB2 installation is being initialized.

 Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)

Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end

Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end

Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end

Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end

The execution completed successfully.

For more information see the DB2 installation log at
"/tmp/db2iupgrade.log.23052".
DBI1070I  Program db2iupgrade completed successfully.

db2inst1@sairam:~$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10014" with level
identifier "0205010E".
Informational tokens are "DB2 v10.1.0.4", "s140509", "IP23584", and Fix Pack
"4".
Product is installed at "/opt/ibm/db2/V10.1".

db2inst1@sairam:~$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/db2inst1
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

db2inst1@sairam:~$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10014" with level
identifier "0205010E".
Informational tokens are "DB2 v10.1.0.4", "s140509", "IP23584", and Fix Pack
"4".
Product is installed at "/opt/ibm/db2/V10.1".

db2inst1@sairam:~$ db2start
07/01/2014 18:14:31     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

db2inst1@sairam:~$ db2 connect to sample
SQL5035N  The database must be upgraded to the current release.  
SQLSTATE=55001

STEP 4

db2inst1@sairam:~$ db2 upgrade db sample

DB20000I  The UPGRADE DATABASE command completed successfully.

db2inst1@sairam:~$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2inst1@sairam:~$