A Fast Way to Get Started with Q Replication for DB2 Active-Active Databases

The instructions in these posts are for two databases on a single Windows system. You can modify the instructions to work for two different systems or for UNIX/Linux systems with a few changes (provided at the end of this post).

In the following sections, you'll define, validate and run an active-active Q Replication configuration.

 
First, Let's Configure Everything Needed to Replicate Data:
C1) Bring up a DB2 Command window, create a temporary directory called myscripts under say C:, and switch to it:

If you use two separate systems, follow the instructions provided in Running on Separate Systems (separate setup). Once you are done, go directly to step C4 (skip steps C2 and C3) below. 

You will run each program in its own DB2 Command window* (if you do this under Windows 7, make sure you read the footnote):

If you use two separate systems, the command above is started on System 2. 
 

Replication is now running between SITE1 and SITE2! Let's complete this exercise with a few DB2 updates and queries.

The query returns 20 projects for Michael Thompson of the Planning department at SITE1. To see the results for SITE2, substitute SITE2 for SITE1. You will get the same result.

If you use two separate systems, follow the instructions provided in Running on Separate Systems (connect update) to add the userid and password syntax necessary to connect to the SITE2 database from System 1.

The query should now return 20 projects all for Maria Perez. As expected,  you will see the same result for SITE1 and SITE2 as the active-active configuration replicates data in both directions.

To do this, you will also run Q Replication on each system as in the following graphic:

1) On System 1, create the SITE1 database by issuing three commands from a DB2 command window on System 1 as follows:

2) Login to System 2 by using the same logon (DB2 userid) as the one you use for System 1.

This step is required to ensure that all tables will be created with the same schema across the SITE1 and SITE2 databases.

3) On System 2, bring up a DB2 Command window, create a temporary directory called myscripts under say C:, and switch to it:

4) On System 2, create the SITE2 database by issuing three commands from the newly created DB2 command window as follows: 

5) Catalog the SITE2 database on System 1 by issuing two commands from a DB2 command window on System 1 after substituting the correct ip address and port number for System 2 as follows:

Running on Separate Systems (asnclp activeqset update)

To provide the asnclp activeqset command the right credentials to connect to the SITE2 database, add an id and password clause to the 'set bidi node' statements of the activeqset.asnclp script as follows:

In this blog, the same userid is used to connect to SITE1 and SITE2 so 'id2' is the same userid you use to connect to SITE1. 'pwd2' can be set to any password you need and may be different than the password associated with the userid for SITE1.  

Once you are done with the update, please go back to step C6 of the blog. 

Running on Separate Systems (asnclp activeqchk update)

To provide the asnclp activeqchk command the right credentials to connect to the SITE2 database, add an id and password clause to the 'set server'statements of the activeqchk.asnclp script as follows:

The 'id2' and 'pwd2' should be the same userid and password provided in the section above (asnclp activeqset update).

Once you are done with the update, please go back to step V2 of the blog. 

Running on Separate Systems (db2 connect update)

Use the following connect statement to include the credentials to connect to SITE2 from SITE1, as follows: 

The 'id2' and 'pwd2' should be the same userid and password provided in the sections above (asnclp activeqset update and asnclp activqchk update). 

Once you are done with the update, please go back to step R6 of the blog. 

Three scripts are introduced in this post for a single system deployment: 

First script: The createdb.bat File 

You can also download the contents of the file (add file extension '.bat' if you run on Windows) here: createdb

If you are running on Unix/Linux, you will need to update the dbpath, logarchmeth1 path and backup path appropriately. 

• The create replqmap statement associates MQ objects with a replication queue
  
  • for active-active configurations, two replication qmaps are defined, one for each direction
  • MQ defaults in this statement are the same as the defaults created by the 'Create MQ Script' command of the previous post
• The create qsub statement creates subscriptions for all tables listed in the 'set table' statements
  • for active-active configurations, two subscriptions are defined for each table, one for each direction
  • in this example, conflicts are detected but their resolution is not enforced (asnclp defaults). Conflicts may arise if an update on SITE1 conflicts with an update on SITE2.
  • the 'from node' clause in this example specifies not to load SITE2 before replicating change data. This is because the db2sampl command populates SITE2 with data. In active-active configurations, this is often a best practice (configuring a site for active-active often includes loading it with data before starting replication).

--