Q Replication Tips for Beginners
If you are new to Q Replication, you may be interested in some tips on making sure your first configuration is successful. This blog is for you. It is a list of tips and includes links to help you successfully configure, deploy and monitor a typical Q Replication scenario.
This blog assumes you've read the post titled A Fast Way to Get Started with Q Replication for DB2 Active-Active Database. For example, this post assumes that you are running replication between two systems as shown in the picture below. However the tips here apply to any Q Replication configuration:
The tips fall into four basic categories:
Use the InfoSphere Data Replication Dashboard!
Validate your WebSphere MQ ('MQ') setup
Validate your DB2 setup (for users new to DB2)
Reset your Q Replication Environment!
Expanding our Picture...
In system 1 and system 2, the DB2 instances, Q Capture/Q Apply pairs and MQ managers communicate with each other as shown in the diagram below:
The DB2 database names, MQ manager names and MQ port numbers in the diagram above are also referenced in the tips sections below (Tips #2 and #3). Q Replication tutorial can be found here
Tip #1: Use the InfoSphere Data Replication Dashboard!
Follow the InfoSphere Data Replication Dashboard Install Procedures to install the InfoSphere Data Replication Dashboard on one of your systems. Installation is quite fast and simple, and footprint (including embedded web server) is small.
Just open a web browser window pointing to the URL defined as part of the installation steps and the Q Replication Dashboard will give you:
- easy access to a consolidated view of all your Q Replication configurations
- for each configuration, live monitoring graphs display metrics like log latency, Q Capture throughput, etc.
- for each configuration, at-a-glance status: drill-down from Q Capture or Q Apply to Q subscription status and access DB2, MQ or Q Replication on-line Information Center topics as needed
for each configuration, troubleshoot problems and select recovery scenarios, detailed in Tip #4
Tip #2: Validate your MQ Setup!
The post named A Fast Way to Get Started with WebSphere MQ shows you how to use the ASNCLP CREATE MQ SCRIPT command to create MQ definitions for your active-active configuration. The command returns two MQ scripts, one script for system 1 and one script for system 2. Each script creates an MQ queue manager and its related MQ definitions. This section includes some tips on how to use the create mq script command. It also lists a few tools you will want to use to validate your MQ setup, should you encounter some problems.
Guidelines on how to use the ASNCLP 'CREATE MQ SCRIPT' command:
Do not use 'localhost' value as the mqhost parameter. Use the actual ip address or host name (site1.xx.com for mqserver 1 and site2.yy.com for mqserver 2). 'local' is a meaningless concept in bidirectional environments and creates problems.
If your test environment has a small file system, review the parameters associated with the crtmqm command in each MQ script output file. The '-lp' and '-lf' parameter values may be too large as the MQ log file sizes generated by ASNCLP assume a production environment. If necessary, update the values in each MQ script file before running the script.
Do not update the output MQ script before running it other than the crtmqm command parameters above. For example, each MQ transmit queue definition in a given MQ script output points to the other system's MQ listener port number and should not be changed.
In some cases, you may be asked to reuse an existing MQ environment: an MQ queue manager and possibly MQ administration and restart queues for a Q Capture server. If that is the case, you won't be able to use the ASNCLP script commands as provided and will need to make some updates. This may be error-prone so this section lists various tools and tips to validate and correct your MQ setup for your active-active configuration.
Tools you will want to use to validate (and correct) an MQ configuration for Q Replication:
WebSphere MQ Explorer
- at-a-glance list of all your MQ managers
- MQ queue manager status, channels, queues definition, listener port information etc.
ASNCLP 'Validate MQ Queues' command
- validates that your MQ definitions are correctly set up for a bidirectional scenario
- is described in the post A Fast Way to Get Started with Q Replication for DB2 Active-Active Database
- catches setup error that occurs with the administration queue; for example, the
DB2 ASN.IBMQREP_RECVQUEUES.ADMINQ column value on system 1 (or system 2)
should be the same as the local MQ administration queue name defined on
system 2 (or system 1); it should never match the local administration queue
system 1 (or system 2). The ASNCLP validate function alerts you if that is the case
- DB2 Replication Center 'Send Test Messages'
- validates that the two MQ managers defined for a Q Replication configuration can talk to each other by sending a test message between the two MQ queue managers
- note that the restriction stating that the Q Capture and Q Apply programs have to be stopped before doing the test has been lifted
- for more information on how to use the Replication Center to verify your MQ setup for Q Replication, watch the short video (seven minutes) that is available on ChannelDB2. If you use DB2 V10 and above, the Replication Center is available as a stand-alone DB2 center.
Tip #3: Validate your DB2 Setup!
If you are new to DB2 client/server configuration setup, review the guidelines below:
Update the DB2COMM environment variable on each system if it is not set to 'TCPIP'
Configure DB2's TCPIP communications on system 1 so that ASNCLP or any SQL command issued from a DB2 command window can access the SITE2 database on system 2.
Use a hostname instead of a dynamic IP address when you catalog the tcpip node for the remote DB2 (if you don't use static IP addresses) to prevent loss of connection over time. For example, on system 1, catalog the node for system 2 using site2.yy.com and port 50002.
ASNCLP runs on one system (system 1 in the active-active blog) and creates all the Q Replication definitions from that system. If you decide to run ASNCLP on system 2, you will need to catalog a tcpip node to access the SITE1 database from system 2.
In active-active configurations, the same table definitions must exist on each active site. The active-active blog uses the db2sampl command to create the same tables in SITE1 and SITE2. To ensure that all tables have the same schema, use the same userid to login to both systems.
Tip #4: Know How to Reset your Q Replication Environment!
If tables in the SITE2 database become out of synch with tables in the SITE1 database, you can reset your Q Replication environment by first stopping and then starting the corresponding Q subscriptions with the necessary load methods to populate the target tables:
If you need to reset your Q Replication environment for other reasons (some subscriptions in inconsistent state, messages were sent to a dead-letter queue or hardware failure could be some examples), use the Data Replication Dashboard's Recovery Advisor tab. It helps you troubleshoot and analyze your environment including four recovery scenarios. The remainder of this section highlights its key features.
Note that the information available in the Recovery Advisor is also available in the Troubleshooting section of the Q Replication Information Center.
The following features are available for you to reset your Q Replication environment:
- A 'Help me troubleshoot' wizard (Start Analysis button) and Four Recovery Scenarios (pull-down menu options):
The option to generate an html script (in HTML format) listing the recovery steps of a given recovery scenario before attempting any step (click on the green button 'Recovery Steps and Scripts' pull-down menu option 'Generate Script' or 'Email' on the upper right hand side corner):
The option to save and exit of a given recovery scenario and complete some of the remaining steps later (click on the green button 'Recovery Steps and Scripts' pull-down menu option 'Save and Exit' on the upper right hand side corner - see image above)
The option to restart or delete a saved (recovery scenario) procedure (the table at the bottom of the Recovery Advisor main page lists all saved procedures - if the table is empty, as it is below, there is no procedure work to complete later):