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

    Unanswered: unused indexes in db2

    Hi my db is V8.1 fixpak12.i have question
    is there any way in db2 to recognize the unused indexes present on the tables?.
    i think one way is to use the eventmonitor i guess.other than this is there any tool which shows the unused indexes if present bunch of SQL select queries.
    in oracle there is a command
    ALTER INDEX indexname MONITORING USAGE
    i think this command let us know whether or not an index is used.
    is there any similar thing present in db2?
    to what extent db2pd -tcbstats will be helpful in this scenario?.
    any help is appreciated.
    thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use the DB2 Design Advisor to get recommendations on additional indexes that would benefit the given workload - or to remove indexes that are not used by the workload.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    You can use the DB2 Design Advisor to get recommendations on additional indexes that would benefit the given workload - or to remove indexes that are not used by the workload.
    I don't see how, given a set of existing indexes, the Index Advisor will tell you which existing indexes are not being used. Maybe this is just my ignorance.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2006
    Posts
    471
    if all sql would be static, you could find out the not used indexes, but most of the time, the dynamic sql is not known and unused indexes are difficult to find..
    dynamic statement cache is only a snapshot at specific moment in time and could be different from other moments in time
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The Design Advisor only operates on the given workload. If the workload does not use some of the existing indexes, the advisor will say so. Of course, if the workload you provided is only part of the real workload, it would be foolish to simply drop all indexes that are not used by the provided workload because other SQL statements may make good use of those indexes.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    One way to approach this would be to collect all dynamic SQL statements issued by the application over a representative interval (e.g. a week) using snapshots, then explain all of them and query the explain_object (I think that's the right table) for index names that were used.

    Obviously, this method is limited by the fact that you won't be able to collect static SQL data.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    The Design Advisor only operates on the given workload. If the workload does not use some of the existing indexes, the advisor will say so. Of course, if the workload you provided is only part of the real workload, it would be foolish to simply drop all indexes that are not used by the provided workload because other SQL statements may make good use of those indexes.
    It seems to me that for this to work, every index suggested by the index advisor must exist in the exact specification as the index advisor recommends, which in my opinion and experience is a very bad situation to be in.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2008
    Posts
    1
    One method I've been looking at is db2pd -tcbstats. To see the index stats I run "db2pd -tcbstats index <tablespace_id> <table_id>". It gives a bunch of info, the problem is figuring out what the information means. Specifically, there is a column in the output called scans, which I would hope means how many times an index is scanned. I was hoping in my environment that this value would be 0 for some, meaning that I could probably do without those indexes. Unfortunately, that was not the case. Does anyone know how to figure out what the other output colums from that command mean? I've asked our IBM tech rep and I've logged a PMR but haven't found any good documentation yet.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vrm468
    is there any way in db2 to recognize the unused indexes present on the tables?
    There are some other potential aspects of index "use" that the Design Advisor will never catch:
    - a unique index has, as everybody knows, some interesting side effects even if not used in an access path
    - the mere existence of an index can give the optimizer certain information which it would otherwise not have, e.g. index-based statistics. Dropping such an index will potentially change access paths (in a potentially negative way) unless one is aware of the fact and generates non-index-based statistics that carry the same information (if at all possible).

    (I can give more details for DB2 on z/OS for those of you interested in it.)

    Bottom line: there is no 100% foolproof automatic way to do this; one has to know & understand the database design and the applications/queries typically used against it.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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