Unused indexes are like a dead weight holding back your platform’s performance. Why? Well, indexes take up disk space. This means saves and restores take longer to complete. To make matters worse, you need to maintain these indexes during heavy updates, causing IOPS, core and memory usage to increase.
(Since I sell IBM i on prem or in our cloud, I should, in theory, encourage this bad behavior. But then again, these bad habits make the platform underperform, so it’s not good for any of us).
I’ve heard of some IBM i admins creating every index that the index advisor suggests. Please don’t do this. Blindly creating indexes is an excellent way to end up with indexes you literally NEVER use.
Are you starting to worry that you or predecessor might have made this mistake in the past? First, rest assured that you’re not alone, it’s a common mistake. This article will help you figure out if the indexes on your system are actually being used, and how often.
How to figure out if you have indexes on your system that you literally NEVER use
STEP 1: Display the indexes for a table (start with the big ones)
- Using Access Client Solution or System i Navigator window, expand the system you want to use.
- Expand Databases and the database you want to work with.
- Expand Schemas and the schema you want to work with.
- Double click on Indexes.
STEP 2: Check the dates
If an index is not being used, the following 3 dates are or could be blank:
- Last Query Use
- Last Query Statistics Use
- Last Used Date
STEP 3: Check the count fields
If an index is not being used, the following count fields are or could be 0:
- Query Use Count
- Query Statistics Use Count
- Days Used Count
STEP 4: Double-check before you delete
When it comes to removing unused indexes, you do need to be careful do not delete indexes that were supplied by your software provider. It’s entirely possible that the index is there for a function you simply haven’t leveraged yet. Before doing anything drastic, ask your software provider or check the source code (if you have it) in case the index is being referenced.
And there you have it! You can boost your IBM i system performance in just a few minutes and at no extra cost. Oh, and speaking of costs… Remember our deal: I’m happy to reveal some of IBM i’s best-kept secrets to help you optimize your operating system, but I respectfully ask that you let me know if my tips help you by sending me a quick note at: tamarav@r2i.ca
As I explained in my previous article, your feedback helps me know if this information was valuable. Don’t forget to be specific about what you did and how much it impacted your IBM i platform performance! If this information helps you, I’ll be back to reveal more best-kept secrets covering performance analysis, databases, security, fix-level reporting and more!
