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


  1.  Home
  2. RPG400-L
  3. October 2008

AW: SQL versus READ for sequential processing

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.