StarQuest: keeping data in motion

 

Stelo Technical Documents

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

Last Update: 7 March 2023
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.

Solution

  1. Create a new Incremental Group for the large table by right-clicking the Groups folder and selecting “Create Incremental Group”. The new group will contain only one incremental subscription.

Enter

  • Group Name and Comment (optional)
  • Select “Allow disable/enable indexes”
  • Deselect “Allow Checkpoint/Restart”
  • Specify "Maximum Subscriptions" (Concurrency) of 1


 
On the Group Advanced page:

  • Select appropriate Source and Destination
  • IMPORTANT: Deselect “Automatic Snapshot”
  • Click “OK” to save the group

  1. Create a new incremental subscription for the large table
  • Expand the new group, then right-click “Members” and select “Insert Member”.
  • After selecting the source table click “Next”
  • Choose the appropriate Destination, Database, Object Schema and Object Name. Select “Truncate table before replication” and “Manual synchronization” and “Strict Apply Rules”.
  • If the destination table exists, select Destination DDL Option “Use existing table for baseline”.
  • If the destination table does not exist or is to be dropped and recreated you should:
    Select “Create every time subscription is Saved”, then save the subscription. If prompted to “Run Baseline” select “No”.

 



After saving, view the properties of the subscription and change the Destination DDL Options to “Use existing table for baseline”.

 


Finally, save the subscription.

  1. Create a “snapshot” group.
  • Right-click the “Groups” folder and select “Insert Group”


  • Enter a Group Name and Comment (optional), deselect all “Indexes/Constraint” options and deselect “Allow Checkpoint/Restart”.

The “Maximum Subscriptions” value should be equal to the number of concurrent snapshots you intend to run for the big table. That is, if you intend to partition the source table into ten sections, you will be creating 10 snapshot subscriptions and the value should be 10.



  • Click “OK” to save the snapshot group.
  1. Create snapshot subscriptions
  • Pause the incremental group created in Step 1.

 

  • Right-click the incremental subscription created in Step 2 and select “Copy Member”.

  • Select “Convert to Snapshot” and add “_SS0” for Member Qualifier. Also select the “Apply To” Subscription checkbox. Then click OK.


After saving, the subscription will appear under the source.

  • Right-click the new snapshot subscription and select “Properties”. Select “Use Existing table”, “Append replicated rows to existing data” and “Use native-loader function, if available”.


  • Select the “Criteria” tab.


 

  • Enter criteria and then “Verify Criteria” (verifying may take some time i.e. do not verify all snapshots, just this first one).

The criteria is used to partition the table. We have found that function “MOD” is the best way to do this.

Syntax:

WHERE MOD(<expression>, x) = y
x= the number of partitions or snapshots you want to use to baseline the table
y= an integer specifying which partition to use

e.g.
1st snapshot
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=0
2nd snapshot
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=1
3rd snapshot
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=2
…
10th snapshot
WHERE MOD( RRN(QAUSER.APPSW19A), 10)= 9

MOD clause examples:

Db2 for i:
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=0

Oracle (use exactly as shown)
WHERE MOD( DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ,10)=00

SQL Server:
where abs(cast (cast(cast(substring(%%lockres%%,2,12) as varchar(20)) as varbinary(40)) as bigint)) % 10 = 0

  • After verifying criteria click “OK” to save the subscription.
  • Duplicate the first snapshot.
  • Right-click the new snapshot subscription and select “Duplicate”.


 

  • Give the new snapshot a name:

 

 

  • Continue to duplicate the first snapshot until the total number of snapshots is equal to the value of “x” in the “MOD” expression.
  • After all snapshots are created, view the properties for each snapshot and alter the criteria to match the partition number to “x”.


If the MOD expression indicates 10 snapshots, you should have entered the following criteria:
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=0
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=1
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=2
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=3
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=4
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=5
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=6
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=7
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=8
WHERE MOD( RRN(QAUSER.APPSW19A), 10)=9

  1. Right click the members folder of the Snapshot group and select “Insert Member”.


  • Add the new snapshots subscriptions to the group:

 

  1. Run the IR group. This will prepare the destination table by truncating all data.

 

  1. Run the Snapshot group until all subscriptions complete.
  2. Run the IR group a second time.
  3. Resume 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.