× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Just to make things worse, my original selection, that took 5 seconds to display in a subfile with rpg, now takes a fraction of a second in an SQL session!

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.