last use of a table

NetToni99 · #1 (**permalink**) 09-10-04, 09:54

Hi,

can explain someone to me, how I can receive information about the last use of a table? We want to delete all tables in a DB2-db, which were no more used since 6 months. We have DB2 v7.2 with FP12 on a AIX system.

Thanks in advance
Toni

blom0344 · #2 (**permalink**) 09-10-04, 15:51

How about the db2diag.log ?

Depending on settings you may find the latest update/insert on the table if you start at the bottom of the file and scan it upwards?

" Shot in the dark"

dollar489 · #3 (**permalink**) 09-11-04, 19:14

Create a trigger, if there is any activity such as insert/update/delete add an entry to a table lets say table_tracker (just for tracking table changes) with table name and date (along with activity type) and then query to table to see if somebody has done any activity on this table.

dollar

blom0344 · #4 (**permalink**) 09-13-04, 03:09

Creating a trigger is a very good idea, but unfortunately this will only give information on future activity, not on the past 6 months.

Trigger DDL: (for delete action)

Code:

CREATE TRIGGER SCHEMA.LOG_DEL_TABLE
  AFTER
  DELETE
  ON SCHEMA.LOG_DEL_TABLE
  FOR EACH STATEMENT
  MODE DB2SQL
  INSERT INTO TABLE_LOGGING
  VALUES('DELETE ON TABLE',CURRENT TIMESTAMP);

sathyaram_s · #5 (**permalink**) 09-13-04, 04:01

AFAIK, There is no information on last access to a table in db2 ... The trigger method may work only for Insert, Update, Delete ... Selects cannot be captured using triggers ..

To know a list of tables accessed, you can take a table snapshot, say once every 10 minutes ... At the end of the day, you can format the output to get the list of tables ... The risk here is that only information on tables in the catalog cache will be available .. So, there is a possibility that your table is not listed ...

The definitve method to generate a list is the table event monitor ... The table is triggered when the last application connected to the database is terminated ...

HTH

Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech