RE: SQL versus READ for sequential processing -- RPG400-L

David,

You might need the S/O logicals for your RPG programs, but not for ANY use by SQL. The SQE will NEVER process a query that references a S/O logical. SQL prefers to reference PF object types directly, and THEN will decide which access paths it could use to optimally process the query. If you need your SQL result set to contain the same records as you'd see in your S/O logical, then simply add the select or omit logic in your WHERE clause.

Does your QAQQINI file ignore derived indexes?

Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Tuesday, October 14, 2008 3:10 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL versus READ for sequential processing

Vern,

I need the select/omit. I think the LF would have to be replaced by a view AND an index for the SQL statement to run with the SQE. But there are RPG's CHAINing on those LF so I can't replace them. Looks like we're stuck with the CQE in this case.

David FOXWELL
Service Informatique
Tél : 03 90 23 91 63
david.foxwell@xxxxxxxxx

P*Pensez à l'environnement avant d'imprimer ce message

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg Envoyé : lundi 13 octobre 2008 21:11 À : RPG programming on the AS400 / iSeries Objet : Re: SQL versus READ for sequential processing

David

I think the solution is to add an LF (index) - you can create with DDS or DDL - it does not matter. Make it have the same keys as your select/omit logical, just leave off the S/O criteria. You might even be able to create this, then delete the S/O logical, then recreate it - you would probably get access path sharing, which saves lots of space.

You should almost NEVER use LFs in a SELECT statement, for all the reasons Birgitta gave you.

HTH
Vern

David FOXWELL wrote:

Thanks Birgitta,

So there's no point in me taking the LF out of my request.
The PF indexed by the LF has 1 000 000 records at present. There are 6 LF based on it that use omit. The cure would be to stop using DDS, I suppose?

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Birgitta Hauser
Envoyé : vendredi 10 octobre 2008 18:39 À : 'RPG programming on the AS400 / iSeries'
Objet : AW: SQL versus READ for sequential processing

Hi,

Birgitta, are you saying that some of these won't work with
DDS-defined

logicals?

No I said, those enhancements cannot be executed by the CQE and all queries where DDS described logical files are specified will be rerouted to the CQE.

The SQE can also be used with DDS described logical files, as long as you only specify the physical files in your SQL statements and as long as there are no logicals with select/omit clauses built over the physical files you use.
If there are logical files with select/omit clauses the query will be rerouted to the CQE, too.
If you set the IGNORE_DERIVED_INDEX option in a copied QAQQINI file to *YES and use this QAQQINI file in your job with the cl Command CHGQRYA:
CHGQRYA QRYOPTLIB(MYLIB

Now the SQE can be used even if there are logical files with select/omit clauses. But the query optimizer will ignore all access paths stored in these logical files.
In this way you may end up by executing the query with the SQE but instead of using an index a table scan will be performed (because the formerly used access path is now ignored).

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: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Joe Pluta
Gesendet: Friday, 10. October 2008 12:49
An: RPG programming on the AS400 / iSeries
Betreff: Re: SQL versus READ for sequential processing

David FOXWELL wrote:

Have you an example of enhancements of SQE that cannot be used in
this

case? I don't think you mean that certain SQL commands wouldn't execute, oder?

That's a good question, David. I'm assuming Birgitta is talking about things like ROLLUP and/or full outer joins. Birgitta, are you saying that some of these won't work with DDS-defined logicals? I don't have a problem with the concept, because I am trying to move entirely to DDL, but it's good to know especially for clients with older legacy systems.

Joe
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.