|Product:||Syniti Data Replication, DBMoto|
|Summary:||How to append target tables (Union) during a Refresh replication|
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.
When DBMoto perform a Full Refresh, by default the target table gets truncated.
To append tables together:
- Configure Syniti DR/DBMoto with a replication for each source table linked to the same target table.
- Bypass the Truncate/Delete statement by intercepting the TableRefresh_onBeforeTruncate event and setting the CancelTruncate parameter to “True”:
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
- 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(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.
- Open the Replication Properties dialog.
- On the General tab, check the Use Script option and click the Script button.
- Add the following lines to the VB.NET code:
Public Override Sub Refresh_onBeforeTruncate(ByRef CancelTruncate As Boolean)
CancelTruncate = True
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.