Pages

Wednesday, September 27, 2017

Index Advisor helps performance

As we all start using more SQL to get data from our tables and files, there are things we can do to ensure that our programs are not that "monster" that takes down a system. One of them is to ensure we use the best access path to get that data. Rather than hunt for the "best" logical file/index to match our key, we should always build our Selects, etc, over the physical file/table and allow the Db2 for i engine to find the best access path for us. Personally, I think that is remarkable that the Db2 for i engine will find the best logical file/index to use. If there is not an index that matches what I need, it builds a temporary access path. This temporary access will be reused, within the same job, whenever I run that SQL statement. You may have noticed if you execute a complex SQL statement many time it will get faster the more times it is run, within the same job.

Building temporary access paths can still result in a "monster" being created. The best way to improve the performance of the "monster" is to make sure that there are indexes that match what I need. Rather than having to identify all the indexes myself, I can use Index Advisor and have Db2 for i recommend the index I should build.

The Index Advisor provides an easy to understand interface for the information in the table SYSIXADV. When the Db2 for i engine is running SQL statements it logs information about the temporary access paths it builds into this table. You can go ahead and run a SQL Select over that table to find the advised indexes, but I think you will find the Index Advisor a lot easier to use.

Operations Navigator
icon

The Index Advisor is part of the Operations Navigator, which might be called the "System i Navigator" or "iSeries Navigator" on your system. To get to the Index Advisor:

Open the Operations Navigator

If you have more than one system select the one you want to see information for.

Click on the plus ( + ) next to the Databases.

Right click on the database, in the example below it is called E202307w.

Select Index Advisor.

Select Index Advisor, again.

Getting to the index advisorClick to open a large image in a new window

When you open the Index Advisor it may not look the way mine does. You will have to click on the column headings to sort it the way you want.

I went looking for any of tables or files the Advisor makes recommendations for in my libraries, RPGPGMn. I found that it had made recommendations for the table I built for the post about Temporal Tables.

6 comments:

Firstly, thanks for all the great articles you publish. I am constantly learning something new from your posts.

I'm currently using index advisor found in Operations Navigator but keep meaning to take the time to use, and learn, the index advisor in ACS Schemas. Was there a reason for you not using the ACS Schemas version?

Actually, ACS itself does NOT contain System-wide Index Advisor tool. When you use this tool from ACS, ACS itself will invoke System-wide Index Advisor from Navigator for i tool for you. Here is more details : http://www-01.ibm.com/support/docview.wss?uid=nas8N1019797

In my past experiences (as an IBMer, I deliver useful index creation service to many customers), I prefer using System-wide Index Advisor tool from IBM i Navigator for Windows tool (as described in this blog post) just becuase it is comparatively faster (on a properly tune Wintel PC). The only catch now is that the Windows-based tool does not support Windows 10 or any later releases.

Simon, excellent article as always. I would add that when tuning queries, its often best to sign off/on before running another test in order to start fresh with an empty cache. Otherwise one can sometimes get false results. This has been my experience...

To prevent "comment spam" all comments are moderated.Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.