Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Unanswered: How can I find out the Perticular INDEX usage

    Hello,
    First I would like to thank you to give me an oppertunity to join with DB2 community.

    We are using DB2 V8. FixPack 12.
    According to our Application provider there are two indexes for two different tables are not being used.
    So I am planning to drop those two indexes. However is there any way to findout that the indexes are being used by DB2 or any applications? or when last time it was used?

    Thanks, Gunas.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you know all the SQL statements that go against that table, you can do an explain on them to see if any of them use those indexes, but otherwise it is difficult to know for sure.

    However, the nature of certain indexes may suggest that DB2 will not use them, even if they show up in predicates. For example, if you have a low cardinality column with less than about 20 different values (the exact number depends on a variety of factors), an index on that column is not likely to be used (unless there is some sort of extreme distribution of values or the table is clustered on that index).

    Keep in mind that DB2 will only use an index if it can reduce the number of data pages that it has to access for the SQL statement. If DB2 "thinks" (during SQL compilation) it would have to access at least one row on every page (so that all pages must be accessed), then it will not use an index.
    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
    Quote Originally Posted by Marcus_A
    If DB2 "thinks" (during SQL compilation) it would have to access at least one row on every page (so that all pages must be accessed), then it will not use an index.
    Two other reasons for using an index, which kind of contradict the above "filtering" reason, are:
    - the data can be returned by only looking at the index ("index-only" access)
    - by doing an index-based access, an additional sort can be avoided (and that sort would be more expensive than the overhead of using the index)

    B.t.w.: even an index which is not "used", i.e., is not found in any access path for any query, could be "useful" for other reasons:
    - an index which enforces a UNIQUE constraint
    - an index with the CLUSTER attribute, enforcing the clustering sequence on its table
    - an index having statistics attached to it, based on which the optimizer can choose a better access path (not using that index) than when those statistics would be unknown to the optimizer
    --_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
    Dec 2008
    Location
    Chennai
    Posts
    111
    Quote Originally Posted by Marcus_A
    If you know all the SQL statements that go against that table, you can do an explain on them to see if any of them use those indexes, but otherwise it is difficult to know for sure.

    However, the nature of certain indexes may suggest that DB2 will not use them, even if they show up in predicates. For example, if you have a low cardinality column with less than about 20 different values (the exact number depends on a variety of factors), an index on that column is not likely to be used (unless there is some sort of extreme distribution of values or the table is clustered on that index).

    Keep in mind that DB2 will only use an index if it can reduce the number of data pages that it has to access for the SQL statement. If DB2 "thinks" (during SQL compilation) it would have to access at least one row on every page (so that all pages must be accessed), then it will not use an index.
    Thank you for the berief answer.
    Gunas

  5. #5
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    Quote Originally Posted by Peter.Vanroose
    Two other reasons for using an index, which kind of contradict the above "filtering" reason, are:
    - the data can be returned by only looking at the index ("index-only" access)
    - by doing an index-based access, an additional sort can be avoided (and that sort would be more expensive than the overhead of using the index)

    B.t.w.: even an index which is not "used", i.e., is not found in any access path for any query, could be "useful" for other reasons:
    - an index which enforces a UNIQUE constraint
    - an index with the CLUSTER attribute, enforcing the clustering sequence on its table
    - an index having statistics attached to it, based on which the optimizer can choose a better access path (not using that index) than when those statistics would be unknown to the optimizer
    Thank you so much for the help. I will double check with Apps provider and drop those indexes.

  6. #6
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Multi Page file allocations

    Hi,
    I have noticed that our database is configured to extend SMS tablespace objects by one page at a time. (Multi page file allocation enabled -NO).

    We have mix of SMS and DMS tablespaces and the DB size is over 500GB.

    Is it important to enable the Multi page file allocation ? should be able to see any better performance? finally how can I enable this ? once i have enabled this is it possible to backout ?

    Thanks, Gunas

  7. #7
    Join Date
    Jun 2007
    Posts
    66
    Multipage file allocation can be enabled using db2empfa for databases that are created after the registry variable DB2_NO_MPFA_FOR_NEW_DB has been set.

  8. #8
    Join Date
    Jun 2007
    Posts
    66
    you can find unused index with the help of the following::



    db2pd -db testdb -tcbstats index|awk '/TCB Index Stats/{found=1} found==1 {print}'| grep -i tb0077z|awk '{print "Index id:" ,$3 " Index Scans:",$8}'

  9. #9
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    DB2 Win 2003. Not unix

    Quote Originally Posted by nivasreddy007
    you can find unused index with the help of the following::



    db2pd -db testdb -tcbstats index|awk '/TCB Index Stats/{found=1} found==1 {print}'| grep -i tb0077z|awk '{print "Index id:" ,$3 " Index Scans:",$8}'
    Hi,

    The DB2 we are using Version 8.2 on Window 2003. Not Unix. Instead of using awk, any other way to get the results?

    Thanks.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dgunas
    Instead of using awk, any other way to get the results?
    You could of course install Cygwin (which has GNU awk) from www.cygwin.com
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The db2pd command is not conclusive, since it only reports on items in package cache. If you have infrequently run SQL statements, or ad-hoc queries, or even different literals in the predicates, you could be making an incorrect assumption about which indexes are not being used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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