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:

1. Open the Operations Navigator
2. If you have more than one system select the one you want to see information for.
3. Click on the plus ( + ) next to the Databases.
4. Right click on the database, in the example below it is called E202307w.
5. Select Index Advisor.
6. Select Index Advisor, again.

Getting to the index advisor Click 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.

First few columns of the Index Advisor

CREATE TABLE RPGPGM5.PERSON ( PERSON_ID FOR UNIQUE NUMERIC (10,0) GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NOCYCLE), FIRST_NAME VARCHAR(25), MID_INITIAL CHAR(1), LAST_NAME VARCHAR(30), BEGIN_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, END_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, TS_ID TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, PERIOD SYSTEM_TIME (BEGIN_TS,END_TS) ) ;

There a lot more information that you can see about these advised indexes.

Click to open large images in a new window

I only look at the following columns when making the decision whether to build an index or not.

1. Schema : library where the table/file is in.
2. Short name : physical file/table name.
3. Keys advised : the keys for the index.
4. Times advised for query use : the larger the number the more times this index has been recommended.

So what next? I find the index I want to build, then right click on the line.

Getting to the SQL code

I can either create the index or I can show the SQL for the suggested index. Personally I always show the SQL as I copy the source, place it into a source member, make any changes, and compile it.

Suggested index's SQL code Click to open a large image in a new window

As I built my SQL statements over the table PERSON the next time I run the "monster" program it will use the new index, and run faster than it did before.

If you are not able to use the Operations Navigator you can get the same information about advised index keys from the table SYSIXADV. I have just selected the columns I find useful:

01 SELECT SYS_DNAME AS LIBRARY, 02 SYS_TNAME AS TABLE, 03 FIRSTADV AS FIRST_ADVISED, 04 TIMESADV AS NBR_TIMES, 05 KEYSADV AS ADVISED_KEYS 06 FROM QSYS2.SYSIXADV 07 WHERE SYS_DNAME = 'RPGPGM5' 08 AND SYS_TNAME = 'PERSON' 09 ORDER BY TIMESADV DESC,FIRSTADV

Which gives me the same as what I saw in the Index Advisor:

LIBRARY TABLE FIRST_ADVISED NBR_TIMES RPGPGM5 PERSON 2016-09-27-03.47.11.436098 33 RPGPGM5 PERSON 2016-09-30-03.38.24.281928 17 RPGPGM5 PERSON 2016-09-25-07.18.38.142613 14 RPGPGM5 PERSON 2016-09-30-04.33.32.139177 3 ADVISED_KEYS PERSON_ID BEGIN_TS PERSON_ID FIRST_NAME

I can then build my index statement manually with the keys recommended in the Advised Keys column.

You have found that the Index Advisor will advise indexes for physical files too. You can build indexes over physical files, or, if you desire, translate the index's information into a logical file.

I do not recommend that you build every index the Advisor suggests. If you do find one that has been advised, let's say, 100,000 times then I would definitely look into building it.

 

You can learn more about this from the IBM website:

• Displaying index advisor information
• SYSIXADV table

 

This article was written for IBM i 7.3, and should work for earlier releases too.