Re: SQL Cursors and *ENDMOD

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You understand *ENDMOD correctly.

You're misunderstanding CLOSE.  The DB will often pseudo-close cursors
so that they can be reopened quicker.

If another process tries to get a lock, the DB will final close the
cursor so the lock can be granted.  But that takes time.  If the other
process doesn't wait, you'll get the in use error.

Two options (one or both should fix the problem)
1) If you're using SQL to access the file, you should be using SQL to
clear the file.  As of v5r3, performance of "delete from myfile" is as
good as CLRPFM MYFILE when possible (see the SQL manual for details)
2) Make sure the WAITFILE setting on the file is 30 or higher instead
of *IMMED   (30 is much longer than needed, but an SQL created table
defaults to 30)

Actually solution #2 is debatable
http://archive.midrange.com/midrange-l/200902/msg00560.html

But my experience in a production environment is that it does solve
the problem.

HTH,
Charles


On Tue, Apr 21, 2009 at 7:02 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:
> I am having problems with clears being able to clear members and I
> have traced the problem back to the SQL statements in a service
> program.
>
> It appears that I was not closing an SQL cursor after I was done with it.
>
> My question with all of that is how *ENDMOD works. My understanding of
> the close SQL Cursor option *ENDMOD is that at the end of the module,
> ie when I return from the call to the procedure in the module, that
> any SQL Cursors are closed but it does not appear that was what was
> happening.
>
> If you have not explicitly closed an SQL Cursor when you leave a
> procedure with the module compiled with CLOSQLCSR(*ENDMOD) is that
> cursor left open?
>
> Is my understanding of *ENDMOD incorrect or does it work differently
> for service programs? Does it mean when module is ended when the
> service program is shut down.
>
> Now the fun part is that when I run it in test I don't see the cursor
> open after it returns.
>
> Any help would be appreciated.
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.