Having the source for suggested indexes created for you

Last year I wrote about using the Db2 for i index advisor to suggest indexes that would improve the performance of your SQL statements. I showed how you could copy data from its results to use to create for SQL statements to create the suggested Indexes. Recently I found a Db2 for i Procedure that makes this so much easier as it will create source members for the suggested Indexes.

I can use the Index Advisor View to see what Indexes are suggested for the FILE1 in the library MYLIB.

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 = 'MYLIB' 08 AND SYS_TNAME = 'FILE1'

This statement returns four suggested Indexes, listing the keys:

LIBRARY TABLE FIRST_ADVISED NBR_TIMES MYLIB FILE1 2017-01-16-11.25.54.767710 4,000 MYLIB FILE1 2017-09-24-13.26.05.294104 738 MYLIB FILE1 2017-06-30-08.29.08.977529 250 MYLIB FILE1 2017-02-21-11.23.31.796991 250 ADVISED_KEYS FLD001 FLD004 FLD001, FLD002 FLD001, FLD002, FLD004, FLD005

The ADVISED KEYS column shows the columns/fields that should be used as keys in the suggested Indexes.

The wonderfully named Db2 for i Procedure HARVEST_INDEX_ADVICE, makes it that much easier as it generates the source code for each Index in a source file of my choice. The Procedure has seven parameters:

1. Name of the schema/library that contains the parent table/file
2. Name of the parent table/file
3. Number of times the Index has been advised. If I enter a number for this parameter then only the Index with this or greater number of times suggested source will be generated
4. Number of times a maintained temporary index has been used. I don't care for this parameter and by passing zero it is ignored
5. Average estimated number of seconds needed to execute the statement(s) that caused the Index suggestion. I am going to ignore this parameter too by passing zero
6. Name of the schema/library that contains the output source file
7. Name of the output source file

Before I run the Procedure I am going to build my output source file:

CRTSRCPF FILE(QTEMP/INDEXES) RCDLEN(1000) TEXT('Output from HARVEST_INDEX_ADVICE')

The record length needs to big enough to contain the suggested Index's statement on one line. I picked 1,000 as I knew that would easily accommodate the source I expect to be generated.

One warning before I run the Procedure: The rows for the suggested Indexes that have their source created by HARVEST_INDEX_ADVICE are removed from the table(s) that the Index Advisor uses.

I want to create the source code for the suggested Indexes that has been suggested more than 500 times for my file, FILE1.

CALL SYSTOOLS.HARVEST_INDEX_ADVICE('MYLIB','FILE1',500,0,0, 'QTEMP','INDEXES')

Notice that this Procedure in the schema/library SYSTOOLS.

Once the Procedure has completed I can go to the source file to see the source members it created.

Work with Members Using PDM File . . . . . . INDEXES Library . . . . QTEMP Opt Member Type Text HARV1 HARV2

As I expected two source members have been created. If I wanted to see the generated SQL statement in the source members I would usually put a "5" next to the member and press Enter, and the member would open. With these members when I do that I receive the following message:

Record length greater than 240.

The additional information message for this message is:

Additional Message Information Message ID . . . . : EDT0303 Severity . . . . . : 40 Message type . . . : Information Message . . . . : Record length greater than 240. Cause . . . . . : Records longer than 240 positions cannot be processed. Recovery . . . : Specify a different file to edit.

As I created my source file with a record length of 1,000 I cannot use PDM to open the members. I can use the Display Physical File Member command, DSPPFM, instead.

DSPPFM FILE(QTEMP/INDEXES) MBR(HARV1)

My source member has just one line:

000100180221 CREATE INDEX MYLIB.FILE1_RADIX_INDEX_1 ON MYLIB.FILE1 (FLD001, FLD002, FLD004, FLD005 )

I had to break this into two lines to make this fit on this screen.

The first eleven characters are the source sequence number, the following six characters are the source record change date. The CREATE INDEX statement follows.

The source for the second source member looks like:

000100180221 CREATE INDEX MYLIB.FILE1_RADIX_INDEX_2 ON MYLIB.FILE1 (FLD004 )

Why is "radix" included in the Index's name?

I just want to say "Because the Indexes are created as radix type indexes". This is not very helpful. IBM describes a radix index as:

A radix index is a multilevel, hybrid tree structure that allows many key values to be stored efficiently while minimizing access times. A key compression algorithm assists in this process. The lowest level of the tree contains the leaf nodes, which contain the base table row addresses associated with the key value. The key value is used to quickly navigate to the leaf node with a few simple binary search tests.

For a more detailed explanation visit IBM's page here.

Having created those two indexes when I go back to the Index Advisor View I find those rows have been removed:

LIBRARY TABLE FIRST_ADVISED NBR_TIMES MYLIB FILE1 2015-06-30-08.29.08.977529 250 MYLIB FILE1 2017-02-21-11.23.31.796991 250 ADVISED_KEYS FLD001, FLD002 FLD001, FLD002, FLD004, FLD005

Do remember this before you start playing with this Procedure, otherwise you might lose the ability to create the source members for the Indexes you want.

It might be a good idea, for future reference, to create a table of the information before you run the Procedure. In the statement below I am creating a table, ADV_INDEX, that will contain all the suggested Indexes information for FILE1.

01 CREATE TABLE MYLIB.ADV_INDEX AS 02 (SELECT * 03 FROM QSYS2.SYSIXADV 04 WHERE SYS_DNAME = 'MYLIB' 05 AND SYS_TNAME = 'FILE1') 06 WITH DATA

By creating the source members for the suggested Indexes for you Db2 for i makes it easy for you to build the Indexes needed to improve the performance of your SQL statements.

 

You can learn more about the HARVEST_INDEX_ADVICE Procedure from the IBM website here.

 

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