If you have reorganized the index and it still requires 4 levels then it is unlikely that you will be able to get NLEVELS any lower without changing the definition of the index. Without more information about each of the index structures you reference it is tough to give you any advice. I am guessing that the index in question has a longer key than the other index that is being selected.
You might try another approach to "resolve" you problem. It seems to me that you are just trying to get DB2 to choose IX1 instead of IX2. Have you (or your DBA if you are not the DBA) tried to modify the catalog statistics to get the access path you desire? If you are sure that it is the NLEVELS statistics that is causing DB2 to select the "wrong" index, then you are in luck. It is possible to modify this value using a SQL UPDATE statement such as:
UPDATE SYSIBM.SYSINDEXES SET NLEVELS = 3 WHERE CREATOR = 'creator id' AND NAME = 'index name';
Such a technique should be used with caution and only by an authorized DBA who knows all of the potential ramifications of making such as change. Before attempting this, make sure that you know that any subsequent execution of RUNSTATS will over-write this change and you will have to re-issue the UPDATE to reset the NLEVELS to 3. Also, be sure that you know that this change will impact every other SQL statement that is bound after you have made the change. So, if you want to avoid such a global impact, consider running the UPDATE, binding your program, then resetting NLEVELS back (either with another UPDATE or by running RUNSTATS). That way, only your particular program will be impacted by the modified statistic.
Other DB2 statistics can be modified using UPDATE. These are documented in the IBM DB2 Administration (SC26-9931) manual in the charts found in Chapter 32. Good luck with your SQL tuning attempts!
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
Our expert suggests that the best way to pass the SAP BW exam is to have hands-on job experience. But that's not all he has to say about ... Continue Reading
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.