CODEWITHSUNDEEP
sqldatabasedb2
dbza
dbza

Reputation: 326

List DB2 tables on which DML queries were performed recently

(This is similar but not a duplicate of get the list of db2 tables which have been changed since a particular timestamp say since 20120801185856 timestamp )

I need to get a list of DB2 9.7 luw tables which are modified after a specific timestamp. Is there any sys tables which I can use? I came across the above question, and the query on SYSCAT.TABLES gives a list of tables created/altered with the timestamp. But I am looking for the last insert/update/delete timestamp on each table.

Also LASTUSED column in SYSCAT.TABLES will not work for me as it is about last 'select'; I am looking for modifications and not select alone.

we have technical/metadata columns within the tables, but if there is a metadata table maintained by DB2 I would like to use that first.

Also this requirement is not for any change data log or audit trigger etc, I just want a list of tables inserted/updated/deleted after a specific timestamp.

Upvotes: 0

Views: 2719

Answers (1)

AngocA
AngocA

Reputation: 7693

What you are looking for is an Audit. That information is not in the catalog tables. Instead, you should activate the audit tool: db2audit, and then this tool will write the information that you want.

This can give you a lot of details about what is happening in the database. But, it impacts the performance.

This is a article about how to use this tool: http://www.ibm.com/developerworks/data/library/techarticle/dm-0603wasserman/

Remember, that the data is not logged as part of the utility, because this is against the privacy. Thus, you cannot know what exactly a user modified in the database. With db2audit, you can know which action was performed on which object.

If you really want to know what a user did, then you need to develop your own tracking system. You can implement something with triggers or use a logging utility like log4db2 in stored procedures.

Upvotes: 1

Related Questions