create timestamp column

[ukyrvd]

Hi,
I was wondering if there is any better method for doing this. For each row to be inserted/updated into the database (oracle) I need to include current time stamp information in the row in a separate column.

I was thinking of following tow options:
1) Oracle Enterprise stage, Have a user defined insert statement and in the insert clause have sysdate value for column .. however this requires insert array size of 1
2) Parallel transfer .. define and derive new column and use Oracle enterprise stage with load option.

Is there any other way to achieve this?? If any of you have tested the options which one is better one?? I could test it for myself, but just wanted to know if somebody else has already done this

Thanks for your help
prasad

[DSguru2B]

In otherwords you need to capture the timestamp at row level when that particular row was inserted or updated.
Well for inserts, specify that column to get the current timestamp at the table itself. This way you dont even need to pass that particular column. When a row gets inserted, the database will generate the timestamp. In sql server you do it by the function getdate(). Dont know its equivalent function in oracle. Folks working with Oracle can jump in and provide it.

For updates you can specify that column with sysdate in the sql as you mentioned.

[ukyrvd]

Thanks DsGuru.

You mean to say, during table creation have that column defaulted to currentTime (of row insertion) and pass nothing for that column in Insert SQL?? That looks like a better option than my #1.. thank you!!

[DSguru2B]

You got it.

[chulett]

In sql server you do it by the function getdate(). Dont know its equivalent function in oracle.

sysdate

[ukyrvd]

Yes chulett!!

Any other methods other than these two options (#1 and #2)?? And is it better to go with #1 compared to #2??

[ray.wurlod]

For completeness, in DataStage you could use

Code: Select all

TimestampFromDateTime(current_date(),current_time())

to generate the timestamp. Use this expression when initializing a stage variable and do not modify the stage variable row by row, so that it is only calculated once. In this way every row will get the same update stamp. If you want "now" for each row, simply use this expression as the stage variable derivation for each row processed.

[ukyrvd]

Thanks Ray.

If you want "now" for each row, simply use this expression as the stage variable derivation for each row processed.

thats how, I wanted to implement option #2.

I am in dilemma which one to chose Transformer vs Oracle_default value/insert statement

[chulett]

Factor in the 'overhead' of calling out for the system date/time on every record through the transformer. I'm not really sure how much it adds, but it must be something.

Balance that against doing it in Oracle, where the overhead is negligable as far as I know. The downside would be it would only work for inserts and not updates. A trigger could work for both, but some people are trigger shy... and there can be noticable overhead with them.

If you really want to know which way is more performant in your environment with your data - try both! Let us know.

[ukyrvd]

If you really want to know which way is more performant in your environment with your data - try both! Let us know.

I sure will .. I will try with about 3M rows and see how it behaves!!