Re: Permanent Access Paths and SQL

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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