Seeing which index or logical file is being used by a SQL statement

I was asked if it was possible to see which DDS file or SQL index was used by a SQL statement?

Fortunately it is easy to see. But before I show how to do it, let me set up my "test data".

I have my physical file, TESTFILE, which has no key:

A R TESTFILER A FLD001 6P 0 A FLD002 10A A FLD003 L

I built two logical files built over this physical file. TESTFILEL0 has the field FLD001 as its only key:

A R TESTFILER PFILE(TESTFILE) A K FLD001

And TESTFILEL1, which has the key field FLD003:

A R TESTFILER PFILE(TESTFILE) A K FLD003

I also built two SQL indexes over the physical file. TESTFILEX0, which is keyed by FLD002 and FLD001:

CREATE INDEX MYLIB.TESTFILEX0 ON MYLIB.TESTFILE (FLD002,FLD001) RCDFMT TESTFILER ;

And TESTFILEX1, keyed by field FLD003, giving it the same key as TESTFILEL1:

CREATE INDEX MYLIB.TESTFILEX1 ON MYLIB.TESTFILE (FLD003) RCDFMT TESTFILER ;

I used ACS's "Run SQL scripts" for running the SQL statements. All of the Select statements were basically the same, the only difference was the Order by clause.

But before I run any of those statements I need to find the job name of my "Run SQL Scripts" job. I can retrieve this using the SQL built-in function JOB_NAME like this:

SELECT JOB_NAME FROM SYSIBM.SYSDUMMY1

The result shows the job name:

JOB_NAME ----------------------- 435219/QUSER/QZDASOINIT

I can copy-n-paste my job name into my 5250 session like this to display this job:

DSPJOB 435219/QUSER/QZDASOINIT

I take option 14 "Display open files, if active" on the Display job menu and the open files for the "Run SQL Scripts" job are displayed.

If I execute the following SQL statement, with no Order clause, it shows that just the physical file is open.

SELECT * FROM MYLIB.TESTFILE Member/ File Library Device ... TESTFILE MYLIB TESTFILE

To minimize the influence of one statement on the next I disconnected from the database in the "Run SQL Scripts" ( Connection > Disconnect ) and reconnect again ( Connection > Reconnect database name ) between each of the following statements.

Next SQL statement used the Order by clause to sort the file by the first field/column FLD001. As expected the logical file TESTFILEL0 is used as its key matches the Order by clause.

SELECT * FROM MYLIB.TESTFILE ORDER BY FLD001 Member/ File Library Device ... TESTFILE MYLIB TESTFILE TESTFILEL0 MYLIB TESTFILEL0

Next SQL statement orders the file by the second field/column FLD002. This time the SQL index TESTFILEX0 is used as its first key field/column is FLD002:

SELECT * FROM MYLIB.TESTFILE ORDER BY FLD002 Member/ File Library Device ... TESTFILE MYLIB TESTFILE TESTFILEL0 MYLIB TESTFILEX0

Ordering the SQL statement by the third field, FLD003, made for an interesting scenario as the logical file TESTFILEL1 and the SQL index TESTFILEX1 have the same key field: FLD003.

The first time I ran this statement the "winner" was the logical file:

SELECT * FROM MYLIB.TESTFILE ORDER BY FLD003 Member/ File Library Device ... TESTFILE MYLIB TESTFILE TESTFILEL1 MYLIB TESTFILEL1

But the more times I ran this statement I found that the SQL index was selected instead:

SELECT * FROM MYLIB.TESTFILE ORDER BY FLD003 Member/ File Library Device ... TESTFILE MYLIB TESTFILE TESTFILEX1 MYLIB TESTFILEX1

How about I use one of the logical files instead of the physical file in the Select statement and Order it by a different key? TESTFILEL0 is keyed by FLD001 so I am going to sort it by FLD003:

SELECT * FROM MYLIB.TESTFILEL0 ORDER BY FLD003 Member/ File Library Device ... TESTFILEL0 MYLIB TESTFILEL0 TESTFILEX1 MYLIB TESTFILEX1

The SQL index that is keyed by FLD003, TESTFILEX1, is used.

 

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