1420 - Appending Tables (Union) in a Refresh Replication

Q.

In a Refresh replication, how do I append tables together? When I use the same target table, the table is truncated before each refresh replication.

A.

When DBMoto perform a Full Refresh, by default the target table gets truncated.

To append tables together:

1. Configure Syniti DR/DBMoto with a replication for each source table linked to the same target table.
2. Bypass the Truncate/Delete statement by intercepting the TableRefresh_onBeforeTruncate event and setting the CancelTruncate parameter to “True”:

Example

A group of 3 companies have the same ERP software on DB2/400 (C1, C2, C3).

They would like to replicate data from the INVOICES table in database connections C1, C2 and C3 to a single target table:

  InvoicesTarget = InvoicesDB2(C1) + InvoicesDB2(C2) + InvoicesDB2(C3)

The INVOICES table has a field containing the company code, and there is no potential conflict with the primary key.

(If there is a problem, you can add a field to the target table to identify the source table and set the value to a constant value. This field must be in the primary keys list.) 

Steps to configure the replication

1. Configure Syniti DR/DBMoto with 3 replications linked from each source to the same target table (REPL1, REPL2, REPL3.)
   
   Without modifying the "CancelTruncate" behavior, a problem will arise at the beginning of the replication:
   
   Refresh(REPL1) OK
   Refresh(REPL2) deletes all the records of REPL1
   Refresh(REPL3) deletes all the records of REPL2
   
   For REPL2 and REPL3, add a replication script to each replication.
2. Open the Replication Properties dialog.
3. On the General tab, check the Use Script option and click the Script button.
4. Add the following lines to the VB.NET code:

  Public Override Sub Refresh_onBeforeTruncate(ByRef CancelTruncate As Boolean)

    CancelTruncate = True

  End Sub

To be completely sure of the order in which the replications are run (REPL1 then REPL2 then REPL3), create a replication group and add the replications to the group, then set the order of replication within the group. Alternatively, you can create a script to cancel truncation for all three replications and perform a manual truncation of the target table before running the full refresh.

The events below are raised before/after the refresh and can be used to modify the default behavior of a refresh replication.

• Refresh_onBeforeTruncate
• Refresh_onAfterTruncate
• Refresh_onBeforeRefresh
• Refresh_onAfterRefresh