create timestamp column
Moderators: chulett, rschirm, roy
create timestamp column
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
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
thank you
- prasad
- prasad
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
For completeness, in DataStage you could use 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.
Code: Select all
TimestampFromDateTime(current_date(),current_time())
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers