Re: SQL debug gobbledygook -- MIDRANGE-L

David

One important principal - NEVER (almost) use a logical file in a SELECT - the main reason is not that SQE can't use it, in my opinion, but that the optimizer has to ignore it and go back to using CQE and find its own way - wasting time.

I think that having a good index (btw, it can be a logical file) is more important that whether the engine is SQE or CQE. SQE is the desirable one but might not be available for other reasons, and CQE can run just as well in some circumstances. SQE was first developed with the intent of running complex queries as well as DB2 in AIX did within some tolerances. The test statements were the TPC-H set, and anyone can look those up.

A good index will cover several parts of a SELECT - joining, ordering, whereing (made that up!), and grouping - I don't think that is the order - there are some articles about indexes at Centerfield Technology and in white papers at www.iseries.ibm.com/db2

If you have the S/O logical and need it for native IO in RPG, then to increase the chances of using SQE, set up a QAQQINI file in some library other than QUSRSYS, change the entry for ignoring derived indexes to *YES, and set the job to use the new QAQQINI by running CHGQRYA in that job. The user of the job has to have job control explicitly, even if they are the user of the job.

Did I remember to say that you should never use an LF in a SELECT?

Vern

David FOXWELL wrote:

Hi Birgitta,

I realise the problem with not being able to use SQE because of the select in the DDS of the logical.

I think the penny is beginning to drop : I created the SQL index that was indicated in QTEMP. Ran the request on the physical file and it runs immediately.

What I did not realise was that if I were to use SQL in my RPG program, I'd be completely stuffed. We'd have to have the index as well as the logical.

How would you recommend replacing the select/omit of the logical?

-----Message d'origine-----
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Birgitta Hauser
Envoyé : jeudi 26 novembre 2009 11:15
À : 'Midrange Systems Technical Discussion'
Objet : AW: SQL debug gobbledygook
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?
--
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 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.