A Sample Program For Moving DB2 Changed Data from WebSphere MQ to Files with Q Replication
Introduction
This article introduces a sample program that retrieves DB2 changed data in a delimited values format from
WebSphere MQ and saves it to files. The sample is based on the event publishing function found in the IBM
InfoSphere Data Replication (IIDR) Q Replication technology.
Event Publishing captures changed data from a database transaction log and makes it available for applications
to consume. The consuming applications may range from in-house programs to off-the-shelf ETL tools, such as
InfoSphere DataStage, that perform complex transformations on the data. This process is illustrated in the
diagram below:
Q Replication offers event publishing to WebSphere MQ and to tables called CCDs. Some applications are not
able to consume from these directly They need another interface to changed data.
Files are often a good option. You can move changed data from WebSphere MQ to files with a simple event
consumer. The sample discussed in this paper is an event consumer. It shows how more applications can
consume changed data from Q Replication.
Even though the article shows you how to invoke the sample program for DB2 changed data, its contents also
apply to changed data from Oracle.
The Solution at a Glance
The three stages of the solution are shown in the diagram below:
In the first stage, you identified the tables for which changed data is to be captured. The event publishing
function puts committed transactions into a queue. WebSphere MQ sends the data to the system where you
need it.
In the second stage, the sample program, called asnmsg2file, reads the queue and writes data to the local file
system. One file is created for each table. The program reads data from the queue for a user-defined amount
of time. For example, 5-10 minutes. At the end of that time, the program shuts down so that the files can be
processed.
In the final stage, the consuming application reads the files. The data is in a delimited values format. The
application or a script deletes the files once the application is finished with them. The asnmsg2file program can
now be run again.
System 1 and system 2 in the picture above are logical servers that can reside on the same physical server. This
is the case for the exercise in Part 3.
How This Article is Organized
The article starts with a description of the sample program prerequisites then moves on to file format and
processing, restrictions, program logic and input parameters.
The second part of the article includes instructions to install and configure the program to run on your system.
The third part of the article ends with the tasks and commands necessary to run a simple end-to-end scenario.
Prerequisites
The sample program runs on Windows, UNIX (including Unix System Services USS) or Linux. Before you run it, a
Q Replication environment must be up and running for a DB2 or InfoSphere Warehouse. The Q Replication
environment must be defined as follows:
•
Provide a WebSphere MQ local queue on the system where you plan to run the asnmsg2file program. This
queue will hold the incoming data. You also need the supporting MQ definitions to connect the source
system to the target system. If you are not familiar with WebSphere MQ, you can set up a typical MQ
configuration for event publishing using the instructions found in A Fast Way to Get Started with
WebSphere MQ for DB2.
•
License Q Replication for your DB2.
◦
On Windows, UNIX or Linux, the Q Capture program in DB2 ESE and DB2 Workgroup will not start
unless it has been licensed through one of the following products:
◦
◦
◦
◦
The IBM Homogeneous Replication Feature for DB2
InfoSphere Data Replication
InfoSphere Replication Server
On z/OS, the Q Capture program is licensed though one of the following products:
◦ InfoSphere Data Replication
◦ InfoSphere Replication Server
Processing Files Created by the Sample Program
For each run, the program creates a directory whose name is a timestamp. The directory is created as a
subdirectory of a main directory provided through an input parameter. Each run's data files go into this
subdirectory. One data file is created for each table with changed data.
Consuming applications look in the main directory for the presence of subdirectories to determine whether the
asnmsg2file program ran, and how many times, since the last time the consuming application was run. If new
subdirectories are found, the consuming application then looks for data files in each subdirectory.
The consuming application should delete subdirectories after successfully processing their data files.
The output format of each data file follows closely the Data Event Publisher delimited message format.
You can not change the output format of the data file but you can provide your own output delimiter (using an
optional parameter). For example, you won't be able to change date or timestamp format.
The picture below idisplays the contents of the EventPublisherFiles directory, the directory provided to the
asnmsg2file program for the scenario described in part 2. As you can see:
◦
The 1329960391105 directory was created when asnmsg2file first started and holds data for that run
◦
Three files were created corresponding to three tables found with changed data in the MQ queue
when the program was running:
Restrictions and Error Messages
The asnmsg2file program does not currently perform comprehensive error checking so review the configuration
attributes and prerequisites to avoid unnecessary exceptions. If any unexpected error happens after the
message has been read from the queue, the data is available in the data files so data read from the MQ queue
is not lost.
There are two restrictions
1) The program currently only runs on a system where a WebSphere MQ queue manager is running. It is a JMS
application that uses JMS binding mode connections (not client mode). What this means is that it won't be able
to access a remote MQ queue manager.
2) The program currently only supports the comma ',' as the decimal delimiter.
The asnmsg2file Program Logic
The sample program runs as a batch job. You decide how often and how long the program runs. A cron job
may be helpful to schedule these runs automatically.
Once started, the sample program does the following:
•
Verifies input parameters
Three required input are the MQ queue manager name, the MQ publishing queue name and the output
directory where all files are created. One of the optional parameter is the duration of the run with a
default of five minutes
•
Connects to the queue manager
•
Creates a directory with the current timestamp. This is created as a subdirectory of the one provided as an
input parameter.
•
Listens to the queue and process messages for a user-specified amount of time. By default, this is five
minutes, a good choice for many situations.
•
For each message found in the queue
◦
Parses the message and finds the table name(s) associated with the changed data. The MQ message
may include changed data for multiple tables.
◦
Processes the message to format the output.
◦ Writes the data to files in the directory with the current timestamp. The program uses the file
naming convention tablename.dat (the file name does not include the table's schema) and creates the
file if it doesn't already exist.
Note: if a large transaction spans across multiple MQ messages, the data files are first created in a
“stage” directory. After the transaction is processed in its entirety, the sample moves the contents of
the stage directory to the timestamp directory.
•
When the run time has been completed, disconnects from the queue manager and stop.
The asnmsg2file Parameters:
The three required parameters are:
-qmgr: WebSphere MQ queue manager name that manages the local queue to be read by the sample
program. The queue manager must be running before invoking asnmsg2file otherwise you'll receive a
JMS exception (queue manager not started)
-queue : Name of the local queue. If you want to process messages from a different queue, you will
need to start another invocation of the program
-out: Directory where timestamped subdirectories (and their data files) are created. If it doesn't exist
prior to invoking asnmsg2file, it will be created. That value should remain the same across scheduled
invocations of asnmsg2file if the stage directory is populated with data files across the invocations.
You will need to invoke the program with additional (optional) input parameters if you:
•
•
•
want to run the program for a different amount of time than the provided defaults (5mn)
made some updates to the default Q Replication publication queue map properties
want to change the output file structure (changed data record delimiter or column delimiter).
The six additional optional parameters are:
-time: Length of time the program should run, in milliseconds. Default is slightly less than 5 minutes
(28500 ms)
-outRecord: Output changed data record separator. Default is 0A (ASCII new line)
-outCol: Output column delimiter. Default is 1E (ASCII record separator)
-inCol: Input column delimiter. Default is 2C (ASCII comma), same as Data Event Publisher Hex ASCII
value column delimiter default
-inString: Input character string delimiter. Default is 22 (ASCII double quotation mark), same as Data
Event Publisher Hex ASCII value char string delimiter default
-inRecord: Input changed data record separator. Default is 0A (ASCII new line), same as Data Event
Publisher Hex ASCII value changed data delimiter default
Part 2: Installing and Configuring asnmsg2file to Run on Your System
To run asnmsg2file in your environment, complete the following tasks:
•
•
Launch a new DB2 Command Window and create a new directory where the program will run.
◦
If you are running Windows 7, launch the new command window as Administrator to prevent any
authorization errors running Websphere MQ.
◦
For the exercise of Part 3, I used 'mymsg2f'.
Switch to the 'mymsg2f' directory and unzip the asnmsg2file.zip file (in the Samples section of the Q+SQL
Replication Forum on developerWorks) by running the command: unzip asnmsg2file.zip.
You should see six files in your directory. Let's call this current directory 'mymsg2f':
•
◦
EPcreatedb.bat: DB2 script to create a sample database
◦
EPmq.asnclp: ASNCLP script to create WebSphere MQ objects
◦
EPpub.asnclp: ASNCLP script to create event publishing objects
◦
asnmsg2file.jar: sample event consumer program to save messages to files
◦
asnmsg2file.java: sample source code
◦
README: a subset of this article describing the asnmsg2file event consumer program
Switch to the 'mymsg2f' directory and run the following command to update your CLASSPATH variable and
avoid a 'NoClassDefFoundError' exception:
•
◦
Windows systems: set CLASSPATH=%CLASSPATH%;C:\mymsg2f\asnmsg2file.jar
◦
UNIX/Linux systems: exportCLASSPATH=$CLASSPATH:/u/.. /mymsg2f/asnmsg2file.jar
Verify that the WebSphere MQ JMS jar files already exist in your CLASSPATH (part of the WebSphere MQ
JMS install):
◦
Windows systems: ..\Websphere MQ\Java\lib\com.ibm.mqjms.jar and ..\Websphere
MQ\Java\lib\jms.jar
◦
UNIX/Linux systems: ../lpp/mqm/java/lib/com.ibm.mqjms.jar and ../lpp/mqm/java/lib/jms.jar
If they don't, you need to include them in your CLASSPATH using the method listed above.
•
The asnmsg2file.jar file is compatible with JDK 6 VM. If you run in a pre-JDK 6 VM environment, compile
the asnmsg2file.java source to create a new jar file by running the two commands below:
◦
javac -verbose asnmsg2file.java
◦
jar cvf ansmsg2file.jar asnmsg2file.class asnmsg2file$MQListener.class asnmsg2file$StopListen.class
You're now ready to run your scenario.
Part 3: Running a Simple Scenario
For the simple scenario included below, I used a Windows 7 system with a copy of WebSphere MQ V7 and a
copy of the DB2 product at version 9.7 FP4.
This section takes about 20 minutes to complete. It shows you how to configure three publications and run the
sample program to create three data files with the changed data. The diagram summarizes the configuration:
The scenario includes four steps:
1. Set up directories
2. Create a DB2 sample database PUBSRC and publication definitions for three of its tables
3. Start Q Capture (asnqcap) on your system
4. Update the DB2 source system and run asnmsg2file to review the output files
1.
Set Up Directories
Before running the scenario, complete the two tasks below:
2.
◦
Create a new directory for the output directory where all sub-directories and data files will be created.
I used 'DEPFiles' which I created in the 'C:\mymsg2f\' directory.
◦
Launch another Command Window, switch to 'mymsg2f' and create another new directory: 'myQCap'
Create the sample database and publication definitions
Run the four commands below all from 'mymsg2f' directory:
◦ createdb.bat
The command creates a sample database called PUBSRC
◦ asnclp -f EPmq.asnclp
The command creates a shell and a batch file, for UNIX/Linux and Windows systems respectively
◦ qrepl.pubsrc.mq_windows.bat (windows system) or qrep.pubsrc.mq_aixlinux.sh (UNIX/Linux system)
The command creates a new queue manager PUBSRC, starts it and creates all necessary queue objects
for Data Event Publishing. The publishing queue is called ASN.PUBSRC.DATA.
◦ asnclp -f EPpub.asnclp
The command creates the publications and their related objects for three source tables: EMPLOYEE,
DEPARTMENT and PROJECT.
3.
Start Q Replication's Q Capture program.
Run the command below from 'myQCap' directory:
◦
asnqcap capture_server=PUBSRC
You should see initialization messages for the EMPLOYEE, DEPARTMENT and PROJECT tables.
4.
Update the DB2 Source System and Run asnmsg2file
Run the five DB2 statements below all from 'mymsg2f' directory:
◦
db2 "update EMPLOYEE set job = 'MANAGER' where lastname = 'QUINTANA' ";
db2 "insert into DEPARTMENT values ('A01', 'TEST', null,'A00',null)”;
db2 "insert into DEPARTMENT values ('A02', 'TEST2', null,'A00',null)”;
db2 "insert into DEPARTMENT values ('A03', 'TEST3', null,'A00',null)”;
db2 "update PROJECT set prendate = CURRENT DATE where projname = 'OPERATION SUPPORT' ";
You should now see five new messages in the ASN.PUBSRC.DATA queue.
Finally, run the Java program from 'mymsg2f' directory to process the messages and save them to files:
◦
◦
java asnmsg2file -qmgr PUBSRC -queue ASN.PUBSRC.DATA -time 60000 -out DEPFiles
▪
I'm using a one-minute run here so you don't have to wait long to see the results
▪
You'll want to add optional parameters as discussed earlier and/or vary the db2
insert/update/delete statements to fully try this new program out.
You should have three files in a directory created under the DEPFiles output directory, as follows:
Stay Tuned
Expect an upcoming article and blog post with a real-world use case for this sample.