RE: SQL debug gobbledygook

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de David FOXWELL
Envoyé : jeudi 26 novembre 2009 12:44
À : Midrange Systems Technical Discussion
Objet : 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.

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