Set row "create_date" and "last_update_date&q

NYCooper · Post by **NYCooper** » Wed Aug 15, 2012 7:26 am

Hello,
i am new on data stage and i wonder if i can set a "initial create date" and a "last update date" in a table for each row. The table has one key. The Process is an update/insert. i want to save two date's "initial create date" and the "last update date".
in transformer stage i found only the function currentimestamp() but this way set everytime the date when i run the job...
How can i do this?
Thanks a lot

BI-RMA · Post by **BI-RMA** » Wed Aug 15, 2012 8:54 am

You have to identify which rows to update and which ones to insert and split the streams for these two operations.

Then you can set initial create date on insert and last update date on update.

But Datastage won't identify which column to write in an Insert/Update-Operation.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 15, 2012 2:56 pm

Welcome aboard.
You can perform a lookup to determine whether the key already exists and thereby determine the derivation for create date or update date.

NYCooper · Post by **NYCooper** » Thu Aug 16, 2012 1:18 am

Hello,
the first way works fine. (Split the process in two: one insert and one update each time with the currenttimestamp).
the second way with the lookup i try later, sounds much more professional...
thanks for the ideas!
NYCooper

chulett · Post by **chulett** » Thu Aug 16, 2012 6:51 am

The issue with the "upsert" mechanism is that all columns that you need for the insert must also be used in the update. Typically there are columns that you do not want to change, such as the create date, and in order to do that you need to look up the current value and pass that along rather than the current date when the record already exists. And then the insert will need to fail before the update is performed if that is the order of execution.

I personally find the 'combo' insert/update actions to be a crutch and not something one should use the vast majority of the time. Do the lookup, get the information you need for the update and then send them to separate targets doing either the insert or the update. And in this particular case, the only information you may need from the lookup is whether it succeeded or not.

aartlett · Post by **aartlett** » Thu Aug 16, 2012 7:27 pm

I agree with Craig. Split and insert or update separately.

This is especially true if the number of updates is small. Upsert and update operations are incredibly time consuming in most databases, Inserts are normally very quicl. Insert/Update (or update/insert) tend to take a lot of time.

Be careful of your transaction size. These are no absolutes as it depends on number and size of records and indexes impacted. Play with it a bit.