topic Re: Find deleted records from source and delete it from target !! in Talend Studio

Find deleted records from source and delete it from target !!

Anonymous — Sat, 16 Nov 2024 10:17:42 GMT

Hi Guys,
We are doing a migration of data from informix database to Oracle database. We need to track the deleted rows from source and need to delete it from target. Is there any way that i can do it. Please help
Thanks,
Vishnu

Re: Find deleted records from source and delete it from target !!

Anonymous — Thu, 27 Oct 2016 08:36:09 GMT

Hi,
Usually, Nb_LINE is used to count the total number of records have been proceed.

When this option is set to NONE, there is no a global variable for counting the total number of records have been inserted, updated, or deleted.

When delete item is choosen, there will be a global variale such as ((Integer)globalMap.get("tOracleOutput_1_NB_LINE_DELETED")) will be avaible, which counts the total number of records have been deleted.

Best regards

Sabrina

Re: Find deleted records from source and delete it from target !!

Anonymous — Thu, 27 Oct 2016 08:46:05 GMT

How are the rows being deleted from your source database? Is Talend doing this or is it happening via some application/other process? If it is Talend, then the solution is simple so I doubt this is the case. If it is an application or another process, can you edit the source db to add delete triggers? If so, you could keep track of deleted records by adding a delete trigger and keep the record key in a "deleted" table. If you cannot do this, then you are left with comparing your source and target, then removing the rows that do not exist in your source but do in you Target. That is the most expensive way and depending on how many table you are comparing and how big your database is, could be quite a lengthy process.

Re: Find deleted records from source and delete it from target !!

Anonymous — Thu, 27 Oct 2016 10:44:06 GMT

Hi rhall,
The rows are getting deleted by another process..for which i dont have any control.
For me the job would be running weekly,
1.at that time only the new and updated records should be inserted into the target and
2.for the records which get deleted from source in between the earlier job and current job ,should also get deleted from my target.
I did not get the solution for step 2.
How will i compare them?.....using a lookup? or?
if(by using lookup{where will i give the condition}
else
{which component should i use}.
Thanks,
Vishnu

Re: Find deleted records from source and delete it from target !!

cterenzi — Thu, 27 Oct 2016 19:42:05 GMT

You would need to query the source and target tables for their primary keys, join them using a tMap lookup (unless there's a faster way I don't know yet), create an output for target table keys without a matching source key, then delete all target table records with those keys.

Re: Find deleted records from source and delete it from target !!

Anonymous — Fri, 28 Oct 2016 04:34:18 GMT

This solution could be a problem in case not all history is maintained on the source side. How is data maintained on the source side needs to be looked into before implementing this solution. Having a delete trigger is one of the clean solutions that can be implemented.

Re: Find deleted records from source and delete it from target !!

Anonymous — Fri, 28 Oct 2016 07:16:20 GMT

Hi All,
I got the solution,thanks to cterenzi.
1,I just compared the source and target using a tMap lookup.Checked the option for Inner Join,
2,SET the 'catch lookup Inner Join Reject' to: 'True' for Target in tMap.
3.Performed a Deletion Job on this Mapping
Thanks All,
Vishnu

Re: Find deleted records from source and delete it from target !!

Anonymous — Fri, 20 Jan 2017 16:05:06 GMT

Hi All,
I got the solution,thanks to cterenzi.
1,I just compared the source and target using a tMap lookup.Checked the option for Inner Join,
2,SET the 'catch lookup Inner Join Reject' to: 'True' for Target in tMap.
3.Performed a Deletion Job on this Mapping
Thanks All,
Vishnu

Hello Vishnukr894,
Thanks for posting your solution. But can you kindly explain what you did in Step "3. Performed a Deletion Job on this Mapping".
And if possible, post a screenshot of your solution. That will be very helpful.
Thanks in advance.
Cheers,
Dela.