I believe that it was because L2 had selection criteria.  I've had a
performance concern visa vie SQL since we have been on V5R2.  I seems that
any SQL statement built over logically joined files or logical files with
selection criteria seemed to always build an access path.  I spoke to IBM
about the issue and was told to "tune" the database by building indexes.  My
response was I have indexes, that's why I created the logicals.  They
responded that I should use the Create Index sql command.  I did and they
were right.

-----Original Message-----
From: rick.baird@xxxxxxxxxxxxxxx [mailto:rick.baird@xxxxxxxxxxxxxxx] 
Sent: Thursday, March 18, 2004 12:45 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Query Optimizer Access Path Selection via Opnqryf


hey all,

I decided to do some bench testing on using opnqry with different types of
logicals to see what access paths are chosen.

i have a file - about  500,000 records, not keyed.

My CL is as follows:

PGM
OVRDBF     FILE(SHP72PTD) SHARE(*YES)
OPNQRYF    FILE((SHP72PTD)) QRYSLT('FRMLC9 = "9" *AND +
             FRMLOC = "81" *AND SHPFOR = "SHP"') +
             KEYFLD((FRMLC9) (FRMLOC) (TOLOC) (PRDCDE) +
             (SHPFOR))

/* shptestr does nothing but read every record in the file, input primary,
by key */

CALL       PGM(SHPTESTR)

CLOF       OPNID(SHP72PTD)
DLTOVR     FILE(*ALL)
ENDPGM

I have 2 logical file DDS members (as yet not built)

 * SHP72PTDL1
A          R SHP72PTDR                  PFILE(SHP72PTD)
A          K FRMLC9
A          K FRMLOC
A          K TOLOC
A          K PRDCDE
A          K SHPFOR

and

 *SHP72PTDL2
A          R SHP72PTDR                  PFILE(SHP72PTD)
A          K FRMLC9
A          K FRMLOC
A          K TOLOC
A          K PRDCDE
A          K SHPFOR
A          S FRMLC9                    COMP(EQ '9')
A            FRMLOC                    COMP(EQ '81')
A            SHPFOR                    COMP(EQ 'SHP')

note, the L2 logical is exactly the sequence and selection criteria as the
opnqryf.

I ran the call the CL 3 times with debug turned on so I could see the query
optimizer messages.

1. no logicals built:  it created the access path, as expected.

2. L1 only built:  it considered all paths and used the existing L1 path, as
expected.

3. L2 only built.  it considered all paths, and decided to create it's own.

why didn't it use the L2 logical?  it was exactly what it needed?

I'm assuming it's because it won't consider existing access paths that have
record selections, because the chances if it meeting the selection criteria
is slim to none.

I guess what I'm asking is, if you're going to do record selection, rather
do it via opnqryf than by logical, because you'll end up having a ton of
single purpose logicals, and if you do ever happen to use opnqryf, your
logicals with record selection won't ever be considered - wasted access
paths.

Comments?

BTW, I have the bench times involved too if anyone is interested.

Thanks,


_______________________________________________
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.

-----------------------------------------
This message was scanned for viruses.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.