What is Q Replication real-time monitoring?

IBM WebSphere Information Integrator Version 8.2 introduced Q replication and event publishing for DB2® Universal Database™ (DB2 UDB) tables by extending the replication support of WebSphere Information Integrator. Both Q replication and event publishing consist of the QCapture component detecting change to DB2 tables and placing those changes on a WebSphere MQ queue. In Q replication, those messages are then read by the QApply component and the changes applied to target tables. In event publishing, a user-written application consumes the messages.

Once you have Q replication configuration defined and executing, you may be looking for a way to monitor its execution in a simple visual manner. Some important metrics are:

• Latency. End-to-end latency is the time it takes from the point in time when an update occurs at a source table until that update is applied to the target. This includes the time it takes to capture the change, transport it to the target system using MQ, and apply the change at the target. In a well-tuned Q replication setup, end-to-end latency can be a couple of seconds or less.
• Throughput. This is the number of row updates per second that are being transported.
• QCapture and QApply status. QCapture and QApply are the programs that perform Q replication. It is vital to know if either of them terminate for any reason.

The Q replication architecture consists of two separate programs, QCapture on the source system and QApply at the target. Because of this, the Replication Live Monitor also consists of two monitors, one for QCapture and one for QApply. If you only want to monitor end-to-end latency, then only the QApply monitor needs to be used. The QCapture monitor is primarily used for event publishing, where there is no QApply.

Do not confuse the Replication Live Monitor with the Replication Alert Monitor. The Alert Monitor ships as part of Q Replication and will monitor QCapture and QApply in the background. The user defines values to the Alert Monitor, and if those values are exceeded, the Alert Monitor sends a notification to the user. But the Alert Monitor has no ability to display the current activity of the Q replication programs.

Back to top

Installation

The Replication Live Monitor can be installed independent of where the Q replication components are installed. For example, you can install the Live Monitor on a local desktop computer monitoring a server in another location. In addition, the monitor will work with any version of Q replication. The only prerequisites are:

• A Java™ Virtual Machine must be present. The monitor is written in Java, requiring a JVM to execute. It has been tested with Java 1.3 and 1.4, and should work with most JVMs. You can enter java -version from a command line to see if a JVM is available. Note that if the monitor is installed on the same machine as the Q replication administration tools (Replication Center), then a JVM is already present in \sqllib\java\jdk\jre\bin.
• JDBC connectivity to the Q replication metadata. Both QCapture and QApply store their metadata in SQL tables. The QCapture monitor needs connectivity to the QCapture metadata tables, while the QApply monitor needs to access the QApply metadata.

Windows®

Use the following steps to install the monitor on a Windows system.

1. Create a directory to hold the monitor. An example is \replMonitor .
2. Unzip the attached zip file into the directory.
3. Edit the QCaptureMonitor.bat and QApplyMonitor.bat files to set the environment variable DB2PATH. This variable is used to find the JDBC drivers contained in db2java.zip.

Linux®, AIX®, and other UNIX® systems

The monitor should work fine on any system that has a JVM. Use the following steps to install the monitor on a non-Windows system:

1. Create a directory to hold the monitor. An example is \replMonitor .
2. Unzip the attached zip file into the directory.
3. Add the replMonitor.jar file into the CLASSPATH used for the JVM. In addition, insure that the DB2 JDBC drivers are part of the classpath (usually shipped in db2java.zip).
4. Start the JVM with QApplyMonitor or QCaptureMonitor as the initial class.

Back to top

QApply monitor

The QApply monitor is started by running QApplyMonitor.bat from a command line. Make sure the current directory is where you unzipped the monitor files. The bat file takes the following parameters:

• alias. This is the DB2 database alias where the QApply metadata information is stored.
• schema. (Optional, default = ASN) The schema qualifier of the QApply metadata tables.
• uid. This userid is used to connect to the QApply metadata tables. This userid must have authority to read the metadata.
• pwd. The password for the userid.

For example, QApplyMonitor.bat -alias SOURCE_DB -uid tjacopi -pwd hi1there would start monitoring the QApply that has metadata in the ASN schema at SOURCE_DB.

