Stelo Technical Documents

Using a Partition Key for Many-to-One Replication

Last Update:26 January 2023
Product: SQDR
Version: 6.1x and later
Article ID: SQV00DR040

Abstract

Notice: The techniques in this document require SQDR v6. If you are using SQDR v5, refer to the archived 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 schemas 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 unique value (e.g. @SCHEMA or @DSN) 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).

Note that there may be multiple partition columns, using @SCHEMA or @DSN, or a derived column expression that evaluates to a character constant.

Example

Schemas 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) SchemaID VARCHAR(128) NOT NULL PRIMARY KEY.

This example assumes that the data is stored in two different schemas in the same source databases; if the data is stored in two different databases, use @DSN rather than @SCHEMA as the Partition Key.

 

Procedure

1. Create the Source(s) and the Destination.
2. If desired, create one or more incremental groups. In the case of multiple schemas on the same DBMS system, you only need one incremental group.
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:
   • Destination DDL Options: Create if does not exist, else use existing.
   • Delete existing data before replication
     
   • Use Native Loader (or Insert using ODBC)
   • Strict Apply Rules (default).
     
   4. On the columns table, select Insert Derived Column
   5. Enter:
   • Expression:@SCHEMA.
   • Column Name: SchemaID
   • Data Type VARCHAR
   • Precision 128
   • 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;@SCHEMA;SchemaID;varchar;128;0;FALSE

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

6. Complete the creation of the subscription. This will create and populate the destination table.

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 (SchemaID).
   
   
   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:

<REPLACE THIS>
Starting touch-up of destination table: Index INVENTORYIR created over ("SchemaID", "PRODUCTID")
Primary Key INVENTORY_PK created over ("SchemaID", "PRODUCTID")

6. Examine the resulting destination table: Verify that the new derived column is present, and that it is part of the primary key and that it has been populated with the schemaID.
   
7. Create and run the subscription for the second source in a similar manner.

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

Any incremental changes will now affect only the rows associated with the related schema.