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.
