Passing Existing Record For Update Again In Update Table Component In Abinitio

Question

What happens if an already existing record is sent for an update query as it is to an update table component? Does it go unused?

I have an abinitio output file which has records to be updated(not inserted). I need to collect only those records which are actually updated. So how can we separate the records which are exactly same as before in DB (not updated) and which have at least one field updated?

Why is this tagged with both a database and Ab Initio? Where is the work being done? — Gordon Linoff, Jul 23 '16 at 15:03
I am using Update/insert query in update table component(abinitio) to load in database dB2. Can you please answer my question? — Richa Garg, Jul 24 '16 at 03:09

score 1 · Answer 1 · answered Jul 24 '16 at 15:29

This is an ETL question as DB2 will do the update whether the row has changes or not. I do not know Ab Initio in detail but you have to do a change detection upfront the DB2 Update. Ususally ETL tools have some kind of "Change Capture" / "Compare" / "Difference" functions to detect changes.

score 0 · Answer 2 · answered Jul 25 '16 at 15:34

You can try to play with the Unused port in the Update Table component, Also look at ActionRequired Flag.

An easy way to determine if an update will occur, or better yet, only feed in updates only is to join data from the database (select statement that will select every record that is to be updated by your update file), and join it with the database on all the fields in the record as key. Those records that come out on the unused port pertaining to the update file are the ones that will perform an update action.

score 0 · Accepted Answer · answered Jul 26 '16 at 17:15

My first approach was the same as Alex suggested but it seems like Join with DB with all fields as key will take more time and resources. A better approach is to compare existing values and new values to be updated in a reformat select parameter or Filter by expression for each field. This will give only those records which will actually be updated. Also Michael is right, DB2 will update irrespective of it is an actual update or not. So, unused port will not give the records which are not updated.

Passing Existing Record For Update Again In Update Table Component In Abinitio

3 Answers3