ChannelDB2

A Fast Way to Get Started with Q Replication for DB2 Active-Active Databases

 

As you may know, IBM made two-site Q Replication available in DB2 Advanced ESE and InfoSphere Warehouse at no additional cost. The main reason was to give DB2 a built-in solution for active-active databases. Since a lot of DB2 people are new to Q Replication, I wanted to show you how easy it is to give it a try and set up a configuration. In a previous post, I described a fast way to get started with MQ for active-active databases. In this post, I show how to set up a Q Replication configuration for active-active databases. It takes advantage of the MQ set up from the previous post. Neither post is very long and you'll probably spend no more than 30 mn on this one.


The instructions in these posts are for two databases on a single Windows system. You can modify the instructions to work for two different systems or for UNIX/Linux systems with a few changes (provided at the end of this post).

Before You Start

 

First, you'll need a copy of MQ V7. If you don't have one, you can download it from one of two places - IBM's Passport Advantage or the MQ pages on ibm.com. You should be able to download from Passport Advantage if you've purchased DB2 or InfoSphere Warehouse. That's because most DB2 and InfoSphere Warehouse editions include a copy of MQ. Otherwise, if you're just kicking the tires, you can download an evaluation copy of MQ from the WebSphere MQ pages on ibm.com.

 

Next, you'll need a copy of a DB2 product at version 9.7 FP4 or above. You will need to use an updated replication tool from it. You can get this tool several ways. For example, if you have a DB2 server and it's at a level earlier than 9.7 FP4, you can upgrade your DB2 to the latest fix pack. If you cannot upgrade your DB2 server at this time, you can download the IBM Data Server Client and install the latest release of the Client from ibm.com's DB2 pages without affecting your DB2 server. The updated tool is in this client.



Do It Yourself

 

You will now set up active-active Q Replication between two databases called SITE1 and SITE2. These two databases are created with the same contents as the DB2 SAMPLE database. At a high level, your Q Replication set up looks like the following:

 

 

In the following sections, you'll define, validate and run an active-active Q Replication configuration.

 
First, Let's Configure Everything Needed to Replicate Data:
C1) Bring up a DB2 Command window, create a temporary directory called myscripts under say C:, and switch to it:

mkdir c:\myscripts
cd \myscripts

If you use two separate systems, follow the instructions provided in Running on Separate Systems (separate setup). Once you are done, go directly to step C4 (skip steps C2 and C3) below. 

 

C2) Copy and paste the first script at the bottom of this post into a file called createdb.bat in C:\myscripts. 

 

C3) Run the batch file by issuing the following command from your DB2 command window:

 

createdb.bat
 
You now have two new databases called SITE1 and SITE2.

 

C4) Follow the instruction in "A Fast Way to get started with MQ" to create the MQ objects Q Replication needs.

 

C5) Copy and paste the second script at the bottom of this post into a file called activeqset.asnclp in C:\myscripts.
 

If you use two separate systems, follow the instructions provided in Running on Separate Systems (activeqset update) to make the necessary updates to the script you just copied. Once you are done, proceed to step C6 below. 


C6) Run the script file by issuing the following command from your DB2 command window:

asnclp -f activeqset.asnclp

You now have Q Replication subscriptions for active-active data replication between SITE1 and SITE2 for three tables - Employee, Department and Project.


Next, Let's Verify your MQ Set Up Is Correct for Q Replication:
 
Naturally, your MQ configuration will work for Q Replication if you followed the instructions in my previous post. However, you can verify an MQ configuration with a few simple commands before you run Q Replication. That's what the following instructions do:

 

V1) Copy and paste the third script at the bottom of this post into a file called activeqchk.asnclp in C:\myscripts.

 

If you use two separate systems, follow the instructions provided in Running on Separate Systems (activeqchk update) to make the necessary updates to the script you just copied. Once you are done, proceed to step V2 below. 

 
V2) Run the script file by issuing the following command from your DB2 command window:

asnclp -f activeqchk.asnclp
The output of the command is displayed in stdout and should include four ASN2291I informational messages confirming that none of the checks failed. The output of the command is also available in the asnclp default log filename qreplmsg.log. 

