I think that clears me up. Thank you for taking time to do this.
"Birgitta Hauser" wrote in message
news:mailman.8005.1320303220.2520.rpg400-l@xxxxxxxxxxxx...
... assumed we are working with CLOSQLCSR = *ENDMOD:
1) In a Main Module ==> A Cursor is opened within a Sub-Routine (BEGSR).
Will the cursor be closed after exiting the sub-routine?
A subroutine is not a procedure and always run in the same module as the
(main) procedure to which it belongs to.
The module is not left when calling a subroutine nor will it be left when
the subroutine ends.
It will be left as soon as your main procedure ends.
ODP gets deleted when the main procedure ends.
2) In a Main Module ==> A Cursor is opened within a Sub-Procedure. Will the
cursor be closed after exiting the sub-procedure?
Because the sub-procedure runs in the same module, the module is not left
when calling the procedure.
The module will be left as soon as your main procedure ends.
ODP gets deleted when the main procedure ends.
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?
*LR does not influence SQL at all! *LR is part of the RPG cycle.
In this way your cursor gets closed as soon as your main procedure ends.
ODP gets deleted when the main procedure ends.
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?
If the exported procedure is called from a different module, the ODP will be
deleted as soon as the procedure is left.
If the exported procedure is called from a procedure within the same module,
the ODP will be closed as soon as the caller procedure ends.
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?
Because the procedure is located within the same module, the ODP will be
deleted as soon as the caller procedure leaves the module
6) In a service program ==> A Cursor is opened within an exported
Sub-Procedure. Will the cursor be closed after exiting the sub-procedure?
In a service program modules are bound either! Same situation as 4.
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?
See 5 and 6.
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 Lim Hock-Chai
Gesendet: Wednesday, 02. November 2011 16:01
An: 'rpg400-l@xxxxxxxxxxxx'
Betreff: Re: AW: CLOSQLCSR=*ENDMOD
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
