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.