Home Programming IBM InfoSphere Replication Server and Data Event Publisher

IBM InfoSphere Replication Server and Data Event Publisher

By Pav Kumar-Chatterjee , Pav Kumar Chatterjee
books-svg-icon Book
Subscription FREE
eBook + Subscription €14.99
eBook €39.99
Print + eBook €48.99
READ FOR FREE Free Trial for 7 days. €14.99 p/m after trial. Cancel Anytime! BUY NOW BUY NOW BUY NOW
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
READ FOR FREE Free Trial for 7 days. €14.99 p/m after trial. Cancel Anytime! BUY NOW BUY NOW BUY NOW
Subscription FREE
eBook + Subscription €14.99
eBook €39.99
Print + eBook €48.99
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
  1. Free Chapter
    Q Replication Overview
About this book
Business planning is no longer just about defining goals, analyzing critical issues, and then creating strategies. You must aid business integration by linking changed-data events in DB2 databases on Linux, UNIX, and Windows with EAI solutions , message brokers, data transformation tools, and more. Investing in this book will save you many hours of work (and heartache) as it guides you around the many potential pitfalls to a successful conclusion. This book will accompany you throughout your Q replication journey. Compiled from many of author's successful projects, the book will bring you some of the best practices to implement your project smoothly and within time scales. The book has in-depth coverage of Event Publisher, which publishes changed-data events that can run updated data into crucial applications, assisting your business integration processes. Event Publisher also eliminates the hand coding typically required to detect DB2 data changes that are made by operational applications. We start with a brief discussion on what replication is and the Q replication release currently available in the market. We then go on to explore the world of Q replication in more depth. The latter chapters cover all the Q replication components and then talk about the different layers that need to be implemented—the DB2 database layer, the WebSphere MQ layer, and the Q replication layer. We conclude with a chapter on how to troubleshoot a problem. The Appendix (available online) demonstrates the implementation of 13 Q replication scenarios with step-by-step instructions.
Publication date:
August 2010
Publisher
Packt
Pages
344
ISBN
9781849681544

 

Chapter 1. Q Replication Overview

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.

 

Why do we want to replicate 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.

Overview of what is available today

In the IBM software world today, there are a number of options available to replicate data:

  • InfoSphere (formerly WebSphere) Replication Server

  • InfoSphere CDC (formerly the Data Mirror suite of programs)

  • The DB2 High Availability Disaster Recovery (HADR) functionality

  • Traditional log shipping

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.

The different replication options

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.

Replication toolset

We have three ways of administering a replication environment. We can use:

  • The Replication Center GUI

  • The ASNCLP command interface

  • Standard SQL

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

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 command interface

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.

 

Q replication constituent components


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. 1. As root, use gunzip and tar to unpack the WebSphere MQ packages:

    # gunzip C87RUML.tar.gz
    # tar -xvf C87RUML.tar
  2. 2. As root, the first task we need to perform is accept the MQ license, as shown next:

    # ./mqlicense.sh
  3. 3. Now we can install the base packages. As root, issue the following commands:

    # rpm -U MQSeriesRuntime-6.0.1-0.x86_64.rpm
    # rpm -U MQSeriesServer-6.0.1-0.x86_64.rpm
    # rpm -U MQSeriesSDK-6.0.1-0.x86_64.rpm
  4. 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:

    # rpm -U MQSeriesSamples-6.0.1-0.x86_64.rpm

For future reference, to uninstall WebSphere MQ, perform the following steps:

  1. 1. We can check which packages are installed using the following command:

    # rpm -q -a | grep MQSeries
  2. 2. We can check what version of WebSphere MQ we are running by using the following command:

    # dspmqver

    This should give us an output similar to the following:

    Name: WebSphere MQ
    Version: 6.0.0.0
    CMVC level: p000-L080610
    BuildType: IKAP - (Production)

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:

2009-09-02-12.47.39.730985 <ASNMQLOD:MQCONN> ASN0584E "Q Capture" : "ASN" : "AdminThread" : An error occurred while the program was dynamically loading the WebSphere MQ library "libmqm_r.so". Error code: "0x90000076", "Cannot load the specified library". Environment variable ASNUSEMQCLIENT is set to "".

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:

echo $LD_LIBRARY_PATH

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:

LD_LIBRARY_PATH=/opt/mqm/lib64:$LD_LIBRARY_PATH

If we ever need to remove the packages, we would use the commands as shown:

#rpm -ev MQSeriesRuntime-6.0.1-0.x86_64.rpm
#rpm -ev MQSeriesServer-6.0.1-0.x86_64.rpm
#rpm -ev MQSeriesSDK-6.0.1-0.x86_64.rpm
#rpm -ev MQSeriesSamples-6.0.1-0.x86_64.rpm

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.

Note

The machine clock time on all servers involved in replication should be synchronized.

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.

             
About the Authors
  • Pav Kumar-Chatterjee

    Pav Kumar-Chatterjee (Eur Ing, CENG, MBCS) has been involved in DB2 support on the mainframe platform since 1991, and on midrange platforms since 2000. Before joining IBM he worked as a database administrator in the airline industry as well as various financial institutions in the UK and Europe. He has held various positions during his time at IBM, including in the Software Business Services team and the global BetaWorks organization. His current position is a DB2 technical specialist in the Software Business. He was involved with Information Integrator (the forerunner of Replication Server) since its inception, and has helped numerous customers design and implement Q replication solutions, as well as speaking about Q replication at various conferences. Pav Kumar-Chatterjee has co-authored the “DB2 pureXML Cookbook” (978-0-13-815047-1) published in August 2009.

    Browse publications by this author
  • Pav Kumar Chatterjee

    Pav Kumar-Chatterjee (Eur Ing, CENG, MBCS) has been involved in DB2 support on the mainframe platform since 1991, and on midrange platforms since 2000. Before joining IBM he worked as a database administrator in the airline industry as well as various financial institutions in the UK and Europe. He has held various positions during his time at IBM, including in the Software Business Services team and the global BetaWorks organization. His current position is a DB2 technical specialist in the Software Business. He was involved with Information Integrator (the forerunner of Replication Server) since its inception, and has helped numerous customers design and implement Q replication solutions, as well as speaking about Q replication at various conferences. Pav Kumar-Chatterjee has co-authored the “DB2 pureXML Cookbook” (978-0-13-815047-1) published in August 2009.

    Browse publications by this author
IBM InfoSphere Replication Server and Data Event Publisher
Unlock this book and the full library FREE for 7 days
Start now