Permanent access path indeed ought to mean an SQL index. Most frequent
difference between a keyed LF and an SQL index is in the logical page size
(LF usually defaults to 8k and sql index usually defaults to 64K). There
could be other differences (i.e. Select/Omit criteria in the LF).
SQL operations prefer larger logical page size, so that could be a reason
for the recommendation. Why not build an index and see if performance
improves? It doesn't really cost you anything but few machine cycles. And if
it doesn't help, drop the index.
Since system is already building it on the fly, I strongly suggest you keep
it permanently. Whether you should do this or not really depends on the run
frequency and business priority of this particular query (i.e. is it OK to
wait 120 seconds for it to run).
I think you may have other issues though. You are querying an LF directly
(it's in your FROM clause). This restricts you to CQE ([old] Classic Query
Engine), which has been in maintenance only mode for about 12 years now. You
should try and get your queries to run in SQE ([new] SQL Query Engine),
which is where all of the enhancements and IBM investment have been going to
for the past 12 years (at least; perhaps longer than that), and performs
better than CQE in over 95% of the queries. Sometimes MUCH, MUCH better.
Try rewriting your query so it references the physical file only. If you
find it difficult to replicate keyed LFs S/O functionality, try creating an
SQL view that mimics its selection/omission criteria, and use it in the FROM
clause in place of the LF. If you're going to go this route, you absolutely
should build an SQL index that was advised (or a new one that will be
advised once you make the switch to querying the view instead of the LF),
since sql views have no keyed access path to improve performance.
When building an index, selection criteria (the WHERE clause) is usually of
much higher priority than the ORDER BY clause.
Hth, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: Permanent Access Paths and SQL
We have an SQL statement that occasionally runs really slowly (up to 120
seconds). When I run the statement interactively in debug mode, the
joblog shows a recommendation that a permanent access path is built as
that may help the query run faster.
The SQL runs over a logical file that is keyed on account number plus
another five sub-keys and only contains records with the open amount
field set to zero. The logical file is joined to the physical file
using a left outer join and relative record numbers (amongst other
criteria) and the resulting data is sequenced to match the sequence from
the logical file.
The suggestion though, says to create a permanent access path for the
logical file and then says to base it over the physical. The question I
have though is how do you create a permanent access path?
From reading various references, I got the impression that this meant
use the CREATE INDEX statement, but that just creates a logical file and
as the logical file I want already exists it doesn't seem to make much
sense to create another one.
Thanks
Jonathan
midrange.com
