|
-----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-----Birgitta Hauser
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de
Envoyé : jeudi 26 novembre 2009 11:15 À : 'Midrange SystemsTechnical
Discussion'not able to
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
analyze DDS described logical files. In this way the queryis rerouted
to the CQE.begin. At
After rewriting the SQL statement the optimization process
this time the optimizer does not know anymore that therewas a logical
file with a key, instead the optimizer estimates allavailable access
paths. The CQE optimizer starts with the latest created SQLindex or
DDS described logical file. Also the CQE optimizer may geta timeout
before it estimated the last available access path. In this way thepath to your
optimizer could have got an timeout and created an access
logical file.way is not
In either way try to move the SQL statement to the SQE.
Speculating why the CQE optimizer handles in this or that
worth the time spent.the stars."
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among
(LesDavid FOXWELL
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
Gesendet: Thursday, 26. November 2009 10:42surname, forename
An: Midrange Systems Technical Discussion
Betreff: SQL debug gobbledygook
Hi to all,
I have an RPG pgm that searches a client file using
and date of birth.birth='26.11.1988'
If I enter surname='F', forename =' ' and date of
(am I really THAT old), the program takes a long time to show theforename, date of
results.
It does a SETGT on a logical with the keys surname,
birth and client number set to 0, followed by reads until surnamemode, I see
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
"Reason Codes for supplementary access paths have beenused". Message
CPI432A emmitted, something about timing out of theoptimizer. Maybe
because of Select in the DDS of the logical and the noncompatability
with SQE? This is why I specified the logical on my SQL statement.specified the
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
sorting.please take
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,
a moment to review the archives atplease take
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,
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.