Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: last use of a table

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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"
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Sep 2002
    Posts
    456
    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

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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);
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •