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

Birgitta

That was not the issue - no SQL indexes were in use here. I simply had not set the DYNSLT keyword on the S/O logical file, as pointed out by a couple others. Once I used that keyword, the access path of another logical file WAS shared.

See my subsequent post for a little bit more.

I have not tried creating an S/O logical file that has the same key fields as an SQL index. That would be an even better option for David, assuming it would work. Just create the index with key fields the same as his S/O logical and no S/O criteria. Then delete and recreate the S/O logical using DDS. Ba-da-boom Ba-da-bing! Shared access path, larger access path, all the best of the world!

Regards
Vern

Birgitta Hauser wrote:

Hi,

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

There is, but you have to know an SQL index will never share access path
with a DDS described logical file because of the larger page size of the SQL
index (64K versus 8K).
But a DDS described logical file can share access path with an SQL index
(and even gets inherited the larger page size).
In this way delete the logical file, create the SQL index and recreate the
logical file and voilà only a single access path will be created.

If the ignore derived index option in the QAQQINI-file is set to *YES, the
SQE can use the newly created SQL index while the DDS described logical file
can be used by native I/O and only a single access path must be maintained. Keep in mind each additional access path will decrease performance, because
access paths get actualized as soon as a row gets inserted, updated or
deleted in the base table (or physical file).

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 Vern Hamberg
Gesendet: Tuesday, 14. October 2008 23:09
An: RPG programming on the AS400 / iSeries
Betreff: Re: SQL versus READ for sequential processing

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