I manage an iSeries that serves as the enterprise server for a large ERP package. Over the past year, I have created many indexes using the "Advisor" application within Operations Navigator/SQL Scripts. Accordingly, our "Read only" DSS reports run much better than they use to. While our OLTP performance is also currently acceptable, I want to begin an audit on all of our current indexes.
Unfortunately, the ERP installation creates a myriad of composite indexes, with redundant columns -- all in various orders. Trying to audit this will be cumbersome, to say the least.
I am trying to find a method that can help me determine what indexes are being used and what indexes are not. With 3500 tables and many indexes, trying to intercept SQL manually, and log what indexes are being used, isn't feasible. Can you offer any suggestions?
No support exists today, but there will be some tools specifically targeted at this area in the next release of iSeries Navigator. So keep your eyes posted. In the meantime, you might also want to investigate third-party tools.
MORE INFORMATION ON THIS TOPIC
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: tips, tutorials and more.
Check out this Search400.com Featured Topic: Database issues resolved
Dig Deeper on DB2 UDB (universal databases)
Let us know if you find this guide helpful. If you have a topic you'd like to see a user guide on, let us know and we'll do our best to provide you with it.
Related Q&A from Kent Milligan
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading
When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to ... 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.