The QApply monitor monitors latency and throughput on a queue-by-queue level. Given that, when the QApplyMonitor starts up, a dialog is shown that allows queues to be selected for monitoring. More than one queue can be selected, and additional queues can be added later. If there only is one queue defined, the add queue dialog is not shown and the queue is automatically selected.

Now the QApply monitor will show, as appears in Figure 1. The queues being monitored are separated from each other by a horizontal grey line. In this case, the queue "B2R_2" is first, followed by "B2R." The first graph on the top shows end-to-end latency for queue B2R_2. The total height of the bar is the total latency. The bar itself is color coded to indicate the different components of end-to-end latency:

• QCapture latency is colored turquoise, on the bottom. This is the time it takes QCapture to read the update from the log and write it to the WebSphere MQ queue.
• WebSphere MQ latency is colored green, in the middle. This is the time between when QCapture puts a message on the queue and QApply takes it off.
• QApply latency is colored white, on the top. This is the time from when QApply pulls the message off WebSphere MQ to when the update is finally applied to the target.

To the right of the graph are a set of color-coded labels and numbers. The color coding of the label is to act as a legend for the graph. The phase "QApply" is in white, to remind that the white part of the bar graph is QApply latency. The number is the actual latency time in the latest monitoring interval. In Figure 1 above, queue "B2R_2" had a WebSphere MQ latency of 0.4 second in the last interval (rightmost bar).

Average latency and the trend are shown underneath the graph. Average latency is defined as the latency over the last 1000 intervals. The trend is how the average of the last 30 intervals relate to the overall average. If the average latency over the last thirty intervals is greater than the longer average, the trend shows as an up arrow to indicate that the trend is rising. A smaller value shows as a down arrow.

Below the latency graph is the throughput graph, measuring rows per second. This is the average rows per second over the length of the interval. Underneath the throughput graph is information about averages, with an overall average of the last 1000 intervals as well as a trend indicator.

The layout of the graphs can be changed to a horizontal layout as shown in Figure 2 through the "View" menu. Additional queues can be monitored by selecting File -> Add Queue. Queues can be removed from the monitor by right clicking on the graph and selecting Remove from the popup. Monitoring can be stopped and started by selecting Stop or Start from the "File" menu.

The monitor will also monitor the status of the QApply program. If QApply goes down, or hangs, the monitor will display a message as shown in Figure 3. As soon as QApply starts back up, the message will disappear and the graphs will resume scrolling. This is a very handy quick feedback as to the status of QApply.

Each bar on the graph represents a value for one refresh interval, which can be anywhere from one second to many hours. When new values appear on the right, old values are scrolled to the left. To adjust the interval, please see Changing the refresh interval.

Back to top

QCapture monitor

The QCapture monitor is very similar to the QApply monitor in that it monitors activity on a queue-by- queue basis. However, latency for QCapture is not queue-by-queue; rather, it is the amount of time that QCapture is behind DB2 when reading the log. DB2 is writing log records, recording the updates to the database, and QCapture is reading the log to discover those updates. The time between when DB2 writes a log record and QCapture reads it is the log latency. This value applies to all queues, so in the QCapture monitor it is shown once at the top. Only throughput is on a queue-by-queue basis, as shown in Figure 4.

Back to top

How it works

The QApply monitor is driven by reading the IBMQREP_APPLYMON table, and interpreting the results returned. When the QApply program is running, it writes latency, throughput, as well as other statistics to the APPLYMON table on an interval defined by the MONITOR_INTERVAL column in IBMQREP_APPLYPARMS. When the monitor program starts, it reads the APPLYPARMS table to discover the monitor interval that QApply is using to update the monitor table. Then once during that interval, the monitor program runs one query to fetch the latest values. The query has a WHERE clause that will only fetch the most recent values. Those values are then formatted and displayed in the graph. If no values are retrieved for two intervals in a row, QApply is assumed to be down, and the "QApply Inactive" message shows. As soon as any value is seen, QApply must be back up, and the inactive message goes away.

The QCapture monitor works the same way, except the tables used are different. The monitor data is split across two tables, IBMQREP_CAPMON and IBMQREP_CAPQMON. IBMQREP_CAPMON has information about the log latency, while IBMQREP_CAPQMON has the queue-by-queue throughput information.

