Replication Transformations Between Non-Identical Tables

HVR can simply replicate between tables with an identical structure, but it can also perform transformations when it needs to replicate between tables which are not identical. Note that replication between different DBMS (e.g. between Oracle and SQL Server) does not necessarily count as a "transformation"; HVR will automatically convert between the data types as necessary.

Transformation logic can be performed during capture, inside the HVR pipeline or during integration. These transformations can be defined in HVR using different techniques:

• Declaratively, by adding special HVR actions to the channel. These declarative actions can be defined on the capture side or the integrate side. The following are examples:
  • Capture side action
    ColumnProperties /CaptureExpression="lowercase({ colname })" can be used to perform an SQL expression whenever reading from column colname . This SQL expression could also do a sub select (if the DBMS supports that syntax).
  • Capture side action
    Restrict /CaptureCondition="{ colname }>22" , so that HVR only captures certain changes.
  • Integrate side action
    ColumnProperties /IntegrateExpression="lowercase({ colname })" can be used to perform an SQL expression whenever writing into column colname . This SQL expression could also do a sub select.
  • Integrate side action
    Restrict /IntegrateCondition="{ colname }>22" , so that HVR only applies certain changes.
• Injection of blocks of business logic inside HVR's normal processing. For example, section DbObjectGeneration shows how a block of user supplied SQL can be injected inside the procedure which uses to update a certain table.
• Replacement of HVR's normal logic using user supplied logic. This is also called "override SQL" and is also explained in section DbObjectGeneration .
• Using an SQL view on the capture database. This means the transformation can be encapsulated in an SQL view, which HVR then replicates from. See example section DbObjectGeneration .
• In an HVR "agent". An agent is a user supplied program which is defined in the channel and is then scheduled by HVR inside its capture or integration jobs. See section AgentPlugin .

HVR does not only apply these transformations during replication (capture and integration). It also applies these transformations when doing a compare or refresh between the source and target databases.