× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact copyright@midrange.com.

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.