Hi Everyone,

I have one performance issue and when i ran an EXPLAIN on that i found the MATCHCOLS = 0 on the index it was using. Now i have created another index on the columns the query is using and ran an RUNSTATS & REBUILD utility on that. But the explain information in the plan table still says that the new index has not been used.

Is there anyway we can force DB2 to use the new index..? I'm sure if it uses the new index i have created it would come with MATCHCOLS greater than zero and it will go for INDEX ONLY scan. I have no much idea about this and didnt get an oppurtunity to work on this. Can you guys please help me..?

I have taken the RUNSTATS on the table data as well. When DB2 can get the more information from the new index why it is going for the old INDEX..? My question may sound odd. I was just thinking if there is way to ask DB2 to use this INDEX. (I know we just WHAT TO DO and not HOW TO DO ) but still curious. Please help me..!!

What's the query and what's the index definition? Maybe your query is written in such a way that no matching index scan can be performed.