AW: SQL debug gobbledygook

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

If you specify a logical file in an SQL statement, the execution is always
returned to the CQE.
The query optimizer needs to rewrite the SQL statement based on the physical
files/SQL Tables, but the SQE (SQL!!!) Engine is not able to analyze DDS
described logical files. In this way the query is rerouted to the CQE.

After rewriting the SQL statement the optimization process begin. At this
time the optimizer does not know anymore that there was a logical file with
a key, instead the optimizer estimates all available access paths. The CQE
optimizer starts with the latest created SQL index or DDS described logical
file. Also the CQE optimizer may get a timeout before it estimated the last
available access path. In this way the optimizer could have got an timeout
and created an access path to your logical file.
In either way try to move the SQL statement to the SQE. 

Speculating why the CQE optimizer handles in this or that way is not worth
the time spent.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von David FOXWELL
Gesendet: Thursday, 26. November 2009 10:42
An: Midrange Systems Technical Discussion
Betreff: 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?