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.

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.

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 for the berief answer.
Gunas

Thank you so much for the help. I will double check with Apps provider and drop those indexes.

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

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.

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.

You could of course install Cygwin (which has GNU awk) from www.cygwin.com

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.