0

Please provide an example configuration of Transformation of a Single Table to Multiple Tables

Practical Scenario: I have a corp node and store node. I have two tables customers and imp_customers in the corp node, I have one table customers in the store node.

I have to do a transform from customers node in store to customers and imp_customers in cloud node. Please give an example configuration of the above transform in SymmetricDS 3.5 using sym_transfrom_table and sym_transform_column.

Binyamin Melepat
  • 59
  • 11

2 Answers2

1

To do a transform from a single table to multiple tables,
Step 1:- First setup a trigger for the table and link it to a router (Please ignore if already have triggers)
Step 2:- Create a row in SYM_TRANSFORM_TABLE, where we specify the direction of data, when to perform the transform, and the tables involved. 

 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 
    ('customers_to_imp_customers', 'store', 'corp',
    'EXTRACT','customers', 'imp_customers', 'NONE', 'SPECIFIED'),
    ('customers_to_customers', 'store', 'corp',
    'EXTRACT','customers', 'customers', 'NONE', 'IMPLIED');

Step 3:- Create rows in SYM_TRAMSFORM_COLUMN, where we specify the columns which we want to transform and the type of transform

insert into SYM_TRANSFORM_COLUMN
(transform_id, include_on, source_column_name, target_column_name, pk, transform_type)
values
('customers_to_imp_customers', '*', 'id', 'id', 1, 'copy'),
('customers_to_imp_customers', '*', 'name', 'customer_name', 1, 'copy'),
('customers_to_customers', '*', 'id', 'id', 0, 'copy');
Binyamin Melepat
  • 59
  • 11
0

I followed the instructions above and accomplishment that only works when the two columns have the same name. I leave my settings and I hope any suggestions. Greetings.

insert into sym_trigger 
(trigger_id,source_schema_name,source_table_name,channel_id,
    last_update_time,create_time)
values('tg_contacts_tbl_bksms','dbo','Contacts','ch_contacts_tblbksms',
    current_timestamp,current_timestamp);

insert into sym_router 
(router_id,target_schema_name,target_table_name,source_node_group_id,
    target_node_group_id,router_type,create_time,last_update_time)
values('rt_contacts_tbl_bksms','SIREN','TBL_BKSMS','SQLServer',
    'Oracle','default',current_timestamp,current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,initial_load_batch_count,
    last_update_time,create_time)
values('tg_contacts_tbl_bksms','rt_contacts_tbl_bksms',1,0,
    current_timestamp,current_timestamp);

insert into sym_transform_table
(transform_id,source_node_group_id,target_node_group_id,transform_point,
    source_schema_name,source_table_name,target_schema_name,target_table_name,
    delete_action,column_policy) 
values('transf_contacts_tbl_bksms','SQLServer','Oracle','EXTRACT',
    'dbo','Contacts','SIREN','TBL_BKSMS',
    'NONE','SPECIFIED');

insert into sym_transform_column
(transform_id,include_on,source_column_name,target_column_name,pk,transform_type)
values('transf_contacts_tbl_bksms','*','ContactID','BKSM_ID',1,'copy');
Adrián Misael
  • 1