Inserts using DD_UPDATE in Update transformation

1
votes

I am using update strategy transformation in Informatica to update/insert a table. Following are the steps taken in mapping/session :

1) Used update strategy transformation after expression. Post which Update strategy connected directly to target. 2) Provided 'DD_UPDATE' statement in Update strategy expression. 3) Marked 'treat source rows as' to 'Data driven'. 4) Checked 'Insert', 'Update else insert' property at Target level in the session.

When I run the mapping, it updates the records in target if values are already present in the target table and inserts the records if not found in the target tables. However, when I check the session log (set to verbose data), I found the following thing :

Send Expression output- > Update strategy (Value of rows as 0 which is insert) Send Update strategy output - > Target (Value of rows as 1 which is update). Loading to target - > 1 row as update which is already present, 3 rows inserted which are new.

The question is how does Informatica know how to treat a row as insert/update before loading as per above scenario? Since the values passed from update strategy are set for Update.

1
etlinformaticainformatica-powercenter
I think it first excutes the update query; if that fails, it executes an insert query. - Samik
I agree with @Samik - which means this also might affect performance, as actually two operations are performed. Depends on scenario. With say 99% updates it should be perfectly fine, some rows will get inserted instead. But the other way around, if inserts are common, this might be slow. This is a nice trick if you're unable to cache target in lookup and determine the existence of row. - Maciejg

1 Answers

0
votes

If you execute the update query from any programming language and check the return value it will give you the number of rows it has updated.

This is what Informatica uses to identify if the update query it has executed has actually updated any records. If none are updated Informatica will execute the insert query for that record.