Birgitta,
Thanks for the in depth explanation. In my case the SQL is running in a
stand-alone process which contains one program. So when it (the module)
ends, I'm pretty much through with the file so closing the cursor should not
create a performance issue at that point - which isn't to say some yahoo
can't run the same menu option 2 seconds later!
Jerry C. Adams
IBM i Programmer/Analyst
You wouldn't have won if we had beaten you. -Yogi Berra to Al Campanis
(Dodgers exec)
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Birgitta Hauser
Sent: Tuesday, May 01, 2012 3:59 AM
To: 'RPG programming on the IBM i / System i'
Subject: AW: File Still Open
Even using the *ENDMOD may not close the cursor. SQL has soft and hard
closes. It try's to keep the file open in case it needs to be opened again.
You may close the cursor, do *ENDMOD and still see it. If it is a soft close
and some other process needs the file in a higher level of open, the
database releases it to be closed. If it is a hard close(File still open),
the database won't release it.
Are you really sure about that?! I'm NOT convinced it works in this way!
Yes there are hard and soft closes and full and soft/pseudo opens!
A full open means the complete optimization must be executed, i.e. an access
plan must be either built or validated, the temporary objects described in
the access plan must be created and filled based on the access paths and
other techniques (such as table scans) also described in the access plan.
This second step is the most time consuming part of the query execution.
After the first execution the ODP will be deleted (hard close).
The next time the same SQL statement is executed within the same
job/activation group, the access plan gets validated and the ODP Open (2nd
FULL OPEN).
After the second execution and if the ODP is "reusable" (... there are
several reasons why a ODP could not be reusable), the ODP will not be
deleted. (SOFT CLOSE).
Next time the statement is executed (within the same Job/Activation Group)
only the data in the temporary objects of the ODP will be updated (PSEUDO
OPEN).
When using option CLOSQLCSR *ENDMOD a hard close will be performed as soon
as the module ends (i.e. is removed from the call stack).
Next time the SQL statement is executed within the same job/activation group
FULL OPTIMIZATION/FULL OPEN must be performed, i.e. access plan validation
and ODP Opening.
For performance issues CLOSQLCSR=*ENDACTGRP should not be changed, the only
reason why to use CLOSQLCSR=*ENDMOD is if it is absolutely sure that the SQL
statement will be only executed once!
For more information have a look at :
Performance and Query Optimization
Chapter: Application design tips for database performance
- Reducing the number of open Operations
- Retaining Cursor Position
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fr
zajq%2Fopens.htm
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 Alan Campin
Gesendet: Monday, 30.4 2012 19:21
An: RPG programming on the IBM i / System i
Betreff: Re: File Still Open
Even using the *ENDMOD may not close the cursor. SQL has soft and hard
closes. It try's to keep the file open in case it needs to be opened again.
You may close the cursor, do *ENDMOD and still see it. If it is a soft close
and some other process needs the file in a higher level of open, the
database releases it to be closed. If it is a hard close(File still open),
the database won't release it.
On Mon, Apr 30, 2012 at 11:09 AM, Jerry C. Adams <midrange@xxxxxxxx> wrote:
Thanks, Rory. That did the trick. Never used that before because,
usually, I use cursors frequently and always CLOSE cursor inside the
RPGLE
program.
Didn't know I had a cursor in this one since I never DECLAREd one. I'm
a real put putz when it comes to this SQL stuff. The default (at
5.1 anyway) is *ENDACTGRP. If I had called the program via a CL, I
could have used the RCLACTGRP command to the same effect, right?
Jerry C. Adams
IBM i Programmer/Analyst
We've got to pause and ask ourselves: How much clean air do we need?
-Lee Iacocca
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Rory Hewitt
Sent: Monday, April 30, 2012 11:24 AM
To: RPG programming on the IBM i / System i
Subject: Re: File Still Open
Have you tried specifying COLSQLCSR(*ENDMOD) on the CRTSQL* command?
The default is CLOSQLCSR(*ENDSQL), I believe, which probably isn't
what you want.
On Mon, Apr 30, 2012 at 9:19 AM, Jerry C. Adams <midrange@xxxxxxxx> wrote:
First, V5R1.
I have an SQLRPGLE program that uses embedded SQL to update multiple
records (potentially, anyway) in a master file. I build a string
based upon the user's options and then use EXECUTE IMMEDIATE
:sqlstring. The master file is referenced only in the sqlstring;
i.e., it is not defined in the F-specs.
The string is: UPDATE TSTQS36F/"A.IVMAST" SET impidx = 'X ' ,
imprcd =
'
'
WHERE impidx = 'CONW' AND imprcd = ' '
I never noticed before that, when the program ends, the master file
is still open when I run DSPJOB option 14. Is this normal, some
keyword I need to add to the UPDATE string, or something else to be
sure the file is closed?
Jerry C. Adams
IBM i Programmer/Analyst
Everything that can be invented, has been invented. -Charles Duell, U.S.
Patent Office Director (1899)
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
--
This is the RPG programming on the IBM i / System i (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.
--
Rory Hewitt
http://www.linkedin.com/in/roryhewitt
--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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.
midrange.com
