Replication Transformations Between Non-Identical Tables

Data Source Solutions DSS can replicate data between tables with identical structure. It can also perform transformations when replicating data between non-identical tables. 

Replication between different DBMS (e.g., between Oracle and SQL Server) does not necessarily count as a "transformation" since DSS will automatically convert between the data types as necessary.

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

• Declarative actions, which add special DSS actions to the channel. These actions can be defined on the capture side or on the integrate side. For example:
  • Capture side action ColumnProperties with parameter CaptureExpression="lowercase({ colname })" can be used to perform an SQL expression whenever reading from column colname. This SQL expression can also do a sub select (if the DBMS supports that syntax).
  • Capture side action Restrict with parameter CaptureCondition="{ colname }>22" restricts DSS to only capture 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 with parameter IntegrateCondition="{ colname }>22" restricts DSS to only apply certain changes.
• Injection of blocks of business logic inside DSS's normal processing. For example, action DbObjectGeneration shows how a block of user-supplied SQL can be injected inside the procedure used to update a certain table.
• Replacement of DSS's normal logic with user-supplied logic. For more details, see DbObjectGeneration.
• Using an SQL view on the capture database. This means the transformation can be encapsulated in an SQL view, which DSS then replicates from. See example section DbObjectGeneration .
• In an agent plugin. An agent plugin is a user supplied program which is defined in the channel and is then scheduled by DSS inside its capture or integration jobs. See section AgentPlugin .