Can you clarify below to help me better understand "as soon as the module or
better a procedure located in this module is left."?
1) In a Main Module ==> A Cursor is opened within a Sub-Routine (BEGSR).
Will the cursor be closed after exiting the sub-routine?
2) In a Main Module ==> A Cursor is opened within a Sub-Procedure. Will the
cursor be closed after exiting the sub-procedure?
3) In a Main Module ==> A Cursor is opened within a Sub-Routine (BEGSR) or
Sub-Procedure. Will the cursor be closed after exiting the program with
*LR=*OFF?
4) In a Non-Main Module ==> A Cursor is opened within an exported
Sub-Procedure. Will the cursor be closed after exiting the sub-procedure?
5) In a Non-Main Module ==> A Cursor is opened within a non-exported
Sub-Procedure. Will the cursor be closed after exiting the sub-procedure?
6) In a service program ==> A Cursor is opened within an exported
Sub-Procedure. Will the cursor be closed after exiting the sub-procedure?
7) In a service program ==> A Cursor is opened within a non-exported
Sub-Procedure. Will the cursor be closed after exiting the sub-procedure?
Thank you
"Birgitta Hauser" wrote in message
news:mailman.7527.1320158659.2520.rpg400-l@xxxxxxxxxxxx...
CLOSQLCSR=*ENDMOD means:
The ODPs for all SQL statements executed in the module are deleted as soon
as the module or better a procedure located in this module is left.
For all subsequent calls a FULL OPTIMIZATION/FULL OPEN must be performed,
that means the access plan must be created (or at least validated), the
available access paths (indexes and keyed logical files) must be estimated
and after the access plan is built or updated the temporary objects that are
described in the access plan (such as hash tables or RRN lists) must be
created and filled with data (i.e. the data path is opened ODP). Creating
the ODP is the most time consuming step in the optimization process!
If CLOSQLCSR=*ENDACTGRP is specified, the ODP will not be deleted (after the
second execution). For all subsequent calls only the data in the temporary
objects are updated, i.e. the ODP is reused and only a PSEUDO OPEN must be
performed. All sub-sequent calls should be much faster than the first and
second execution.
CLOSQLCSR=*ENDACTGPR also works correctly when debugging a program or
procedure.
But when using serial cursors and the cursor is not closed it cannot be
opened again. The best trick is, to always close the cursor (i.e. execute
the SQL statement CLOSE cursor) before opening it. If it was not closed, it
is now closed. If it was already closed, ignore the appropriate
SQLCOD/SQLCODE or SQLSTT/SQLSTATE.
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 Gary Thompson
Gesendet: Tuesday, 01. November 2011 14:55
An: RPG programming on the IBM i / System i
Betreff: Re: CLOSQLCSR=*ENDMOD
Hockchai Lim wrote:
CLOSQLCSR=*ENDMOD ==> SQL cursors are closed and SQL prepared statements
are implicitly discarded when the module is exited. LOCK TABLE locks are
released when the first SQL program on the call stack ends.
can someone help me understand what “when the module is exited” in the
above statement means? As far as I know, module object is not an
executable object. So, I couldn’t quite understand what it mean?
I can't answer your question about module closing, but I find
*ENDMOD useful when debugging. When specified, I can
repeatedly call the program being debugged and the cursor
"works as expected" - meaning, on subsequent calls, the
cursor starts with the first row.
midrange.com
