Re: Permanent Access Paths and SQL

Jonathan,

You'd need to use visual explain or at the vary least run the
statement in debug and see the details of the access path it is
recommending.

You are correct, that the system wants you to CREATE INDEX.

Note that an SQL Index, while stored in a LF object, is slightly
different than a DDS created logical. The page size is normally
larger on the index, plus I think there may be a few more statistics
collected. So the system may ask for an index even if a DDS logical
exists. If possible, simply replace the DDS logical with an index of
the same name. Another option, delete the logical, create the index
and recreate the logical. The system will share the access path
stored in the index with a DDS logical, but not vice versa.

HTH,
Charles

On Mon, Aug 24, 2009 at 9:55 AM, Jonathan
Mason<jonathan.mason@xxxxxxxxxxxxxxxx> 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



_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.