SQL indexes suggested from the job log

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:

CREATE OR REPLACE TABLE MYLIB.TABLE1 (FIRST VARCHAR(20), SECOND VARCHAR(20)) ON REPLACE DELETE ROWS

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 SELECT SECOND,FIRST FROM TABLE1 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 02 ADDLIBLE LIB(MYLIB) POSITION(*FIRST) 03 MONMSG MSGID(CPF0000) 04 STRDBG PGM(TABLE1TEST) UPDPROD(*YES) DSPMODSRC(*NO) 05 CHGJOB LOG(4 0 *SECLVL) LOGCLPGM(*YES) 06 CALL PGM(TESTPGM) 07 ENDDBG 08 ENDPGM

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:

CALL QCMD

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 .":

100 - ADDLIBLE LIB(MYLIB) POSITION(*FIRST) Library MYLIB already exists in library list. 400 - STRDBG PGM(TABLE1TEST) UPDPROD(*YES) Ownership of object QTESUSRSP in QTEMP type *USRSPC changed. Unable to retrieve query options file. Ownership of object QTESUSRSP in QTEMP type *USRSPC changed. 600 - CHGJOB LOG(4 0 *SECLVL) LOGCLPGM(*YES) 800 - CALL PGM(TABLE1TEST) 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 fields: SECOND, FIRST. 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: SECOND, FIRST.

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

CREATE UNIQUE INDEX MYLIB.TABLE1A ON MYLIB.TABLE1 (SECOND,FIRST)

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.