AW: SQL debug gobbledygook

Hi,

drop your logical file, create the SQL index (but in your data library and
not in QTEMP).
... if necessary recreate your logical file. In this you'll get 2 logical
file objects, the DDS described logical file can be used with native I/O and
the SQL index can be used by the query optimizer. But if you drop before
creating the SQL index and recreate it after, you'll get only a single
access path that must be maintained.

BTW may be you can omit the logical file at all. Even though you cannot
specify an SQL index in a SQL statement, you can use an SQL index with
native I/O like any keyed logical file. That means specify it in the
F-Specs, execute CHAINs, READs, WRITEs and UPDATEs as usual with native I/O.

If you are already on release 6.1 you can add where conditions to an SQL
index, which will replace the Select/Omit clauses in your logicals.

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 12:44
An: Midrange Systems Technical Discussion
Betreff: RE: SQL debug gobbledygook

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.