Re: Permanent Access Paths and SQL

Jonathan,

My first suggestion would be that you run your SQL statements
referencing ONLY the physical file. Let SQL choose the best access
path and qry engine (SQE .vs. CQE) and, unless you are running V6R1 (o
whatever is called these days), use the SQL WHERE clause instead of
the DDS's OMIT/SELECT. IIRC, with V6R1 you can have the SELECT/OMIT
inside the CREATE INDEX stmt..

And, yes, the CREATE INDEX statement creates and indexed logical file
BUT, most of the times SQL indexes are faster than DDS defined ones.

You can find a nice paper about it, written by Dan Cruikshank, here:

http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf

Hope this helps.

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries



On Mon, Aug 24, 2009 at 9:25 AM, Jonathan Mason<jonathan> wrote:

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