StarQuest Technical Documents

SQDR and SQDR Plus:
Using Partitioned Snapshots to baseline a large table

Last Update: 21 October 2015
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL024

Abstract

This technical document describes a specialized technique that can be used to perform a baseline on a very large volatile table.

In normal operations, creating and running an incremental subscription performs the following operations:

  1. The SQDR Plus agent is notified of the subscription and starts monitoring the table for changes. The changes are staged to the local DB2 LUW staging database.
  2. SQDR begins a baseline copy of the table as it existed at the start of the replication. This phase of the operation is indicated by a green up arrow icon, and is performed as a single thread. This may take a long time if the table is large.
  3. After the completion of the baseline, the icon changes to a green circle with a black dot; this indicates that the updates accumulated during the time of the baseline are being applied.
  4. Eventually, the icon changes to a solid green circle, indicating that the catch-up phase has completed and that additional updates are being applied immediately.

In rare occurrences where the amount of changes to the source table during the time of the baseline is large, the replication system is overwhelmed and is not able to catch up in a timely manner. This document describes a technique of breaking the baseline into a group of partitioned snapshots that can be run simultaneously to speed up the baseline process.

 

  1. Create a new Incremental Group for the large table. Use “Force RRN” on the General page and “On Demand” on the Schedule page.





  2. Create a new incremental subscription for the large table and add it to the group in #1 above. Specify “Use existing table for baseline”, “Truncate table before replication” and “Manual synchronization”.



  3. Create a Snapshot group for the large table. Set the schedule to run “On Demand”.
  4. Create snapshot subscriptions. Specify “Use existing table” and “Append replicated rows to existing data”. Each subscription should Include a derived RRN column.



  5. Partition the table using the criteria for each subscription.





  6. Run the IR group. This will prepare the destination table by truncating all data.
  7. Run the Snapshot group until all subscriptions complete.
  8. Run the IR group.


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.