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

Was hoping someone would keep this going!
Yes - RTFM showed me that DYNSLT enables access path sharing if the access path being shared has no S/O rules - otherwise, the S/O rules have to be the same, if not using DYNSLT.

Now I think David really does need both - he needs the S/O for his RPG stuff, and he wants to use SQE for his SELECT statement. Making the non-S/O logical (or index) just lets him take advantage of access path sharing, if he so chooses. Just has to change a QAQQINI file.

It's always good to save space with access path sharing - also takes less time to maintain indexes.

As I know you know!

Regards
Vern

Terrence Enger wrote:

On Tue, 2008-10-14 at 16:09 -0500, Vern Hamberg wrote:

David

I did not say to replace the S/O logical, I said to create an index (or LF) with the same key fields as the S/O LF but without S/O entries. Then recreate the S/O LF as is.

I just tried something like this - there is no sharing of access path.

Vern,

To share the index, I think you need the DYNSLT keywork in the S/O LF.
This is just for your curiosity, as there will be then no need for the
explicitly created index.

Cheers,
Terry.

So you can leave the select/omit alone, just create another index, either with DDS or with DDL, that has the same index fields as the S/O logical. Then set up your QAQQINI to ignore derived indexes - someone else has to give you the exact setup - then it should probably use SQE.

HTH
Vern

David FOXWELL wrote:

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.