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).
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.
createdb.bat |
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.
asnclp -f activeqset.asnclp |
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.
asnclp -f activeqchk.asnclp |
You will run each program in its own DB2 Command window* (if you do this under Windows 7, make sure you read the footnote):
cd \myscripts
asnqcap capture_server=SITE1 |
cd \myscripts
asnqcap capture_server=SITE2 |
If you use two separate systems, the command above is started on System 2.
cd \myscripts
asnqapp apply_server=SITE1 |
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.
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.
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 ;
|
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.
To do this, you will also run Q Replication on each system as in the following graphic:
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 |
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 |
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 |
Running on Separate Systems (asnclp activeqset update)
To provide the asnclp activeqset command the right credentials to connect to the SITE2 database, add 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.
Three scripts are introduced in this post for a single system deployment:
First script: The createdb.bat File
db2sampl -dbpath C -name SITE1 -sql db2sampl -dbpath C -name SITE2 -sql |
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.
asnclp session set to q replication ; set bidi node 1 server dbalias site1 ; create control tables for node 1 ; create replqmap "SITE1.ASN_TO_SITE2.ASN" (node 1, node 2) ; set tables (node 1 "EMPLOYEE") ; create qsub subtype B from node SITE1.ASN source has load phase N target load type 0 ; |
asnclp session set to q replication ; set server capture to DBALIAS SITE1 ; set server capture to DBALIAS SITE2 ; |
--
Comment
Hi Cecile
Thanks for a nice article, it was very helpful.
Regards
Shiva Shankar
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.
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"
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...
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..
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
© 2021 Created by channeldb2.
Powered by
You need to be a member of ChannelDB2 to add comments!
Join ChannelDB2