This sample contains two ASNCLP scripts for setting up
peer-to-peer Q Replication with three servers. The first script generates
commands to create WebSphere MQ objects at all three systems. The
second script includes Q Capture and Q Apply control tables at each
of the three servers, replication queue maps in both directions between
each server, and six Q subscriptions between the servers.
The scenario involves three databases, SAMPLE, SAMPLE2, and SAMPLE3.
One table, STAFF, will be replicated between the three databases.
You can copy the scripts to a text file, modify the values, and
run the scripts by using the ASNCLP -f filename command.
First:
- Script 1: Change the values for the MQHOST keywords
to the IP addresses of the three systems, and ensure that the user
ID that starts the ASNCLP program has permissions to execute the generated
batch or shell script files.
- Script 2: Change db2admin and "passw0rd" to
the user IDs and passwords for connecting to the three servers.
Prerequisite: The scripts require the
replication administration tools to be at Version 9.7 Fix Pack 4.
ASNCLP script 1: Create WebSphere MQ objects
##################################################
ASNCLP SESSION SET TO Q REPLICATION;
CREATE MQ SCRIPT RUN NOW
CONFIG TYPE P
MQSERVER 1 NAME SAMPLE MQHOST "9.30.54.118",
MQSERVER 2 NAME SAMPLE2 MQHOST "9.30.54.119",
MQSERVER 3 NAME SAMPLE2 MQHOST "9.30.54.120";
QUIT;
##################################################
Notes: The CREATE MQ SCRIPT
command generates three shell script files for Linux and UNIX systems
(
qrepl.sample.mq_aixlinux.sh,
qrepl.sample2.mq_aixlinux.sh,
and
qrepl.sample3.mq_aixlinux.sh) and three batch
files for Windows systems
(
qrepl.sample.mq_windows.bat,
qrepl.sample2.mq_windows.bat,
and
qrepl.sample3.mq_windows.bat). If you run
the ASNCLP program on the same system as SAMPLE, SAMPLE2, or SAMPLE3,
the RUN NOW option prompts the ASNCLP program to run the batch files
or shell scripts to define the queue manager, queues, and other WebSphere® MQ objects for that
system. If the ASNCLP program is remote from any of the databases,
do one of these things:
- Run the appropriate batch file or shell script at the remote system.
- Run the ASNCLP script on the remote system. The ASNCLP program
will detect that it is local to that system and will automatically
run the generated script.
ASNCLP script 2: Set up Q Replication
ASNCLP SESSION SET TO Q REPLICATION;
SET PEER NODE 1 SERVER DBALIAS SAMPLE ID DB2ADMIN PASSWORD "passw0rd" SCHEMA ASN;
SET PEER NODE 2 SERVER DBALIAS SAMPLE2 ID DB2ADMIN PASSWORD "passw0rd" SCHEMA ASN;
SET PEER NODE 3 SERVER DBALIAS SAMPLE3 ID DB2ADMIN PASSWORD "passw0rd" SCHEMA ASN;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
CREATE CONTROL TABLES FOR NODE 1;
CREATE CONTROL TABLES FOR NODE 2;
CREATE CONTROL TABLES FOR NODE 3;
# First queue map (from SAMPLE to SAMPLE2)
CREATE REPLQMAP SAMPLE_ASN_TO_SAMPLE2_ASN (NODE 1, NODE 2);
# Second queue map (from SAMPLE2 to SAMPLE)
CREATE REPLQMAP SAMPLE2_ASN_TO_SAMPLE_ASN (NODE 2, NODE 1);
# Third queue map (from SAMPLE2 to SAMPLE3)
CREATE REPLQMAP SAMPLE2_ASN_TO_SAMPLE3_ASN (NODE 2, NODE 3);
# Fourth queue map (from SAMPLE3 to SAMPLE2)
CREATE REPLQMAP SAMPLE3_ASN_TO_SAMPLE2_ASN (NODE 3, NODE 2);
# Fifth queue map (from SAMPLE3 to SAMPLE)
CREATE REPLQMAP SAMPLE3_ASN_TO_SAMPLE_ASN (NODE 3, NODE 1);
# Sixth queue map (from SAMPLE to SAMPLE3)
CREATE REPLQMAP SAMPLE_ASN_TO_SAMPLE3_ASN (NODE 1, NODE 3);
SET SUBGROUP "p2p3group";
SET TABLES (SAMPLE.ASN.ELB.STAFF);
CREATE QSUB SUBTYPE P;
QUIT;
Notes: The commands in this
script perform the following actions:
- The SET PEER NODE commands specify the paired Q Capture and Q
Apply servers at the three databases.
- The SET RUN SCRIPT NOW option prompts the ASNCLP program to generate
and run the SQL to create all objects.
- The CREATE CONTROL TABLES FOR commands use the NODE 1, NODE 2,
and NODE 3 keywords to prompt the ASNCLP program to create both Q
Capture and Q Apply control tables at each server.
- In the CREATE REPLQMAP commands, the (NODE 1, NODE 2) syntax creates
queue maps in both directions between each server.
- For both the control tables and queue maps, the ASNCLP program
by default uses the queue managers, queues, and other objects that
were defined by the CREATE MQ SCRIPT command.
- The SET SUBGROUP command assigns a group name, p2p3group, to all
Q subscriptions in the peer-to-peer group.
- The SET TABLES command specifies one table, ELB.STAFF, at the
SAMPLE database. This provides enough information to generate SQL
statements for creating matching tables at SAMPLE2 and SAMPLE3.
- A single CREATE QSUB command generates statements to create six
peer-to-peer Q subscriptions between SAMPLE, SAMPLE2, and SAMPLE3.
Q Replication and Event Publishing