Using Navigator for i for SQL index suggestions

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:

1. Index advisor
2. Condense advised indexes

 

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.

1. Red box: I entered the name of the library I am interested
2. 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
3. I have resized the columns, using my mouse, to make the information I am interested fit here

The columns I am interested in are:

1. Table for which indexed was advised
2. Schema
3. System schema = Library name
4. System name = File name or short table name
5. Partition – Not interested in this
6. Keys advised is the suggested key for the new index
7. Leading keys order independent – In my experience it is always the same as the previous column
8. Advise index type – not interested
9. Last advised for query use is the last time this index was suggested
10. 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:

1. City
2. City, Country
3. Country
4. Country, City

This has been condensed into just two suggestions, with the keys:

1. City, Country
2. 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.