One of the most common questions that our Db2 team in IBM Technology Expert Labs hears on SQL performance engagements is, Does my table have too many indexes? Given the efficiency of the Db2 for i index maintenance algorithms, I often respond by asking When was the last time you did housekeeping on the indexes for the table?.
If a
table has a large number of indexes (which also includes keyed logical files),
then there’s a strong chance there are probably some indexes that are no longer
used by the Db2 query optimizer or your applications. Meaning clients can often make room for new
indexes by removing indexes that are no longer providing value.
The good
news is that IBM i Access Client Solutions (ACS) for many yes has provided the
ability to perform index housekeeping.
The bad news is that this ACS function was hard to find. The even better news is that with the 1.1.9.9 version of ACS released this month, ACS provides an interface that makes it
easier to evaluate the impact of your existing indexes.
Let’s
start with the original ACS interface for index housekeeping, so that you can
better appreciate the new streamlined interface delivered by ACS 1.1.9.9. The biggest issue with the existing support
is that the interface could not be accessed from the ACS SQL Performance Center
- even though everyone agrees that indexes are a critical success factor for
achieving good SQL performance. The
index usage feedback information was buried inside the ACS Schemas tool.
The
following figure shows the steps that were required to access the index usage
feedback information. After launching the ACS Schemas tool, one needed to
navigate to the table whose indexes they wanted to analyze and to select the
Work With->Indexes option.
After performing all these
navigation steps, you were presented with a window like the following which
displays the usage metrics needed to perform index housekeeping.
The 1.1.9.9 version of ACS streamlines access to these same index usage metrics. This streamlining was made possible by leveraging the launchpad that was added to the SQL Performance Center at the end of 2024. The following figure shows how the latest version of ACS adds an Index Evaluator option right in the middle of the SQL Performance Center launchpad.
Once you click on the Index Evaluator icon in the launchpad, you are prompted for the name of the schema and table that you want to perform index housekeeping on. No need to launch ACS Schemas tool, just a couple of clicks and you’re all set to start finding indexes which are just collecting dust and can be removed!
