Wednesday, March 30, 2022

SQL indexes suggested from the job log

sql ddl indexes advised by debug

I have written before about how Db2 for i will suggest indexes, using the SYSIXADV view or Navigator for i, that will improve the performance of your SQL programs, etc. There are times I need to know if any indexes are advised when a program has run just once.

Fortunately this information is written to the job log, when the statement is executed in the program.

Before I demonstrate that I need a SQL DDL table I can use:


Notice that the table does not have a key.

Next I need a program to use the table:

01  **free
02  dcl-s wkFirst char(10) ;
03  dcl-s wkSecond like(wkFirst) ;

04  exec sql DECLARE C0 CURSOR FOR
               ORDER BY SECOND,FIRST
                 FOR READ ONLY ;

05  exec sql OPEN C0 ;

06  exec sql FETCH NEXT FROM C0 INTO :wkSecond,:wkFirst ;

07  exec sql CLOSE C0 ;

08  *inlr = *on ;

Line 1: I only code in totally free RPG.

Lines 2 and 3: I need to define a couple of variables, into which the data from the table will be moved to.

Line 4: A cursor definition using the table I created. Notice that I want the results returned to me sorted by the columns SECOND and FIRST.

Line 5: I open the cursor.

Line 6: I am just performing a single row fetch as in this example I just need to retrieve one row for Db2 to suggest an index.

Line 7: As I am done with the cursor, I close it.

I need to make some changes to the job I am going to run the above program within to get the results I want. I need to make a change to the way my job log settings work, and I need to run the above program in debug too. I have created a CL program to run the commands I need, rather than me having to type commands on the command line every time I want to do this.

01  PGM







Lines 2 and 3: This is me just ensuring that the library my program and table are in are at the "top" of the user part of my library list.

Line 4: To get the information I need the program needs to run in debug mode. Notice that I have used the Display Module Source parameter, DSPMODSRC. By having that as *NO the source code is not displayed, even if I compiled the program to do so.

Line 5: I change my job's settings using the Change Job command, CHGJOB. You will have to check what yours are, and make them match what I have here.

Line 6: I call the program I created above.

Line 7: I don't need debug anymore, therefore, I end it.

I need to look at my job's log. I must admit this is one time where I think it is easier not to use SQL and just use the IBM i commands.

I find the easiest way to view the job log of my current job is to call the QCMD program:


If the screen is blank, I press F10 to "Include detailed messages".

I page up and down until I find the line that starts "**** Starting optimizer debug message for query .":

Library MYLIB already exists in library list.
Ownership of object QTESUSRSP in QTEMP type *USRSPC changed.
Unable to retrieve query options file.
Ownership of object QTESUSRSP in QTEMP type *USRSPC changed.
Unable to retrieve query options file.
**** Starting optimizer debug message for query .
Temporary result file built for query.
Arrival sequence access was used for file TABLE1.
Access path suggestion for file TABLE1.    
**** Ending debug message for query .
ODP created.
Blocking used for query.
Cursor C0 opened.
Data conversion required on FETCH or embedded SELECT.
1 rows fetched from cursor C0.
ODP not deleted.
Cursor C0 was closed.
SQL cursors closed.
  1000 - ENDDBG

I am looking for a line that says: "Access path suggestion for file TABLE1". I have an arrow pointing to that line above.

If I do not see a line like this then no index was advised. In this case there is a suggestion.

I position the cursor onto this line and press either the Help key or the F1 key.

The details for message id CPI432F will be displayed. I am showing the complete message text below.

Message ID . . . . :   CPI432F
Date sent  . . . . :   DD/DD/DD      Time sent  . . . . :   TT:TT:TT

Message . . . . :   Access path suggestion for file TABLE1.

Cause . . . . . :   To improve performance the query optimizer is suggesting a 
permanent access path be built with the key fields it is recommending. The access
path will access records from member TABLE1 of file TABLE1 in library MYLIB.
In the list of key fields that follow, the query optimizer is recommending the 
first 2 key fields as primary key fields. The remaining key fields are considered
secondary key fields and are listed in order of expected selectivity based on this
query. Primary key fields are fields that significantly reduce the number of keys
selected based on the corresponding selection predicate. Secondary key fields are
fields that may or may not significantly reduce the number of keys selected. It is
up to the user to determine the true selectivity of secondary key fields and to
determine whether those key fields should be used when creating the access path.
   The query optimizer is able to perform key positioning over any combination of
the primary key fields, plus one additional secondary key field. Therefore it is
important that the first secondary key field be the most selective secondary key
field. The query optimizer will use key selection with any remaining secondary key
fields. While key selection is not as fast as key positioning it can still reduce
the number of keys selected. Hence, secondary key fields that are fairly selective
should be included. When building the access path all primary key fields should
be specified first followed by the secondary key fields which are prioritized by
selectivity. The following list contains the suggested primary and secondary key
If file TABLE1 in library MYLIB is a logical file then the access path should be
built over member TABLE1 of physical file TABLE1 in library MYLIB.

   The sort sequence table is used in *HEX and lib .
Recovery  . . . :   If this query is run frequently, you may want to create the
suggested access path for performance reasons. It is possible that the query
optimizer will choose not to use the access path just created.

In reality you can just look for the part of the text that starts: "The following list contains the suggested", as what follows is the suggested key columns.

The following list contains the suggested primary and secondary key fields:

I can then create my index. I always like to create index with unique keys if at all possible.


If I run my CL program again and look in my job log, I no longer see the line about "Access path suggestion" as none were suggested, it even tells me the name of the index that was used.

**** Starting optimizer debug message for query .
The query access plan has been rebuilt.
Access path of file TABLE1A was used by query.
**** Ending debug message for query .


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



The following was received from Kent Milligan, Technology Services, IBM Technology Lifecycle Services, and I thought it is better contained within the main body of this post, rather than as a comment:

Indexes and keyed LFs have actually been different since V4R2 when IBM changed the logical page size for an SQL index to 64K. Keyed LFs normally default to 8K logical page size. The larger logical page size provides better performance with query execution.


  1. great stuff Simon

  2. Thank you for sharing this. Very useful information!

  3. Reynaldo Dandreb MedillaMarch 31, 2022 at 5:07 AM

    Thanks Simon, thats handy while in the backend session

  4. Nice info. Does the access path get deleted if the LPAR is IPLed.

    1. All temporary access paths will be deleted when the partition is IPL-ed.

      All index will not be deleted.

  5. Hi Simon
    Excuse me but I absolutely have what those indexes are really need for?

    1) IMHO it's very clear and obviously that for case when specified are some fields for file with arrival sequence the better use the keyed access
    2) Additionally what are the advantages here of index file before the logical
    again IMHO it is allocating much more disk space ?

    1. 1. The Db2 engine will determine that a "table scan" search is better than a new index for the SQL statement used. I found that if I used a cursor to Select a large number of rows from a file it did not suggest a index, and told me a "table scan" was more efficient.

      2. In the old days, pre-6.1, LF and Indexes were pretty much the same. Often IBM would suggest you build a LF in place of an Index. Things changed post-6.1 and now Indexes are not just LF. IBM has explained to me that they are now different, and if your goal is fast database access you want to use Indexes.


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.