Back to top

Changing the refresh interval

The refresh interval is how often the graph scrolls and shows a new bar. Since the graph cannot show information unless it has been written to the monitor tables, the refresh interval is governed by how often QApply or QCapture are writing monitor information. That value is defined by the MONITOR_INTERVAL value stored in IBMQREP_APPLYPARMS for QApply, and IBMQREP_CAPPARMS for QCapture.

The default value when the control tables were initially created is 300 seconds. Changing the value to 5 seconds will work much better for the monitor. You can change the value using any of several ways:

• By using Replication Center. Navigate the tree in Replication Center as follows: Click on Q Replication -> Operations -> Q Apply Servers and select the servers folder. Then select the appropriate server on the right-hand side, bring up the popup, and pick Change parameters -> Saved. From this dialog you can set the monitor interval.
• Directly from a db2 command line. Connect to the DB2 database from the DB2 Command Line Processor (CLP), and issue the following SQL: DB2 UPDATE ASN.IBMQREP_APPLYPARMS SET MONITOR_INTERVAL = 5 Be sure to replace the "ASN" with the appropriate schema qualifier.

If QApply is already running, it needs to be told about the new refresh interval since it is only read once at startup. Either QApply can be brought down and restarted, or the asnqacmd chgparms monitor_interval=5 command can be used to update a running program. The same information applies for a QCapture program, except the command is asnqccmd chgparms monitor_interval=5.

Back to top

Conclusion

By using the Replication Live Monitor, you will have the most important replication operational statistics, latency and throughput, at your fingertips. One glance can confirm if the replicaiton programs are active plus the latency and throughput trends. The Live Monitor does not impact the replication programs, and can be installed on a machine independently from any existing replication components.

Back to top

Visit the Q Replication Tools page for the latest download

The Replication Live Monitor has been enhanced to add the following features:

• Messages from the trace tables (IBMQREP_CAPTRACE, IBMQREP_APPLYTRACE, etc) can be seen in a web browser
• Exceptions from Q Apply are shown as red bars on the throughput graph and can be seen in a web browser
• There is a monitor for SQL Capture available

Download the latest version from the Q Replication Tools web page here.

Back to top

Download

Description	Name	Size	Download method
Q replication monitor executable and source	monitor.zip	142 KB	FTP | HTTP

Information about download methods

Resources

Learn

• Q Replication Tools: Explore the available free tools for Q Replcation.
  
  
• Introduction to Replication and Event Publishing: Learn more about Q Replication.
  
  
• Replication and Event Publishing Guide and Reference: This product documentation includes details of the structure of the monitor tables.
  
  
• Tuning for Replication and Event Publishing Performance: Learn how to tune a Q Replication configuration.
  
  
• Q Replication Information Roadmap: Grow your knowledge using this comprehensive list of Q Replication information.
  
  
• WebSphere Information Integrator Q replication (developerWorks, 2005): This article introduces the Q replication architecture and shows examples of how it works.
  
  
• Quick start for Q Replication to Oracle and Sybase (developerWorks, 2005): This article offers a step-by-step guide to set up the federated capability of Q replication.
  
  

Get products and technologies

• Trial version of WebSphere Information Integrator V8.2: Build your next development project with IBM trial software, available here for download directly from developerWorks.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• What is Q Replication real-time monitoring?
• Installation
• QApply monitor
• QCapture monitor
• How it works
• Changing the refresh interval
• Conclusion
• Visit the Q Replication Tools page for the latest download
• Download
• Resources
• About the author
• Comments

Next steps from IBM

Spring, JRuby, and Ajax development is easier with WebSphere Application Server - a smart Java 5 and J2EE Web services-based application server.

---

• Try: The no-charge WebSphere Application Server Community Edition is a pre-integrated, lightweight Java 5 application server built on Apache Tomcat and other best-of-breed open source software such as OpenEJB, Apache Axis, and Apache Derby.
• Article: The article leverage the Spring Framework and the WebSphere Application Server to improve your J2EE project productivity.
• Tutorial: See how the free WebSphere Application Server and XML can improve the efficiency of your JRuby on Rails and Ajax development.
• Buy: WebSphere Application Server - Express

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers