Welcome to the start of your journey along the Q replication road. Any journey can be a bumpy ride, but after reading this book and going through the numerous examples, your journey will be a smoother one! In this first chapter, we will take you through the following discussion points:
Why we want to replicate data.
What is available today in the IBM world of data replication.
The toolsets available to set up and administer a replication environment and look at the code that we need to install for a functioning Q replication solution.
Introduce the architecture of Q replication. We look at the different types of replication available, namely the base replication methods of unidirectional, bidirectional, and peer-to-peer, and the replication architectures built on these base methods.
Replicating XML data types and compressed tables. We look at some of the design points when considering replicating compressed table.
Q replication conflict detection.
Available transformation processing for both regular and XML data.
Much has been written about why we need to replicate data, so we will keep this short. What's wrong with just storing our data in one place? Well, in today's 24x7 world where being without data for even a short period of time could be catastrophic to our business, we need a method to be able to take a copy of our data and possibly more than one copy and store it securely in a different location. This copy should be complete and be stored as many miles away as possible. Also the amount of data that has to be stored is ever increasing and being generated at a fast rate, so our method needs to be able to handle large volumes of data very quickly.
In the IBM software world today, there are a number of options available to replicate data:
In this book, we will cover the first option InfoSphere Replication Server, which from now on, we will refer to as DB2 replication. The other options are outside the scope of this book.
In the world of DB2 replication, we have two main options—SQL replication and Q replication, both of which involve replicating between source and target tables. Event publishing is a subset of Q replication, in that the target is not a table but a WebSphere MQ queue. The choice of replication solution depends on a number of factors, of which the fundamental ones are:
Type of source
Type of target
Operating system support
The DB2 Information Center contains a table, which compares the three types of replication. This table can be used as a quick checklist for determining the best solution to a given business requirement (http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.iis.db.repl.intro.doc/topics/iiyrcintrsbsc.html).
The following figure shows the basic operations of SQL replication:

Updates to any tables are logged, and if the table is a registered table (TAB1
and TAB2
), then the SQL Capture program (Capture for short) reads the information from the DB2 log and inserts the row into a change data table (CD_<table-name>
)—there is one of these for each registered source table. The SQL Apply program (Apply for short) reads from these change data tables and updates the target tables (TAB3
and TAB4
).
In Q replication, we do not have the concept of change data tables, as shown in the following figure:

Any updates to registered tables, which the Q Capture program (Q Capture for short) detects are put onto a WebSphere MQ queue. The Q Apply program (Q Apply for short) then reads from these queues and updates the target tables.
In Event Publishing, there is no Q Apply and no target tables as shown in the following diagram:

Q Capture puts messages into the WebSphere MQ queues, and it is up to the consuming applications to destructively/non-destructively read from these queues.
We have three ways of administering a replication environment. We can use:
We recommend that when you are new to replication, you should use the Replication Center and once you are confident with the process, you can then progress onto the ASNCLP interface. For defining production systems, we recommend using the ASNCLP interface, because the commands can be scripted.
The ASNCLP interface generates SQL, which is run against the appropriate control tables to define and administer the replication environment. Therefore, in theory, it is possible for us to write our own SQL to do this. However, the SQL can be complicated and manual coding could result in errors, and therefore we recommend not using this method.
The Replication Center GUI can be used to set up and administer a Q replication scenario. See Chapter 6, Administration Tasks, for details on accessing and using the Replication Center. The launchpad screen is shown next.
The Replication Center has a series of wizards, which are very useful if we are new to replication. The default wizard screen is the launchpad screen, and it can be accessed from the main replication screen through Replication Center | Launchpad. The wizards take us through all the steps necessary to set up a replication environment.

