× 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.



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.



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 [javascript protected email address].

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