CODEWITHSUNDEEP
sqlibm-midrangedb2-400
PoC
PoC

Reputation: 591

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.

Upvotes: 0

Views: 129

Answers (2)

Charles
Charles

Reputation: 23823

v4r5??? That's like 20 years old...

For the IBM i, the "Run SQL Scripts" component of the old Client Access For Windows iSeries Navigator component and the new Access Client Solutions (ACS) contains Visual Explain (VE).

Luckily it seems though it was added to v4r5 http://ibmsystemsmag.com/ibmi/administrator/db2/database-performance-tuning-with-visual-explain/

Just start iNav, right click on "Database" and select "Run SQL Scripts"

Paste your query there and click "Visual Explain" -->"Run and Explain"
(or the corresponding button)

Optionally, in green screen.
Do a STRDBG to enter debug mode, F12 to continue and then go into STRSQL. The Db optimizer will then output additional messages into the joblog giving you more information about what it is doing..

Upvotes: 1

danny117
danny117

Reputation: 5651

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

Upvotes: 3

Related Questions