SymmetricDS syncronizing two tables with different names

While working with our newly discovered SymmetricDS tool, I realized that their manual does not clearly state how to synchronize two databases if their table names do not match. That might happen while matching tables between databases of two different companies, each one with its own table name policies. Blame those Database Administrators!

Here is how I accomplished synchronized two tables with different names:

First, I did the usual configuration: I defined two node groups A and B, defined a link between those node groups, defined a channel and a router for each direction (A to B  and B to A).

The description of triggers requires more configuration effort: Since the table names are different on each database, and since each trigger definition is bound to a table name, I had to define one trigger for each table name. Here, I suppose to databases: A and B, where the equivalent tables are called TableA and TableB.

insert into sym_trigger
   (trigger_id,source_table_name,channel_id,last_update_time,create_time)
values
   ('TriggerA','TableA','channel',current_timestamp,current_timestamp),
('TriggerB','TableB','channel',current_timestamp,current_timestamp);

As usual, the trigger needs to be associated with a router. Additional care is required: TriggerA is applicable only on database A and the reported changes are meaningful to be routed to database A to B. TriggerB is handled similarly.

insert into sym_trigger_router
   (trigger_id, router_id, initial_load_order, create_time, last_update_time)
values
   ('TriggerA', 'routerAtoB', 1, current_timestamp, current_timestamp),
('TriggerB', 'routerBtoA', 1, current_timestamp, current_timestamp);

Then, a table transformation must be defined: From node A to node B, table is to renamed from TableA to TableB. And from node B to node A, table is to renamed from TableB to TableA. The 'IMPLED' column policy means that all columns are to be copied without changes. I assume that both tables have the same column names and types. The delete action is ‘DEL_ROW’ and is not the main subject of this article. I prefered to run the transformation during the ‘Load’ transformation point.

insert into sym_transform_table
   (transform_id,source_node_group_id,target_node_group_id,transform_point,source_table_name,target_table_name,delete_action,column_policy)
values
   ('TransfAtoB', 'nodeA', 'nodeB', 'Load', 'TableA', 'TableB', 'DEL_ROW', 'IMPLIED'),
('TransfBtoA', 'nodeB', 'nodeA', 'Load', 'TableB', 'TableA', 'DEL_ROW', 'IMPLIED');

2 Responses to SymmetricDS syncronizing two tables with different names

  1. David says:

    Great! I am new with this tool, and I didnt know how to do this exactly, thank you

  2. vincent says:

    Hi daniel,

    How to rename nodeA & nodeB under sym_transform_table? it needs creation of table name in sym_node table first?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: