AW: CLOSQLCSR=*ENDMOD

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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.