RE: SQL debug gobbledygook

David,

Have you tried running the same query against the physical file instead of the logical?

Supposedly, the system (SQL optimizer) will then choose the access path that is most performant, or create it on-the-fly if it doesn't exist.

Also, I think you want surname >= 'F' in your SQL statement.

HTH,


Peter Colpaert
Software Engineer, Consumer Luminaires
Industrieterrein Satenrozen 11, 2550 Kontich, Belgium
Tel. +32/ 3 450 74 09, Fax +32/ 3 450 74 33, Internal 1317
peter.colpaert@xxxxxxxxxxx, www.philips.com

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: donderdag 26 november 2009 10:42
To: Midrange Systems Technical Discussion
Subject: SQL debug gobbledygook

Hi to all,


I have an RPG pgm that searches a client file using surname, forename
and date of birth.

If I enter surname='F', forename =' ' and date of birth='26.11.1988'
(am I really THAT old), the program takes a long time to show the
results.

It does a SETGT on a logical with the keys surname, forename, date of
birth and client number set to 0, followed by reads until surname isn't
like 'F'.


I wanted to check the file in an SQL session. I typed

SELECT CCLTNM, CCLTPR, CNADL FROM mylogical WHERE surname>'F' ORDER
BY surname, forename, dateOfBirth

to see all the records my RPG program had to read.

This request also takes a long time to execute. In debug mode, I see
"Reason Codes for supplementary access paths have been used". Message
CPI432A emmitted, something about timing out of the optimizer. Maybe
because of Select in the DDS of the logical and the non compatability
with SQE? This is why I specified the logical on my SQL statement.

Then I see "Access path created for mylogical file".
This is because of code reason 1 :Exécution des critères de
classement/groupage indiqués. Which I think means I've specified the
sorting.

The access path created is on surname,forename, dateOfBirth. Which is
the same as the file in the request.
Why is it creating this access path?


Would someone enlighten me?
--

The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.