Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    153

    Unanswered: Identify Unused tables

    Hi Experts,


    How can we identify unused tables in a database?

    How can i find database activities? Is there any log from which i can find the particular user or IP and the task done by that user?

    TIA

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jan 2009
    Posts
    153
    Thanks Rahul but thats not helping me.

    I want tables which are not used in a database and also those have no data in it

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I am sure you would have little problem identifying tables with no data ...

    As for unused tables, Rahul's suggestion is for identifying used tables (and unfortunately you put in the effort to find the ununsed tables!) ... You can also use Table event monitor to capture a list of all tables accessed between activation and deactivation of the database.

    But there is no gurantee any of these will give the results you want in real life ... How long are you going to monitor the table (un)usage for ... say 1 month ... There may be a table accessed by quartelry job that did not run in your data capture period ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2009
    Posts
    153
    Hi Satya,

    Thanks for the reply i have used the same Rahul said to find the used tables but i felt that not correct.

    We have created the database 1 year back and i want to know the tables we havent used yet.

    Also is there any query to find the tables having no data?

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    There is no way as far as I am aware to identify tables that have never been used ..
    What application is this ? - you should be able to get info from the development/architecture team ... well, it is easier said than done I know ..

    If you have just done runstats, then card column in sysstat.tables will give you the info .. otherwise, you have to do on a table basis
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by ratheeshknair View Post
    Hi Satya,
    Thanks for the reply i have used the same Rahul said to find the used tables but i felt that not correct.
    it depends how you interpret the result. for a table not used since last instance recycle the scans/upsate/ins/del values will be tending towards 0
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks Bella ...
    Good to know .. I thought indexes were the only ones in-scope for LASTUSED



    Quote Originally Posted by db2girl View Post
    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
  •