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.