|Product:||Syniti Data Replication, DBMoto|
|Summary:||VB.NET Example of a Script to filter records during mirroring for IBM i/iSeries/AS400 source tables|
In some cases it can be useful to add a filter to replications in order to propagate only records that have specific values (or ranges of values) in certain fields. In refresh mode, the Refresh Filter property can be used to achieve this purpose, but in mirroring mode you need to write a script to achieve the same functionality. In this article we’ll show how to write a replication script that filters data during a mirroring replication. Note that this example works on databases where the log information is complete with BEFORE and AFTER values such as IBM Db2 on i. For databases where the log information is less complete for non-primary key fields (Oracle and SQL Server), check knowledge base article 1629.
Let’s define a simple table to replicate from source to target:
Col. Name Type Nullable Primary Key
CUSTOMERID Integer N Y
NAME Varchar(20) Y N
COUNTRY Varchar(10) Y N
Suppose we want to filter only records that have the field COUNTRY = “Italy”. This means that only records with this condition will be inserted, updated or deleted in the target table. It also means that records that are updated will be handled depending on the value that the COUNTRY field already has, and as a consequence, an UPDATE can become an INSERT or a DELETE in the target table, if the value of this field changes in the UPDATE statement.
We will need to override the event Record_onBeforeMapping to drive the record propagation according to our needs. To facilitate the comparison with the string “Italy”, let’s also add a helper function, Compare, to evaluate if the value read from the log is Nothing and if not, to compare it with the searched string.
The onBeforeMapping event code will have to select among different cases: if an INSERT has been executed, we need to check if the value of the record after insertion in the field COUNTRY is different from “Italy”, in that case we’ll abort the INSERT; if an UPDATE has been executed, depending on the value of the COUNTRY field before and after the operation, we can decide whether to abort the UPDATE, or switch it to an INSERT or a DELETE; if a DELETE has been executed, the operation would be aborted only in cases where the value before was not “Italy”.
Here we show the full script class:
Public Class ReplicationScript : Inherits IReplicationScript
Private Function Compare (obj As Object, value As String) As Boolean
If obj Is Nothing OrElse IsDBNull(obj) Then
Return (value Is Nothing)
Return (String.Compare(obj.ToString().Trim(), value) = 0)
Public Overrides Sub Record_onBeforeMapping(recSource As IRecord, ByRef AbortRecord AsBoolean)
Select Case recSource.OperationType
If Not Compare (recSource.GetValueAfter("COUNTRY"), “Italy”) Then
‘ Insert aborted
AbortRecord = True
If Not Compare (recSource.GetValueBefore("COUNTRY"), “Italy”)
If Not Compare (recSource.GetValueAfter("COUNTRY"), “Italy”) Then ' xxx --> xxx
‘ Update aborted
AbortRecord = True
Else ' xxx --> “Italy”
‘ Change operation from update to insert
recSource.OperationType = enmOperationType.Insert
ElseIf Not Compare (recSource.GetValueAfter("COUNTRY"), “Italy”) Then ' ”Italy” --> xxx
‘ Change operation from update to delete
recSource.OperationType = enmOperationType.Delete
If Not Compare (recSource.GetValueBefore("COUNTRY"), “Italy”) Then
‘ Delete aborted
AbortRecord = True
- This filter will apply also in refresh mode (the Record_onBeforeMapping event is also called during refresh) therefore it is not necessary to use the Refresh Filter property to execute the filter during refresh.
- This script works on databases where the log information is complete with BEFORE and AFTER values. In some cases, like SQL Server and Oracle, not all BEFORE values are available during transactional replication and it will be necessary to resort to different methods (like, for instance, running a SELECT on the source table to retrieve the original values). Make sure that in such cases, the method used to retrieve the BEFORE value has to be consistent and should not be used if there is any chance of an inaccurate value during replication.