To review the list of all checks that can be performed, see the Information Center topic about MQ validation checks.


Now, Let's Replicate Data:
 
To do this, you will run one Q Capture program and one Q Apply program for each database as in the following graphic:

 

 

 

You will run each program in its own DB2 Command window* (if you do this under Windows 7, make sure you read the footnote):

 

R1) Bring up the first window, switch to myscripts directory and start the Q Capture program for SITE1:

 

cd \myscripts
asnqcap capture_server=SITE1

 

R2) Bring up the second window, switch to myscripts directory and start the Q Capture program for SITE2:

 

cd \myscripts
asnqcap capture_server=SITE2

If you use two separate systems, the command above is started on System 2. 

 

R3) Bring up the third window, switch to myscripts directory and start the Q Apply program for SITE1:

 

cd \myscripts
asnqapp apply_server=SITE1

 

R4) Bring up the fourth window, switch to myscripts directory and start the Q Apply program for SITE2:

 

cd \myscripts
asnqapp apply_server=SITE2

If you use two separate systems, the command above is started on System 2. 
 

Replication is now running between SITE1 and SITE2! Let's complete this exercise with a few DB2 updates and queries.

 

R5) From your initial DB2 Command window, issue the following db2 statements to list employees and projects in the Planning department:

 

db2 connect to SITE1 ;
db2 "select e.firstnme, e.lastname, p.projname from employee e, department d, project p where
d.deptname like 'PLANNING%' and e.workdept = d.deptno" ;

The query returns 20 projects for Michael Thompson of the Planning department at SITE1. To see the results for SITE2, substitute SITE2 for SITE1. You will get the same result.

 

R6) From the same DB2 Command window, issue the db2 statements provided below to update an employee at each site. 
 

If you use two separate systems, follow the instructions provided in Running on Separate Systems (connect update) to add the userid and password syntax necessary to connect to the SITE2 database from System 1.

 

db2 connect to SITE1 ;

db2 "update employee set workdept = 'E21' where firstnme = 'MICHAEL' and lastname = 'THOMPSON' " ;
db2 connect reset ;
 
db2 connect to SITE2 ;

db2 "update employee set workdept = 'B01' where firstnme = 'MARIA' and lastname = 'PEREZ' ";
db2 connect reset ;

 

R7) Perform step R5 again to list the employee and projects that are now part of the planning department.

 

The query should now return 20 projects all for Maria Perez. As expected,  you will see the same result for SITE1 and SITE2 as the active-active configuration replicates data in both directions.

 

R8) After you are done trying a few updates of your own, stop the Q Replication programs. To do so, issue CTRL-C on each of the four DB2 Command windows you previously brought up to start the Q Capture and Q Apply programs.

 

That's it. You're done :)
 
 
Running on Separate Systems

  

To do this, you will also run Q Replication on each system as in the following graphic:

 

 
The asnclp scripts in this blog are all invoked from System 1 even when running on separate systems.
 
 
Running on Separate Systems (separate database setup)

 

When you run on separate systems, the SITE1 database is created on System 1 and the SITE2 database is created on System 2.
 
Since this blog uses the db2sampl command to create the sample tables for SITE1 and SITE2, it is required that you use the same userid to connect to SITE1 and SITE2 to ensure that all tables are created with the same schema.
 
Create and catalog the active-active databases as follows:
 

1) On System 1, create the SITE1 database by issuing three commands from a DB2 command window on System 1 as follows:

db2sampl -dbpath C -name SITE1 -sql
db2 update db cfg for SITE1 using logarchmeth1 "DISK:C:\myscripts"
db
2 backup database SITE1 to "C:\myscripts" without prompting

2) Login to System 2 by using the same logon (DB2 userid) as the one you use for System 1.

This step is required to ensure that all tables will be created with the same schema across the SITE1 and SITE2 databases.

 

3) On System 2, bring up a DB2 Command window, create a temporary directory called myscripts under say C:, and switch to it:

mkdir c:\myscripts
cd \myscripts

4) On System 2, create the SITE2 database by issuing three commands from the newly created DB2 command window as follows: 

