topic Re: SCD implementaion in Talend 5.x +db2 db in Talend Studio

SCD implementaion in Talend 5.x +db2 db

Anonymous — Mon, 25 Jul 2016 10:09:01 GMT

Can SCD implementation in talend be used for synching up data between source and destination tables.
The source and destination tables has master and child tables with parent child relationships.
Any changes(insert/update/delete) on source's parent and child tables needs to be sycnhed up with destination parent and child tables.
Source table shall have an indication KEY column for identifying changed data
Any inputs on this will be appreciated.

Re: SCD implementaion in Talend 5.x +db2 db

Anonymous — Mon, 25 Jul 2016 12:20:40 GMT

I am also expecting answer

Re: SCD implementaion in Talend 5.x +db2 db

gorotman — Mon, 25 Jul 2016 13:29:39 GMT

Hi,
may you preparare an example to better explain your requests?

Re: SCD implementaion in Talend 5.x +db2 db

Anonymous — Tue, 26 Jul 2016 09:10:18 GMT

Hi gorotman,
Thanks for your reply. Please find the example scenario.
1. Source db has below tables
   - employee ( master table)
    - employee_address and employee_salary ( child tables)
     emp_id as acts a key column .
     employee master has a column named " change_data"   which shall have identification for change data "I",'U' and "D" and
      the child tables in source will not have any identifcation column for identifyig the changed data
2. Destination db also has employee,employee_address and employee_salary table with similar data structure as that of source db.
     On daily basis the employee,employee_address and employee_salary tables in destination db needs synched up with change               data from the source db.data has to be synched in both parent and child tables

Can we achieve this via SCD ? I believe SCD Type 1 is capable of performing updates on dest table .
Having said that in our case the change data flag is available only in master table so is this SCD implemtation capable of updating child   records also along with the parent records

Re: SCD implementaion in Talend 5.x +db2 db

gorotman — Tue, 26 Jul 2016 15:24:45 GMT

Hi ushamu,
yes, it is possible to manage your scenario in TOS, but I think you can use a simple tMap->tMSSqlOutput (or ever db output component for your db).
You already have source rows marked with correct operation (I,U,O), so use this column.
This is the example:

1- input is your source master table
2- tMap with filter expression for output flows:

insert=> row1.change_data.equals("I")
update=> row1.change_data.equals("U")
delte=> row1.change_data.equals("O")
3- for every output choose the correct action on rows (insert, update, delete)
If you can use "change_data" column to apply insert/update/delete operations to child rows, you can reuse same logic, but use these tables as primary and do a lookup to master table in tMap component.
At the end, you need to reset "change_data" column? or do you have a date to filter rows processed?