ASNCLP Scripting for Replication

Posted by Frank Fillmore on October 7, 2008 under Q-Replication. Tags: , .

For those of you who have worked on IBM’s SQL Replication (formerly DataPropagator-Relational) and Q Replication you are familiar with the Replication Center, a Java-based, fat-client which is part of Control Center.  Replication Center is good for folks who are learning IBM’s replication software suite or who have a single task that needs to be performed quickly.
On the other hand, managing dozens or hundreds of subscriptions is impractical through the Replication Center.  It makes more sense to use the replication scripting language called ASNCLP (ASN Command Line Processor).  It is documented in ASNCLP Program Reference for Replication and Event Publishing (SC19-1018).
To invoke ASNCLP, first install the DB2 Data Server Client (available for free at http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg21288110) or one of the DB2 engines such as Enterprise Server Edition (which bundles the client functionality).  If you already use Replication Center, you have what you need to use ASNCLP.
Then type ASNCLP at any Windows DOS prompt for an interactive ASNCLP session.  Alternatively, code a script using the ASNCLP language and run that file as follows:

ASNCLP -f queue_map.qrp

In order to run the following script, you must configure access to the source and target DB2 databases – in this case D01G and D03G – which both happen to be DB2 for z/OS subsystems.

queue_map.qrp
# Setting the environment.
# The SET OUTPUT command creates two SQL scripts: qcapqmap.sql, which adds
# definitions for the queue map to the Q Capture control tables, and
# qappmap.sql, which adds definitions for the queue map to the Q Apply
# control tables.
ASNCLP SESSION SET TO Q REPLICATION;
SET LOG “rqmap.err”;
SET SERVER CAPTURE TO DB D01G ID V110079 PASSWORD “xxxxxxxx”;
SET CAPTURE SCHEMA SOURCE ASN;
SET SERVER TARGET TO DB D03G ID V110079 PASSWORD “xxxxxxxx”;
SET APPLY SCHEMA ASN;
SET OUTPUT CAPTURE SCRIPT “qcapmap.sql” TARGET SCRIPT “qappmap.sql”;
SET RUN SCRIPT LATER;

# Creating a replication queue map.
# This command generates SQL to create a replication queue map,
# SAMPLE_ASN1_TO_TARGET_ASN1. It specifies a remote administration
# queue and receive queue at the Q Apply server, and a send queue at
# the Q Capture server. The command also sets the number of agent threads
# for the Q Apply program to 4 (a quarter of the default 16), and specifies
# heartbeat messages be sent every 5 seconds.

CREATE REPLQMAP SAMPLE_ASN1_TO_TARGET_ASN1
USING ADMINQ “ABC.ALL.ASN1.DB2.ADMIN”
RECVQ “ABC.ALL.ASN1.DB2.DATA”
SENDQ “ABC.ALL.ASN1.DB2.DATA”
NUM APPLY AGENTS 4
ERROR ACTION S
HEARTBEAT INTERVAL 5;

# Ending the ASNCLP session.
QUIT;

The resulting files, qappmap.sql and qcapmap.sql are run when attached to the Q Apply DB2 server (“target”) and Q Capture DB2 server (“source”) respectively.  These are standard SQL statements and can be run from anywhere (Command Editor on Windows, SPUFI on ISPF/PDF, etc.).

Thursday’s Q-Replication Challenges

Posted by Frank Fillmore on October 2, 2008 under Data Studio, Q-Replication. Tags: , , , .

Hat’s off to Kim for getting our blog off the ground. From the initial request from IBM, to reading “Blogging for Dummies” (yes, there really is such a book), to the artwork, format, and technical underpinnings, I’m just along for the ride.

Today’s entry comes after a long day of trying to create a DB2 for z/OS to DB2 for z/OS Q Replication environment at a very large financial institution (yes, there still really is such a thing). If you’re not familiar with Q Replication, it’s IBM’s “low-latency” – that means very little lag between when a transaction is committed on the source system and when it appears on the target – means of getting data from one database to another. This particular project is to provide high-availability, but other customers use Q Replication to populate near-real-time data warehouses. Read More…