Update Action

[fmartinsferreira]

In the combo-box "Update Action" from Oracle 9 Stage there is the following action:

-> Update existing rows or insert new rows

Would I like to know if this action update the row independently if there was any change or if it update only the rows that changed something?

Regards,

Fernando

[chulett]

Read the online help available from inside the stage so you understand how all of the update actions work.

That action issues an update and - if the update fails - also attempts an insert. There is another where the two actions are reversed. There is no update action that works only if something has changed.

[LogicDude]

When you select the "Update existing rows or insert new rows" option, what it will do is: check whether the defined Primary keys are present in the target table or not. If the "key" is found, it updates all the columns you are loading. If the "key" is not found, it inserts a new row. As simple as that!

If you want to do a update only if something has changed, you need to compare them in the constraint in Transformer stage. If you have a lot of columns to compare, You can use CRC32.

IHTH

[fmartinsferreira]

Thank you!

[dls]

Depending on the job design and sources, I've found the UNIX 'comm' command to be and effective way to isolate 'new' and 'changed' records up-front rather than in the job's Transformer stage.

[LogicDude]

Thanks dls,

I know that the UNIX 'comm' command compares two files which can select or reject rows common to two files.

But if the "Source" in the DS job is a database table, we need to do a lookup and write the records (new and existing) to two seq files and then use the UNIX command? Will it still show better performance? (compared to the logic where we decide about the update/insert, in the transformer stage constraint).

Thanks.