Hi,
Am I correct to assume that these steps are significantly more expensive
than a traditional OPEN?
If you mean the RPG Open or %OPEN, yes! If you want to compare performance
between native I/O and SQL, native I/O will be faster the first 2 times.
Because there is no optimization process at all, only the specified access
path must be opened. If the ODP for the SQL statement is reuseable and the
cursor will not be hard closed (i.e. *ENDMOD is not specified and the
activation group is not closed), SQL should be faster beginning with the
third execution (at least if more than a single row must be read). If not,
there must be something wrong and you have to check and analyse your SQL
statement.
Using OPEN and CLOSE in a subprocedure, will not decrease performance,
because only pseudo closes and opens are executed.
That means because the ODP was not deleted, close and open will only
actualize the data in the ODP.
You have to distinguish between FULL OPEN / HARD CLOSE and Pseudo Open /
Soft Close.
When a FULL OPEN must be performed the complete optimization (Step 1 and 2
in my previous explication) must be executed before the data can be
accessed. A hard close deletes the ODP, i.e. all temporary objects.
A hard close will be performed:
- at the close statement if the ODP is not reuseable
- at the end of the module if the (service-)program / module is compiled
with CLOSQLCSR *ENDMOD,
- as soon as the activation group will be reclaimed, if CLOSQLCSR *ENDACTGRP
is used
- the library list will be changed and the tables/files or views are used
unqualified in the SQL statements
- a OVRDBF for the unqualified used tables/files or views was used and the
appropriate DLTOVR will be executed.
When a Pseudo Open will be performed, step 1 and 2 of the full optimization
will not be executed, but only the data in the temporary objects will be
actualized and the pointer will be repositioned on the first row of the
result set. A pseudo open can only be performed successfully if the SQL
statement CLOSE was executed before.
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 AGlauser@xxxxxxxxxxxx
Gesendet: Monday, April 09, 2007 20:17
An: RPG programming on the AS400 / iSeries
Betreff: Re: Embedded SQL in an RPG *SRVPGM, file stays open
Thanks for (as always) a very informative and detailed response, Birgitta.
Thanks to the others who've responded as well. Birgitta's response
brings up a few more questions:
Birgitta Hauser wrote on 09/04/2007 12:57:32:
I'll try to explain how SQL works:
1. When executing an SQL statement an access plan must either be built
or at
least validated.
In the access plan the indexes to be used and temporary objects
(such
as
hash tables, bitmaps, relative record lists) to be built for to open
the
data path are desrcibed. In this step all access paths (in either DDS
described logical files or SQL indexes) will be checked and the
statistics
will be consulted.
2. After the access plan is built, validated and actualized the
temporary
objects will be created and filled with data, that can be accessed,
i.e.
the
access path is opened (ODP).
Both steps are very expensive and should be avoided whenever possible.
Am I correct to assume that these steps are significantly more expensive
than a traditional OPEN?
I generally do an OPEN and CLOSE each time a subprocedure in a service
program is called, mostly to avoid the need for initialization and cleanup
subprocedures (which would respectively open and close any files used by
the SRVPGM). I was trying to apply the same logic to SQL, which it seems
will not work the way I had hoped. Perhaps the solution to my problem is
to rethink my opening/closing approach. On that note - can anyone comment
on the performance of %open()?
Thanks again,
Adam
Attention:
The information contained in this message and or attachments is
intended only for the person or entity to which it is addressed and may
contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this
information by persons or entities other than the intended recipient is
prohibited. If you received this message in error, please contact the sender
and
delete the material from any system and destroy any copies. Thank you for
your
time and consideration.
Attention:
Le contenu de ce message et(ou) les fichiers ci-joints sadressent
exclusivement ` la personne ou -entiti ` laquelle ils sont destinis. Ils
peuvent
contenir de linformation confidentielle, protigie et(ou) classifiie. Il est
strictement interdit ` toute personne ou entiti autre que le(la)
destinataire
privu(e) de ce message dexaminer, de riviser, de retransmettre ou de
diffuser
cette information, de prendre une quelconque action en fonction ou sur la
base
de celle-ci, ou den faire tout autre usage. Si vous avez regu ce message
par
erreur, veuillez communiquer avec lexpiditeur(trice), supprimer ce message
et
les fichiers ci-inclus de tout systhme, et en ditruire toutes copies,
quelles
soient ilectroniques ou imprimies. Nous vous remercions de votre entihre
collaboration.
midrange.com
