Search code examples
sqlibm-midrangedb2-400

Omitting the creation of a temporary access path with DB2/400 when accessing DDS-defined tables with SQL

I have two table definitions in DDS, compiled into *FILE objects and filled with data:

Kunpf:

A                                      UNIQUE
A          R KUNTBL
A            FIRMA         60A         ALWNULL
A            KUNR           5S 0B
A            KUNID          4S 0B
A          K KUNR
A          K KUNID

Kunsupf:

A          R KUNSUTBL
A            KUNID     R        B      REFFLD(KUNID KUN/KUNPF)
A
A            SUCHSTR       78A
A          K SUCHSTR
A          K KUNID

I'm using the following statement in interactive SQL (STRSQL):

SELECT DISTINCT FIRMA, KUNR FROM KUN/KUNPF
 LEFT JOIN KUN/KUNSUPF ON (KUNPF.KUNID = KUNSUPF.KUNID)
 WHERE SUCHSTR LIKE 'Freiburg%'
 ORDER BY FIRMA
 FOR READ ONLY

Everytime I execute this statement, I'm getting a considerable delay until the answer screen opens up. Beforehand a message is shown, stating that a temporary access path is being created.

How can I find out which/how this temporary access path is created? My goal is to have this access path made permanent so it doesn't need to be rebuilt with every invocation of this query.

I searched the net (especially the IBM site) but what I found out was mostly for DB2 on z/OS. The F4-Prompting facility in STRSQL doesn't provide help: I was searching for something like EXPLAIN SELECT from MySQL. The IBM DB2 Advanced Functions and Administration PDF states that there's a debug mode but it seems that it is only available from some (old) Windows tool I don't remember to have.

I'm utilizing V4R5, if this is relevant.

Solution

  • to see the access path on the green screen...

    • strdbg

    • strsql

    • run your statement

    • exit f3

    • enddbg

    • dspjoblog

      the access path messages are at the bottom of the log f10 f18 afaik