Q
Problem solve Get help with specific problems with your technologies, process and projects.

How can I reduce NLEVELS for an index?

I have a question on an index. One application program is running slow because it is selecting an inefficient index. This is because NLEVELS (4) is more for the efficient index for the program. I tried running the reorg on the index to reduce the NLEVELS, but still the NLEVELS is 4 for the index. How can I reduce the NLEVELS for the index?

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!

This was last published in February 2003

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close