This is part two of lessons learned during a recent DB2 SQL Replication engagement.
After registering a source table for replication and creating an Apply Control server, you need to create a new Subscription set under that Apply Control server. This is on the target side of your replication environment. I learned a few things about the options you have to sort through.
When you right click on an Apply Control server and go to add a Subsription set there are many options. One option to consider is ‘Data blocking factor’. In the event of a network problem that causes a disconnect and captured data backs up, the data blocking factor tells Apply to process 20 minutes of changed data at a time. If it’s more than 20 minutes, Apply will break it into chunks to process with less time and resources. This keeps a backlog of changed data from killing your system while it tries to replicate.
From the other tabs on the ‘Create Subscription Set’ menu you can see the source to target mapping of columns from source to target. You usually don’t want to change this when first starting out. If a column is later added to a source table, you can go back and map it to a new column on the target. The other tab is to schedule how often replication executes. For our testing we used time-based replication at 1 minute intervals. A recommended time interval may be 5 minutes or 10 minutes based on your application needs.
Add a member (target table) to a subscription set
NOTE: Stop Apply or at least deactivate the subscription set before adding a member.
After creating and configuring a Subsciption set, adding a member can be done either by right clicking on a Subscription set and selecting ‘Add Member’ or from the ‘Properties’ window. The view below is from ‘Add Member’.
We used CCD target tables that will only contain data that has been changed. Select the ‘Add’ button to select a table that has been registered, change the Target_name in this window to the desired name, then select the Change button to make more configurations.
The Change button takes you to the CCD properties tab such as this:
We’ve selected the following options and I wanted to share the details about these options that I learned:
- Appends a separate row for each change that is made to a row in the source object (CONDENSED=N)
- This option puts a single row of data in the target table for every change to the data on the source. This is useful for logging.
- This will not work if you have a unique index or primary key. There will be errors due to key constraints because several changes to a row with a unique key could happen in one replication process.
- Use CONDENSED=Y when a primary key or unique index is needed on the target table. With Condensed=Y only the last change during the replication run is applied and constraints are not violated.
- The TARGET_CONDENSED column in the SUBS_MEMBR table will be set to N for not condensed or Y for condensed. Update these values to Y from N if a unique index or primary key are put on the target table.
- If a Unique index or Primary Key is placed on a Target table. You must make sure TARGET_CONDENSED=Y in the SUBS_MEMBR table and the columns with keys must have the IS_KEY=Y in the SUBS_COLS table for that member.
- Join the CD and UOW table.
- This is NOT necessary if the Capture and Apply servers are running DB2 V9.5 or later. Earlier versions of DB2 need to get the timestamp from the UOW table. We had to select this during our setup for the Registered Views because their target tables are on a DB2 V9.1 windows server. We learned from IBM that if you don’t join the CD and UOW tables and your target is V9.1 then Apply will not run due to mismatch on the LOGMARKER column .
- Selecting the ‘Join’ box sets the value of the TARGET_STRUCTURE column in both the SUBS_MEMBR and PRUNECTRL tables to 3. Not selecting the box makes the value = 9.
- When Apply and Control are both running on V9.5 or later, do NOT select this box. This will boost performance since joins are costly.
- If you upgrade your DB2 V9.1 server to V9.5 or 9.7 you can follow the linked instructions to disable the join as it’s no longer needed.
This was a quick summary of lessons learned about replicating Consistent Changed Data. If you need help setting up your replication environment, please give us a call.