Track Down Useless Indexes Effortlessly with Toad for DB2 LUW

DB2 LUW indexes, like any database indexes, can be extremely helpful for queries that need data that’s scattered in the database.   They act, of course, like a book’s index – rather than methodically thumbing through every page of the book and noting each “hit” on a word or phrase, why not have an index that tells you exactly where to go to find those matches in the book.   Same for database access – indexes help a lot.

Except when they don’t.  

If an index is not helping queries access the data quicker (and with lower overall impact on the database’s resources), why would/should the index exist? They might be causing more resource overhead on the database needlessly.   You’d probably want to know about any “unused” indexes for this very reason – they result in wasted resources on the database and the server as the database engine keeps indexes current as data is added, updated and deleted over time.  

Toad for DB2 LUW has the easy answer to this.   Release 6.5 brought us the “Unused Index” report.  

Figure 1: At the database, tablespace, table, or index level of the “tree” view, right click, then choose Space Management – Identify Exceptions.

Figure 2: The new tab (as of Toad DB2 LUW Release 6.5), “Unused Indexes” – sort by the Last Used column or scroll to find those that have a Last Used date of 1/1/0001. Those have never been used.

The date column on the report tab indicates that last time the index was used. The number of days it’s been since an index was used is listed. The example above shows plenty of “1/1/0001” dates, meaning each of those indexes have never been used.  The other dates in that column mean that the threshold of “number of days” has been exceeded.   To alarm as red (“worst case”), the threshold that is set in Toad Options has been exceeded.   As you might guess, the thresholds for “worst case”, critical or warning types of alarms on this report are configurable.

There are some caveats to consider before dropping indexes that show a “Last Used” date of 01/01/0001 in DB2, or indexes that haven’t been used in a while.   If an index is a DB2 system index, you probably don’t want to drop it.   And, if an index is one of these types, don’t drop them unless you are sure they no longer apply to your database design:

• Block or dimension indexes
• Unique or primary key indexes
• Contain foreign key columns

If you're unsure, seek guidance from IBM.   But for indexes that do not fall into one of those 'exception' categories, if you are sure they are indeed not necessary, you can clean up your database and eliminate wasted resources, but dropping them.

To find out more about Toad for DB2 LUW, and Toad for DB2 z/OS, explore the Toad for DB2 page on Toad World here, and find out all that’s new in the latest release.  

Questions?

Start a discussion about this blog in the Toad World® forum, Toad for IBM DB2 . 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!