db2sampl -dbpath C -name SITE2 -sql
db2 update db cfg for SITE2 using logarchmeth1 "DISK:C:\myscripts"
db2 backup database SITE2 to "C:\myscripts" without prompting

5) Catalog the SITE2 database on System 1 by issuing two commands from a DB2 command window on System 1 after substituting the correct ip address and port number for System 2 as follows:

db2 catalog tcpip node node2 remote your_System2_ip_address server your_System2_port
db2 catalog database SITE2 as SITE2 at node node2
You now have two new databases called SITE1 and SITE2 on System1 and System2 respectively. Please go back to step C4 of the blog. 


Running on Separate Systems (asnclp activeqset update)


To provide the asnclp activeqset command the right credentials to connect to the SITE2 database, a
dd an id and password clause to the 'set bidi node' statements of the activeqset.asnclp script as follows:

set bidi node 2 server dbalias site2 id id2 password "p2wd";

In this blog, the same userid is used to connect to SITE1 and SITE2 so 'id2' is the same userid you use to connect to SITE1. 'pwd2' can be set to any password you need and may be different than the password associated with the userid for SITE1.  

Once you are done with the update, please go back to step C6 of the blog. 

 

Running on Separate Systems (asnclp activeqchk update)

To provide the asnclp activeqchk command the right credentials to connect to the SITE2 database, add an id and password clause to the 'set server'statements of the activeqchk.asnclp script as follows:

set server target to dbalias site2 id id2 password "p2wd";

The 'id2' and 'pwd2' should be the same userid and password provided in the section above (asnclp activeqset update).

Once you are done with the update, please go back to step V2 of the blog. 

 

Running on Separate Systems (db2 connect update)

 

Use the following connect statement to include the credentials to connect to SITE2 from SITE1, as follows: 

db2 connect to SITE2 user id2 using pwd2

The 'id2' and 'pwd2' should be the same userid and password provided in the sections above (asnclp activeqset update and asnclp activqchk update). 

Once you are done with the update, please go back to step R6 of the blog. 

 

Scripts Used in this Post

 

Three scripts are introduced in this post for a single system deployment: 

 

First script: The createdb.bat File 

This file creates the SITE1 and SITE2 sample databases and enables them for archive logging:
 

db2sampl -dbpath C -name SITE1 -sql
db2 update db cfg for SITE1 using logarchmeth1 "DISK:C:\myscripts"
db
2 backup database SITE1 to "C:\myscripts" without prompting

db2sampl -dbpath C -name SITE2 -sql
db2 update db cfg for SITE2 using logarchmeth1 "DISK:C:\myscripts"
db2 backup database SITE2 to "C:\myscripts" without prompting

You can also download the contents of the file (add file extension '.bat' if you run on Windows) here: createdb

If you are running on Unix/Linux, you will need to update the dbpath, logarchmeth1 path and backup path appropriately. 

 

Second Script: The activeqset.asnclp File

 

This script creates the Q Replication definitions for the EMPLOYEE, DEPARTMENT and PROJECT tables:
 

asnclp session set to q replication ;
set output multidir ;
set run script now stop on sql error on ;

set bidi node 1 server dbalias site1 ;
set bidi node 2 server dbalias site2 ;

create control tables for node 1 ;
create control tables for node 2 ;

create replqmap "SITE1.ASN_TO_SITE2.ASN" (node 1, node 2) ;
create replqmap "SITE2.ASN_TO_SITE1.ASN" (node 2, node 1) ;

set tables (node 1 "EMPLOYEE") ;
set tables (node 1 "DEPARTMENT") ;
set tables (node 1 "PROJECT") ;

create qsub subtype B from node SITE1.ASN source has load phase N target load type 0 ;

You can also download the contents of the file here: activeqset.asnclp
  
The two important statements in the script are the asnclp create replqmap and create qsub statements. The things you need to know about these statements are the following:

 

  • The create replqmap statement associates MQ objects with a replication queue
    • for active-active configurations, two replication qmaps are defined, one for each direction
    • MQ defaults in this statement are the same as the defaults created by the 'Create MQ Script' command of the previous post
  • The create qsub statement creates subscriptions for all tables listed in the 'set table' statements
    • for active-active configurations, two subscriptions are defined for each table, one for each direction
    • in this example, conflicts are detected but their resolution is not enforced (asnclp defaults). Conflicts may arise if an update on SITE1 conflicts with an update on SITE2.
    • the 'from node' clause in this example specifies not to load SITE2 before replicating change data. This is because the db2sampl command populates SITE2 with data. In active-active configurations, this is often a best practice (configuring a site for active-active often includes loading it with data before starting replication).

 

