StarQuest Technical Documents

Using SQDR Plus/UDB for Bidirectional Synchronization

Last Update: 20 November 2010
Product: SQDR Plus for UDB
Version: SQDR Plus/UDB 3.68 or later
Article ID: SQV00PU005

Abstract

SQDR Plus/UDB provides a mechanism for bidirectional synchronization between two DB2 UDB for Linux, UNIX & Windows database systems. This mechanism can be used to provide a hot backup system that can quickly be moved into production if the primary system fails. After the primary system is recovered, any changes made on the secondary system will automatically be replicated to the primary system. Another use would be Load Sharing or Load Balancing of applications or user groups, although additional considerations may apply if both databases are being updated simultaneously as no built-in mechanism exists for collision detection and resolution.

Solution

Prerequisites:

• A Windows system configured to run SQDR. See the Quick Start Guide for StarQuest Data Replicator for installation instructions.
• SQDR Plus installed on both DB2 UDB LUW systems
• All tables involved in the replication must have unique constraints, such as primary keys or unique indexes, and no tables may have foreign constraints.

 

In the procedure below, we will designate one DB2 system as the primary (the “P” system) and one as the secondary (the "Q" system. The examples below show UNIX-style pathnames and commands; a similar procedure can be used if one or both of the DB2 systems is running on Windows.

This procedure was developed and tested where both DB2 UDB systems were running the same version of DB2 and running on the same operating system, but the same process should work between different versions of DB2 and different host platforms, as long as attention is paid to differences (for example, database backup/restore cannot be used to move the snapshot between different host platform architectures, and data types introduced in later versions of DB2 may not be available in older versions).

This procedure involves the use of several advanced features of SQDR and SQDR Plus. Refer to the SQDR Documentation Addendum for details on TableChecker (utility in the Tools subfolder), “Append replicated rows to existing data”, “Use unique indexes” and “Manual Synchronization” (options on the destination pane for IR subscriptions), “Use Unique Constraints" (option on the Advanced pane for an IR group), and the ability to copy IR subscriptions from one group to another. Refer to the SQDR Plus Documentation Addendum for details on filterUserID (setting in sqagent.properties), which is used to prevent replication loops.

1. SQDR Plus Installation on PRIMARY DB2 server (the “P” system):
   1. Verify prerequisites.
   2. Install SQDR Plus.
      
   3. Verify Capture Agent is running.
      
   4. Create a userID (e.g., SQDRP) that will be used by the SQDR Client to access this system, both as a source & as a destination. It will be used as a filtering UID to avoid replication loops.
   5. Edit /var/sqdrplus/<dbname>/conf/sqagent.properties and configure filterUserID=SQDRP.
      
   6. Restart Capture Agent (/var/sqdrplus/<dbname>/sqdrplus stop/start).
2. SQDR Plus Installation on SECONDARY DB2 server (the “Q” system):
   
   1. Verify prerequisites.
      
   2. Install SQDR Plus.
      
   3. Verify Capture Agent is running.
      
   4. Create a userID (e.g., SQDRQ) that will be used by SQDR to access the secondary system.
      
   5. Edit /var/sqdrplus/<dbname>/conf/sqagent.properties and configure filterUserID=SQDRQ.
   6. restart Capture Agent (/var/sqdrplus/<dbname>/sqdrplus stop/start).
      
3. SQDR and StarSQL Installation:
   
   1. Install SQL Server, if necessary.
      
   2. Install StarAdmin, bind packages on both DB2 UDB servers.
      
   3. Install StarSQL, configure ODBC data sources to both DB2 servers.
      
   4. Install SQDR.
      
   5. License all software. (StarSQL=20CC, SQDR Plus=2CCs, SQDR=1CC)
   6. Restart SQDR.
      
      
4. Insure that the database on the secondary machine is an identical copy of the primary:
   • One way to do this (if the two DB2 systems are of similar architecture, and you are planning to replicate all or a significant part of the database) is a database save/restore operation. This has the advantage of preserving table privileges and can be faster when copying a large database.
   • You can also make the initial copy using SQDR:
     1. Create a Snapshot Group of subscriptions SS_PtoQ with “Replicate Indexes” and schedule “On Demand”.
     2. Insert snapshot subscriptions of the tables on the primary system to be replicated to the secondary system; use the “Create when Run” destination option.
     3. Run the group SS_PtoQ.
     4. Define privileges on the replicated tables if necessary.
5. Define SQDR subscriptions:
   
   1. Create source and destination for each DB2 server, using the “P”-“Q” names to assist identification.
      
   2. Create IR Group “PtoQ” for PRIMARY -> SECONDARY. On the Advanced pane, select the checkbox “Use Unique Constraints."
      
   3. Create IR Group “QtoP” for SECONDARY -> PRIMARY. On the Advanced pane, select the checkbox “Use Unique Constraints."
      
   4. Create a set of IR subscriptions in Group PtoQ. On the destination pane, specify “Use Existing Table”, “Append replicated rows to existing data”, and “Manual Synchronization.” This effectively bypasses any baseline activity , and results in “Relaxed Apply Rules.”
      
   5. Pause both groups.
      
   6. Copy IR subscriptions from Group PtoQ to Group QtoP.
   7. Run Group QtoP.
      
   8. Resume both groups.
6. Test Replication:
   
   1. Update PRIMARY DB2 source tables, watch updates flow.
   2. Update tables on SECONDARY DB2 system and watch updates flow.
   3. Use TableChecker (in the Tools subdirectory of the SQDR installation) to verify that the two systems are in sync. See the SQDR Documentation Addendum for instructions on using TableChecker.
      

 

Site Map
Products | Trial Download | Customer Support | Partners | About StarQuest | Press & Events | Contact StarQuest

©Copyright 2010 StarQuest Ventures, Inc. All rights reserved.

Legal Notice • Privacy Policy
Email contact@starquest.com with site comments.