• 1 reply
  • Latest Post - ‏2013-05-18T16:34:16Z by B.Hauser
4 Posts

Pinned topic Index Adviser

‏2013-05-17T16:25:52Z | advisor index

I have written a SQL Insert with a subselect. The table being accessed by the subselect has 1,299,059,544 records in it. When I ran the SQL in debug mode, I received the message CPI432F, ACCESS PATH SUGGESTION FOR FILE...

I saw the 7 columns listed, and created SQL to build the index on the DEV partition. One of my coworkers mentioned that he heard at COMMON that just because the index adviser advises another index that it will not necessarily always use it. He mentioned that it may be needed on one partition, but not on the PRD partition. He also mentioned that he has heard that if we do build it in PRD, DB2 may use it for awhile, then all of a sudden, stop using it.

Is there any way to tell before the index gets built, whether or not it is going to be used? Is there any way that, once the index is no longer deemed to be needed, that DB2 will alert us somehow, so we can delete it?

We don't want to start creating indexes and have the system not use them.

Thank you.


  • B.Hauser
    304 Posts

    Re: Index Adviser



    this may not be the answer you want to hear, but in either way you should read the white paper.

     IBM DB2 for i indexing methods and strategies  

    Learn how to use DB2 indexes to boost performance

    You may also use IBM i Navigator Database - Index Evaluator, i.e. Databases --> Schemas --> YourSchema --> Indexes --> Columns Last Query Use, Query Use Count, Last Query Statistics Use, Query Statistics Use Count, to find out which indexes are no longer used and which indexes are how often used. In this way you may be able to clean up your indexes.