Request More Information

We are hiring!

Xtivia, Inc.
2013 North America Liferay Partner of the Year

Join our top-notch team of Enterprise Java and Liferay Portal professionals in our Austin, TX office or work remotely! Please check out our current Dice listings.

Recent Bloggers

Sushil Shirodkar
Posts: 1
Stars: 0
Date: 8/28/14
Luke Smith
Posts: 16
Stars: 0
Date: 8/27/14
Chris Shaw
Posts: 6
Stars: 0
Date: 8/21/14
Vamshidhar Srikantapuram
Posts: 11
Stars: 0
Date: 8/8/14
Barrie Shaw
Posts: 6
Stars: 0
Date: 8/4/14
Omar Yimaier
Posts: 4
Stars: 0
Date: 8/1/14
Bob Dietrich
Posts: 8
Stars: 0
Date: 8/1/14
Matt Wolinski
Posts: 2
Stars: 0
Date: 7/30/14
Richard Ngo
Posts: 1
Stars: 0
Date: 6/19/14
Kaushik Nagaraj
Posts: 8
Stars: 0
Date: 5/22/14
« Back

DB2 Update Anywhere Replication For Dummies.

Hi.  I recently finished a project for a client who needed Update-Anywhere Repliction on one table between 4 DB2 version 9.1 databases at different locations.   I thought I would share a high-level overview of the process I used, starting with 2-way replication between the first 2 databases.  

I started with one Master database and one Replica.  I set it up so that the Master could replicate changes to multiple Replicas as I added more.  It is simply a matter of using the recapture option at the Master so that changes that occur at one Replica are recaptured at the Master and forwared to the other Replica tables.  So, I turned on recapture at the Master and turned it off at the Replicas.

These are the basic steps I started with for one Master and one Replica.  The other two replicas were added in much the same way.

  1. I set up server communications by cataloging the Master node and db on the Replica and visa versa.  
  2. I used the Replication Center on the Master server for the rest of this process.  I first had to configure the 'Manage Passwords and Connectivity' section by adding each node and database with the id's and passwords.
  3. Create Capture control tables on the Master database using the Replication Center using the 'Quick' option.  
  4. Register a table on the Master for Replication.  I used all the defaults except I change the 'Conflict detection level to 'Standard'
  5. Create the Apply control tables on the Replica using the same method.
  6. Create a Subscription Set on the Apply control server (Replica).  When you configure the Source to Target mapping, you change the target type to 'Replica'
  7. After creating the source as a Replica, you will see that your Replica database also shows up as a Capture server now.  This is because both Apply and Capture will be running here so that changes can be captured and applied to the Master. 
  8. You can now open up the Capture Properties of the Replica table and uncheck the 'Capture changes from replica target table' box.  This should only be checked on the Master.
  9. Start Capture on the Master server
  10. Start Apply on the Replica
  11. Start Capture on the Replica

The Master database will only have Capture processes running.  Each Replica database will have both Capture and Apply.

There were only about 5k - 10k rows updated on this table per day so I didn't have a good load to check performance.  I can say that we had no problems with conflicts and scheduling replication to update every 2 minutes worked well.

Lesson Learned:  The table we replicated had a generated IDENTITY column.  This can't be set up to replicate to a Replica table.  I got around it by setting up the IDENTITY field so that it numbered from 1 - 999999 on the Master and 1000000 - 1999999 on the Replica.  As I added more Replicas I change the Identity start number to 2000000 as so on.  Then when setting up the Column Mapping on the Replica Apply database I didn't replicate the ID column.  It was simply generated upon insert. 

I found that using DB2 Replication Center was a pretty simple way to set up 2-way replication and then add more Replicas for Update-Anywere replication.  Starting Capture and Apply and reading logs was easier at the command line.

Let me know if you have any questions I could try to answer.  Thanks!






Trackback URL:

Anonymous User
It sounds like this is SQL Replication. Correct?
Posted on 12/5/12 10:34 AM.
You are correct.
Posted on 12/5/12 10:42 AM in reply to Anonymous User.
Anonymous User
Why did you have to have the identity columns with the ranges? Did you consider GENERATED BY DEFAULT and then have replication do upserts? The way you have it setup here, updates don't appear to be replicated properly. Perhaps you didn't have updates?
Posted on 12/5/12 10:48 AM in reply to Brian Fairchild.
I used this method based on research and testing. Updates were replicated. This IBM technote was one of the sources I based my testing on.
Posted on 12/5/12 1:06 PM in reply to Anonymous User.
Anonymous User
Thanks for that, but I'm not sure I see how an update to the target gets replicated back to the same source row. Unless, I suppose, there is some other unique key. I the CD tables may enable this somehow. I can't see it working in Q Replication without using the generated by default option.

Jim Herrmann
Posted on 12/5/12 1:30 PM in reply to Brian Fairchild.

Request More Information