Hi

Db2 8.2.6 on windows

Would like to know how to get the last timestamp a table was altered. I can get get the create_time of table from syscat.tables, but I cannot see anything there as to when a table was altered.


Thanks

Hari Kumar

You could write a stored procedure (sp) to keep that bookkeeping information for you.

Each time a record is inserted, updated or deleted from that table another table could be updated to reflect that event.

Or, something that is very often done, write a sp that stores per record when it was created, who did it and when it was last updated and by who and a counter that stores the number of updates.

If you only need to know the information for a whole table, the fastest way to accomplish this is the first solution, though with high insert/update/delete loads, the table that stores the bookkeeping info could become a bottleneck.

Wim's solution would work, but only if you are able to force everyone to use the Stored Procedure to do all of the insert/update/deletes. If you cannot guarantee that every user will use the Stored Procedures, you should create triggers to log the information. That way no one will be able to bypass the logging. DB2 by itself does not track this info.

Andy

I believe that the OP wants to know when the schema (number of columns, types of the columns, constraints, ...) of the table were changed - not the content of the table. As far as I know, there is nothing in the DB2 catalog to provide that information.

My error, i meant to use triggers (instead of stored procedures) to do all that bookeeping.

There is still something like a log to keep all the modifications one has applied to a databse.

Right. But with that argument, you wouldn't need triggers or whatever to track data changes either.

The log is not such a good alternative because it is (mostly) for DB2-internal purposes only to ensure the durability and consistency of a database.

Thanks for the reply Stolze.

I take it from your statement that there are no catlog info in DB2 as to when exactly a table structure was modified.

hari

Not that I'm aware of, no.

ya there is nothing in catalog tables
but if you activate db2audit then u can keep track of the ddl changes
and all other changes made by various authorities