Using the Replication Center, it is possible to generate an SQL script for a particular function. The ability for the Replication Center to generate ASNCLP scripts in addition to SQL scripts is being planned for a future release.
The ASNCLP interface (discussed in detail in Chapter 5, The ASNCLP Command Interface) allows us to enter commands from the command line, and more importantly, allows us to combine various commands into a script file, which can then be run from the command line. In this book, we will focus on ASNCLP scripts. It is supported on the Linux, UNIX, and Windows platforms. It is also supported on z/OS natively, through USS. We can also administer replication on z/OS from Linux, UNIX, and Windows system if we catalog the z/OS databases on the Linux, UNIX, and Windows system.
In the next section, we will look at the constituent components of Q replication.
We like to think that the Q replication architecture is made up of three interconnected layers: the DB2 database layer, the WebSphere MQ layer, and finally the Q replication layer—each layer needs to be set up and tested before we move on to the next layer. An overview of the Q replication process is shown in the following diagram:

The basic operating architecture of a Q replication system involves:
An application processing a row in a table and DB2 logging this operation
A Q Capture program calling the DB2 log reader to "process" the DB2 log and place rows that have been committed and that it wants to replicate onto a WebSphere MQ queue
A Q Apply program "reading" from this queue and applying the row to the target table
In this setup, we have two components that need to be installed—the Q replication code and the WebSphere MQ code. We will discuss the installation of both of these in some detail.
With the current packaging, the Q replication code for homogeneous replication already comes bundled with the base code for DB2—all we have to install is a replication license key. The license for InfoSphere Replication Server is called isrs.lic
and for InfoSphere Data Event Publisher the license is called isep.lic
. Use the DB2 db2licm
command to install the license key and to confirm that the license key has been successfully applied.
Turning to the WebSphere MQ component, we can use either WebSphere MQ V6 or V7 with DB2 replication.
The WebSphere MQ V6 Information Center can be found at http://publib.boulder.ibm.com/infocenter/wmqv6/v6r0/index.jsp.
The WebSphere MQ V7 Information Center can be found at http://publib.boulder.ibm.com/infocenter/wmqv7/v7r0/index.jsp.
For the procedure to install WebSphere MQ, consult the WebSphere MQ Information Center and search for install server. As an example, we will now take you through installing WebSphere MQ V6 on x86 64-bit Linux (which comes packaged as C87RUML.tar.gz
). We need to perform the following tasks:
1. As
root
, usegunzip
andtar
to unpack the WebSphere MQ packages:2. As
root
, the first task we need to perform is accept the MQ license, as shown next:3. Now we can install the base packages. As
root
, issue the following commands:4. If we want the WebSphere MQ sample programs, which include
amqsput, amqsget, amqsgbr, amqsbcg
, and so on (which we do!), then we have to install the following package:
For future reference, to uninstall WebSphere MQ, perform the following steps:
1. We can check which packages are installed using the following command:
2. We can check what version of WebSphere MQ we are running by using the following command:
This should give us an output similar to the following:
On UNIX systems, if we are running on a 64-bit system, then we need to add the WebSphere MQ library to the LD_LIBRARY_PATH
environment variable. If we do not do this, then when we try and start Q Capture (or Q Apply), we will see the following messages in the process log file:
The ASN0584E
message tells us to set the LD_LIBRARY_PATH
environment variable. To check the current setting of this variable, we can either list the current values of all environment variables, using the env
command, or we can list the value of this specific variable by using the echo
command and prefixing the variable name with a dollar sign:
We can temporarily set the value of this parameter (for the duration of the session in which the command was issued), using the following command:
If we ever need to remove the packages, we would use the commands as shown:
The Q Capture and Q Apply programs are discussed in detail in Chapter 2, The Q Capture and Q Apply programs. Typically, these programs will be installed on different servers, in which case we have to pay attention to the machine clock time on the servers.
The times on all servers need to be synchronized, because each captured record has a timestamp associated with it, and Q Apply will not apply in the future. Therefore, if the Q Capture server is ahead of the Q Apply server, then Q Apply will wait until it has reached the timestamp in the replicated record before applying it. If the Apply server time is ahead of the Capture server time, then we will not hit the "Apply will not apply in the future" problem, but the latency figures will be out by the time difference.
In the next section, we will look at the different types of Q replication.