Capturing inset, last update date and time, and user id

[tbtcust]

Capturing inset, last update date and time, and user id

Hi all.

I would like to capture the:
- inset update date and time (date and time a row was created)
- last update date and time (date and time a row was created or the date and time of the last update
- user id of the person or application login

I have a table with the respective columns setup to store the above info as varchar 254.

Are there any built in functions I could use to accomplish this?

I'm using DB2 V8.2 (FP 14) for windows 2000 Pro.

Thanks in advance for any help.

 

[MarcLodge]

tbt103,
I don't believe there is a function to do this, but I think that the information you require is in the log. Whenever I have had a similar requirement, I have had to use a piece of 3rd party software to examine the log.

Marc

 

[tbtcust]

Thanks for replying MarcLodge. I should have been clearer.

I have a table with several columns. Three of them are Row_Inset_DT, Row_Last_Update_DT, and Last_User_Id. I need to populate them and/or update them when the row itself is inserted or updated.

I'm hoping there is some built-in function that will handle this for me. That way I don’t have to write the code.

 

[MarcLodge]

tbt103,
The dates can be achieved when the table is defined. For the date last update you can specify in the definition that the CURRENT_DATE is the default. I think that for the Row Insert Date, you may have to define a trigger to the table so that the column is populated with the CURRENT_DATE only when the row is inserted.

The Last_User_Id is more of a problem as I suspect that this is an application held field. Within DB2 there is a register/function called CURRENT_SQLID but this, generally, is a group although that differs from installation to installation.

I would suggest having a word with whoever controls your DB2 tables (often a DBA) and see what they come up with.

Let us know how you get on.

Marc

 

[tbtcust]

Thanks for you input MarcLodge. This is very helpful. I'll post the solution.