Contrary to native I/O, SQL statements have to run through the optimization.
The query optimizer checks the SQL statements, may rewrite it and may add
additional information.
After it is checked whether there exists an access plan (in the SQE Plan
Cache or Program object).
If an access plan is found it is validated if no access plan exists it has
to be created from the scratch.
In both cases the existing access path (indexes, logical files) are
estimated in composition with the statistic data.
An access plan describes, database objects (tables, indexes) are access,
which access methods (table scan, index scan, index probe) is used, which
temporary objects (hash table, relative record list etc.) are needed and how
these objects are linked.
After the access path is built, the ODP (Open Data Path) is opened based,
temporary objects must be built and linked together based on the access plan
and populated with data.
Opening the data path is the most time consuming part of the query
execution.
--> Full Open
For getting the best performance, SQL tries to keep the ODPs opened, i.e.
does not delete the ODP (or the temporary objects).
So the next run only the data in the temporary objects have to be refreshed.
--> Pseudo Open
A close Cursor within an program only flags that the data can be refreshed
(reopening a cursor without having closed it before will not work).
Compile Option CLOSQLCSR = *ENDMOD is not a good idea, because the ODPs get
closes as soon as the module is left. In this way the files are closed, but
each time the program/module is run FULL OPENs must be performed.
Default Compile Option CLOSQLCSR = *ENDACTGRP is the better choice. ... But
if you run your program with activation group *NEW, the ODPs get also
deleted with the end of the program. In this way each time the program is
performed, FULL OPENs must be performed.
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!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Gerald
Magnuson
Sent: Montag, 6. März 2017 20:03
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-L@xxxxxxxxxxxx>
Subject: SQL processing, files still show open, even after cursor is closed.
I am loading a RPG subfile via a SQL cursor. I thought that when the
cursor was closed,
that would close the files from the SQL statement.
is there something I should do to make sure the files get closed?
--
This is the RPG programming on the IBM i (AS/400 and 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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