Third Script: The activeqchk.asnclp File
  
The asnclp script performs MQ validation checks for your Q Replication configuration. These checks ensure that the proper queue managers, queues, channel definitions and queue properties are defined.

 

asnclp session set to q replication ;
set run script now stop on sql error on ;

set server capture to DBALIAS SITE1 ;
set server target to DBALIAS SITE2 ;
validate wsmq environment for capture schema ;
validate wsmq environment for replqmap "SITE1.ASN_TO_SITE2.ASN" ;

set server capture to DBALIAS SITE2 ;
set server target to DBALIAS SITE1 ;
validate wsmq environment for capture schema ;
validate wsmq environment for replqmap "SITE2.ASN_TO_SITE1.ASN" ;

You can also download the contents of the file here: activeqchk.asnclp 
 

--

*In steps R1-R4, if you are running Windows 7 and you are not running as Administrator, you will get an authorization error when starting Q Capture (Windows 7 will request the authorization explicitly) and Q Apply (message number ASN0507E).  To resolve this error, run the DB2 command windows as Administrator.
 

 

 

Views: 10978

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Shiva Shankar on May 6, 2015 at 12:41am

Hi Cecile

Thanks for a nice article, it was very helpful.

Regards

Shiva Shankar

Comment by Murali Chava on January 28, 2014 at 1:07am

Hi Cecile,

I was a newbie to Q replication, just started my career with DB2 this tutorial which you have posted is soo good clearly remarkable easy in the net and i interpreted and learnt a lot with this tutorial .. I have exactly followed your steps struck with few errors resolved them and now the data is perfectly replicating as given here... Thank you very much for this tutorial..

Thanks again Cecile.

Regards,

Murali Chava.

Comment by Pramod on September 6, 2013 at 7:25am

Thank you Cecile. Its a very helpful blog.

I'm failing at the verify step for active-active on Unix server(both DB on same instance).

I've put the fenced userid in mqm group, in spite of that its still failing? Can you please help.

ASN2270E  The stored procedure "SYSPROC.ASN_ADMIN_UTIL" in database "PSLV002D" is not authorized to access the WebSphere MQ queue manager "tiera7aded1 - PSLV002D" because the operating system user ID "test101f", which is the DB2 fenced user of the instance that contains this database, is not a member of the operating system group for WebSphere MQ applications (usually mqm) at the host "XXXXXX"

Comment by Cecile Madsen on October 18, 2012 at 5:39pm

Short reminder on the DB2 fixpack requirement: this blog takes advantage of enhancements made to the Replication product in DB2 FP4 so make sure to use that DB2 fixpack level or above when you try the scenario...

Comment by Cecile Madsen on October 8, 2012 at 4:48pm

Hello,

Thanks for the comment. I updated the section 'Running on Separate Systems' to clarify how to create the SITE1 database on system 1 and the SITE2 database on system 2. Since asnclp commands will be issued on system 1, I also describe how to catalog the SITE2 database on system 1 so that asnlp can connect to it. That should fix your problem..

Comment by hatran on October 7, 2012 at 10:50pm

Hello Cecile Madsen !

Thanks for very clear your post

I'm newbie Q replication. I running with two separate linux systems. I created 2 DB2 database SITE 1 & SITE2 in each server. When I  run second scripts at 6 step

asnclp -f activeqset.asnclp

get error

ASN2022E  The action ended in error. An SQL error was encountered. SQL message is "[jcc][4038][12241][3.62.56] T2LUW exception: SQL1013N  The database alias name or database name "SITE2" could not be found.  SQLSTATE=42705

ERRORCODE=-1013, SQLSTATE=42705".

 

I think need config node on each server. Do you help me?

Thanks

Featured Downloads

Try BLU Acceleration on Cloud

© 2017   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service