Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2008
    Posts
    62

    Unanswered: Identify unused indexes

    Hello,

    We are using db2 V8.1 LUW on aix 5.3

    After lot of searching i derived a query to find unused indexes in a database. Can you please confirm if this is right query (right way)?

    db2 "select NAME, CREATOR from SYSIBM.sysindexes where NAME not in (select distinct sysibm.sysplandep.bname from sysibm.sysplandep, syscat.PACKAGEDEP, syscat.indexes where sysibm.sysplandep.bname=syscat.PACKAGEDEP.bname and syscat.PACKAGEDEP.bname=syscat.indexes.INDNAME)"


    subquery in blue will find indexes whose entry is in tables syscat.PACKAGEDEP and sysibm.sysplandep. Finally it will exclude this result set from sysibm.sysindexes to get unused indexces.

    regards,
    Prashant

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That query may be correct (I have not looked at it in detail) for indexes used in static SQL (inlcuding SQL stored procedures) but will not include any dynamic SQL that is typically submitted from a java program.
    Last edited by Marcus_A; 05-19-09 at 11:35.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Also, be aware that (1) index statistics might give the optimizer lots of information, even if that index won't be used, and (2) cluster indexes play an important performance-related role, even when not used.
    (But if a cluster index turns out to be never used in access paths, it's maybe time to choose an other index as cluster index...)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    db2pd is the best tool to hunt for unused indexes:
    Code:
    db2pd -db sample -tcbstats index
    Look for the "TCB Index Stats:" header and the "Scans" column.
    This will tell you how many times DB2 used that index. If the counter is ZERO, DB2 did not need that index at all so.......
    Be ware:
    1 - when a database stops and starts again all the counters are reset to ZERO
    2 - beware of FK contraints. The index might be required as FK.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dr_te_z
    beware of FK contraints. The index might be required as FK.
    AFAIK, an index is never required to support FKs.
    An index on a FK column can of course be helpful for join queries where the join condition happens to be "PK = FK" and where there is either a strong condition on the PK column, or the FK table is the inner table of a nested loop join.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Peter is right. In an FK-PK relationship, the only requirement is that a unique constraint exists on the PK columns, and in DB2 such a unique constraint is enforced with the help of a (unique) index.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Peter.Vanroose
    AFAIK, an index is never required to support FKs.
    I know, I know. What I mean is:
    Suppose there is a function in the application to DELETE a code from a table. This code is used as a FK in many other tables and because it's an online/realtime function in your application, the developer made sure that all FK's have an index.
    Now we, as "know-it-all" DBA run some analysis, identify unsed indexes and throw them away.... We all know that disaster strikes when the code-deletion application is executed.
    So I mean that you'll have to check with the developers.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    ... and they probably don't know either. ;-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Feb 2008
    Posts
    62
    Thank you Markus, stolze & peter for your valuable comments.

    And Thanks detez, for your suggestion. I was not aware of such a use of "db2pd".

    Thank you ALL , I really got more than what i expected.

    That's why this is my favorite forum

Posting Permissions

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