Pages

Tuesday, May 11, 2021

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

which lf or sql indexed is used in sql select

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.

2 comments:

  1. Another way to do it is using STRSQL after issuing STRDBG. The joblog will give a pretty detailed synopsis of what it did and why, especially if you so an F1 on some of these messages.

    strdbg
    strsql

    Query options retrieved file QAQQINI in library QUSRSYS.
    **** Starting optimizer debug message for query .
    All access paths were considered for file DPPDIVSN. <<< F1 this
    Access path of file DPLDIVNA was used by query. <<< and this
    **** Ending debug message for query .
    ODP created.
    Blocking used for query.

    #1 All access paths were considered for file DPPDIVSN. detailed info:
    Message . . . . : All access paths were considered for file DPPDIVSN.
    Cause . . . . . : The query optimizer considered all access paths built over
    member DPPDIVSN of file DPPDIVSN in library SIERRA.
    The list below shows the access paths considered. If file DPPDIVSN in
    library SIERRA is a logical file then the access paths specified are
    actually built over member DPPDIVSN of physical file DPPDIVSN in library
    SIERRA. Following each access path name in the list is a reason code which
    explains how the optimizer considered the access path.
    SIERRA/DPLDIVSN3 4, SIERRA/DPLDIVNA 0.

    The reason codes and their meanings follow:
    0 - The access path was used to implement the query.
    1 - Access path was not in a valid state. The system invalidated the
    access path.
    2 - Access path was not in a valid state. The user requested that the
    access path be rebuilt.
    3 - Access path is a temporary access path (resides in library QTEMP) and
    was not specified as the file to be queried.
    4 - The cost to use this access path, as determined by the optimizer, was
    higher than the cost associated with the chosen access method.

    #2 Access path of file DPLDIVNA was used by query detailed info:
    Message . . . . : Access path of file DPLDIVNA was used by query.
    Cause . . . . . : Access path for member DPLDIVNA of file DPLDIVNA in
    library SIERRA was used to access records from member DPPDIVSN of file
    DPPDIVSN in library SIERRA for reason code 1. The reason codes and their
    meanings follow:
    1 - Record selection.
    2 - Ordering/grouping criteria.
    3 - Record selection and ordering/grouping criteria.

    While I'm not advocating the use of STRSQL over ACS Run SQL scripts, there are times where it's handy for a quick 'n dirty and this query optimizer story in debug is a nice poor man's alternative if you don't have access to Visual Explain or don't have the time or skills for that.

    --Dan D

    ReplyDelete
    Replies
    1. If you use ACS, you can achieve exactly the same result, if you run the sentence with the Visual Explain options. Since it starts the debug mode.
      And you can get the info in a visual environment.
      Also you can check (with DSPJOBLOG) all that Simon explained.

      Delete

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.