Stelo Technical Documents

Using a Partition Key for Many-to-One Replication (SQDR 5.x)

Last Update: 5 April 2019
Product: SQDR
Version: 4.50 and later
Article ID: SQV00DR040A

Abstract

Notice: This information is provided for customers using SQDR v5. If you are using SQDR v6 or later, we recommend using the current version of this technical document.

Goal: Create incremental subscriptions, replicating from multiple tables of similar DDL into a single destination. The source tables may exist on different schemas on the same source system, or they may exist on different source systems. The different source systems may even be of different DBMS types. For example, the sources may represent multiple stores or branches, and the destination may be a central repository used for analysis or inventory planning.

Issue: Relying on the standard method of using the source database's primary key or unique index is inadequate to avoid duplicate results.

Solution: For each subscription, add a derived column for each source table that evaluates to a constant unique value and mark it as a primary key. We will refer to this as the Partition Key. SQDR will create a composite Primary Key over both the original Primary Key and the new derived column (the Partition Key).

Example

Databases store1 and store2 each have a table INVENTORY containing these columns:

create table INVENTORY (PRODUCTID INT NOT NULL PRIMARY KEY, Description char(64), Quantity INT)

Every product may not be stocked in all stores, so the number of the rows in the source tables will vary, the product Description may vary slightly according to local preferences, and of course the Quantity will vary per store.

The desired destination table will contain the original three columns plus a new column (the Partition Key) StoreID INT NOT NULL PRIMARY KEY.

 

Procedure

1. Create the two Sources and the Destination.
2. If desired, create two incremental groups for the 2 replication pairs. On the Advanced tab, select Allow Upsert for Subscription Wizard Apply Defaults.
3. Create the first subscription:
   1. Under the first incremental group, right-click on Members and select Insert Member.
   2. On the source panel, select the table to replicate (INVENTORY)
   3. On the destination panel, select Baseline Replication Options:
   • Delete existing data before replication. We cannot use Truncate, since truncate isn't aware of WHERE clauses, and could delete data associated with other stores.
     
   • Null synchronization - DDL only. This is to create an empty table now. This will be changed later when we are ready to populate it.
   • Also verify that Incremental Apply Options is Allow UPSERT. If you create the subscription under the incremental group (rather than the source, Allow UPSERT should already be selected.
     
   4. On the columns table, select Insert Derived Column
   5. Enter:
   • Expression: 'S01'. This must be a constant value.
   • Column Name: StoreID
   • Data Type Char
   • Precision 3
   • Select the checkbox for Primary Key. This will automatically grey out the Nulls checkbox.
     

After creating the derived column, it should appear with a check in the Pkey column.

Alternately, you can create an import mapping script e.g. ManyToOne_StoreID.TXT containing:

dbo;%;PCOLUMN;'S01';StoreID;char;5;0;FALSE

and import it on the Destination panel. When you arrive at the Columns table, select Rebuild Column List; the StoreID column will appear.

6. Complete the creation of the subscription. This will create the destination table, but not populate it.

4. Examine the newly-created subscription:
   
   On the Columns tab, there will be checkmarks in the PKey column for both the original primary key (PRODUCTID) and the new derived column (StoreID).
   
   
   On the Creation tab, the CREATE TABLE statement will include the new derived column.
5. View the Replication Event (the history item designated with a checkbox within a circle); it should contain results similar to:

Starting touch-up of destination table: Index INVENTORYIR created over ("StoreID", "PRODUCTID")
Primary Key INVENTORY_PK created over ("StoreID", "PRODUCTID")

6. Examine the resulting destination table: Verify that the new derived column is present, and that it is part of the primary key
   
7. Modify the subscription: On the Destination panel, choose Use Existing table for baseline and Use native-loader function (or Insert using ODBC, as desired). The Other options remain at Delete existing data before replication and Allow UPSERT.
   
   
8. If you receive the warning Data type specified for StoreID is not the data type of that column in the destination table, you may dismiss it.
9. Run the incremental group to copy the existing content from the source table.
10. Examine resulting destination table and verify that the derived column has been populated with the storeID.
11. Create the subscription for the second source in a similar manner, with the exception of choosing Use existing table for baseline on the destination.

Since the destination table already exists with appropriate primary keys, you only need to run the second subscription once to populate the destination with the contents of the second source.

After running the second group, examine the destination table and verify that it contains content from both sources with an appropriate value in the StoreID column.

Any incremental changes will now affect only the rows associated with the related source/Store.