
I have been playing with the new release of Access Client Solutions, ACS 1.1.8.5, and have been impressed with it. I have found Navigator for i a bit faster so thought it was time to revisit one of the coolest features included in it, the Db2 for i index advisor.
I have written about using the index advisor using the old Client Access Operation Navigator before. The ACS version of the index advisor is so much easier to use I wanted to encourage you to look at it.
When you access a table or file using SQL the Db2 engine determines the best access path to use to extract the data requested. If there is not a suitable existing access path it will build a temporary one. When it does, it makes an addition to the index advisor table with the suggested key, or if there is already a row it increments the "times used" counter. I can use the index advisor to see which indexes are suggested. If there is a suggested index that has been used many, many times then I will build it to make the SQL faster, as it no longer has to build the temporary access path.
I always open the Navigator for i from the ACS 5250 emulator, in the "Actions" menu.

Navigator for i will open in your default browser, and present you with a signon screen. I use my usual IBM i user id and password.
The Navigator for i screen consists of two panels. On the left is list a list of links with pluses ( + ) next to them.

I want to click on the plus next to the "Database" link. When this expands I click on the plus next to "Databases". That opens a list of the databases on your IBM i. For most of us that will have the same name as the IBM i partition. I click on the name of the database, in this example it is Dev740.

In the right panel the databases options are displayed. Here I click on the "Actions" drop down and select "Index Advisor".
![]() Click on image to open larger version |
The only two options I recommend you use are:
Index advisor
When I select the "Index advisor" the right panel displays the suggested indexes.
![]() Click on image to open larger version |
To only display the information I am interested in I made a few changes before I made this screen capture.
- Red box: I entered the name of the library I am interested
- Orange box: When the results for my library was returned I selected the down arrow in the first column to sort the results by table/file name
- I have resized the columns, using my mouse, to make the information I am interested fit here
The columns I am interested in are:
- Table for which indexed was advised
- Schema
- System schema = Library name
- System name = File name or short table name
- Partition – Not interested in this
- Keys advised is the suggested key for the new index
- Leading keys order independent – In my experience it is always the same as the previous column
- Advise index type – not interested
- Last advised for query use is the last time this index was suggested
- Times advised for query use is, in my opinion, the most important column. If the number here is multiple millions then I really need to make this index rather than have the Db2 engine building a temporary access path
If I click on the table name I get a popup showing very basic information about the suggested index.

If I right click on the suggested index I get a different popup that allows me to work with this suggestion.

Here I click "Create index", and I am taken several screens that help me build the index I desire.
![]() Click on image to open larger version |
If you are going to use a long name for your index, greater than 10 characters, consider giving this index a system name rather than let IBM i generate the name.
Next tab is for key information.
![]() Click on image to open larger version |
I always leave the key to be what was suggested. Next tab is where I can enter a WHERE clause, my select and omit criteria.
![]() Click on image to open larger version |
To build the selection I click on the "Condition builder" button.
![]() Click on image to open larger version |
I want to make a selection based on the city name. Therefore, I click the radio button next to the City column.

I think this is a bit redundant but here I can select the operator type. Scroll down to see all the offered operators.
![]() Click on image to open larger version |
In the box at the bottom I enter the comparison. In this case I only want to include rows where the city is 'LONDON'. And click the "OK" button. My selection(s) are displayed.
![]() Click on image to open larger version |
I leave everything on the "Options" tab unchanged.
![]() Click on image to open larger version |
If I click on the "Show SQL" at the bottom of the screen the generated SQL statement is displayed.
![]() Click on image to open larger version |
When I click on the "OK" button the index will be created, and following is displayed.
![]() Click on image to open larger version |
Condense advised indexes
The Db2 for i team is always adding cool features for us to use, and I am extremely grateful that they do. This is an example of their wonderful work. Rather than select "Index advisor" I select "Condense advised indexes".

While the layout of the screen is the same as the "Index advisor", the data shown is different.
![]() Click on image to open larger version |
The index advisor suggested four indexes for the City table with the following keys:
- City
- City, Country
- Country
- Country, City
This has been condensed into just two suggestions, with the keys:
- City, Country
- Country
Personally I think this is really cool. Now I can just build two indexes instead of four.
![]() Click on image to open larger version |
![]() Click on image to open larger version |
Here I have all the indexes I built using Navigator for i:
Work with Objects Using PDM Library . . . . RPGPGM1___ Position to . . . . . . . . __________ Position to type . . . . . __________ Opt Object Type Attribute Text __ CITY *FILE PF-DTA CITY goes with table PERSON __ CITY_00001 *FILE LF Index generated from Index Advisor __ CITYIDX1 *FILE LF Index generated from Index Advisor __ CITYIDX2 *FILE LF Index generated from Index Advisor |
The first index, shown by PDM as a "LF", is the one I created using the "Index advisor". As I did not give it a system name IBM i has generated one for me.
The other two indexes were generated using the "Condensed index advice", there I did give system names.
I only tested this with IBM i 7.3 and 7.4 . I cannot guarantee if it will work with earlier releases. I have only been able to test this with ACS 1.1.8.4 and 1.1.8.5. I cannot guarantee this will work for earlier versions.
Why not use IBM i Access Client Solutions -> Schemas -> Databases -> Library -> right click and select Index Advisor ?
ReplyDelete