Home / Repstance User Guide / CONFIGURE REPLICATION WITH REPSTANCE / Objects and Data Transformation

Objects and Data Transformation

Data Transformation is used to change table, column definition or to implement custom data processing logic. The transformations can be configured for both Capture and Apply processes (see Objects Mapping and Possible Transforms for more details).

Rename Schema

This example show how to setup replication for “dbo” schema and enable global transformation rule renaming “dbo” schema to “public”.

Run the following command to prepare Source and Target Databases:

[http://localhost:8796/] repcli>prepare database=source dbtype=mssql server=10.20.30.1 port=1433 user=sa password=xxx dbname=MAPDB

[http://localhost:8796/] repcli>prepare database=target dbtype=postgresql server=10.20.30.2 port=5432 user=postgres password=xxx dbname=MAPDB

Run the following command to create Capture and Apply Processes:

[http://localhost:8796/] repcli>prepare process=capture id=8 dbtype=mssql server=10.20.30.1 port=1433 user=sa password=xxx dbname=MAPDB name=MAPCAP1 dmlinclude=dbo.% ddlinclude=dbo.% map=1,rule=(dbo.%:public.%) autostart=1

[http://localhost:8796/] repcli>prepare process=apply id=8 dbtype=postgresql server=10.20.30.2 port=5432 user=postgres password=xxx dbname=MAPDB capturename=MAPDB ddlprocessing=map autostart=1

The “map=1,rule=(dbo.%:public.%)” transformation rule instructs any linked Apply Processes to change name for all objects in the “dbo” schema to “public”.

The “ddlprocessing=map" is to apply the transformation rule to any DDL statements also (see PostgreSQL Apply Parameters for the details).

Rename Table

The following example shows how to change the Apply Process configured above to rename “public.Customer” table to “public.Clients”:

Run the following command to stop the Apply Process:

[http://localhost:8796/] repcli>stop process=apply id=8

Run the following command to add rule to rename “public.Customer” table to “public.Clients”:

[http://localhost:8796/] repcli>alter process=apply id=8 map=1,rule(public.Customer:public.Clients)

and re-run the process:

[http://localhost:8796/] repcli>run process=apply id=8

Rename Column

The following example shows how to change the Apply Process to rename “public.Customer.ID” column to "CLIENT_ID”.

Stop the Apply process and run the following command:

[http://localhost:8796/] repcli>alter process=apply id=8 map=2,rule=(public.Customer.ID:public.Clients.CLIENT_ID)

Run the Process.

Remove Column

The following example shows how to change the Apply Process to ignore the “public.Customer.SQL_ID” column in any SQL statements.

Stop the Apply process and run the following command:

[http://localhost:8796/] repcli>alter process=apply id=8 map=3,rule=(public.Customer.SQL_ID:public.Customer.NULL)

Run the Process.

Change Column Data Type

The following example shows how to alter the Apply Process to change data type for any “UNIQUEIDENTIFIER” columns to VARCHAR(64):

Stop the Apply process and run the following command:

[http://localhost:8796/] repcli>alter process=apply id=8 map=4,rule=(public.%.%.[TYPE=UNIQUEIDENTIFIER]:public.%.%.[TYPE=VARCHAR;LEN=100])

Run the Process.

Add New Column

The following example shows how to add new “SOURCE_SYS VARCHAR(64)” column to all the tables and populate it with ‘REPLICATED FROM SQL SERVER’ constant value:

Stop the Apply process and run the following command:

[http://localhost:8796/] repcli>alter process=apply id=8 map=5,rule=(public.%.NULL:public.%.SOURCE_SYS.[TYPE=varchar; LEN=64;DATA=\"'REPLICATED FROM SQL SERVER'\"])

Run the Process.

BI-Direction

DATABASE CONFIGURATION COMMANDS