SQL - File staying locked.

[rob@xxxxxxxxx]

I've very recently gotten into the habit of using 
Commitment control . . . . . . . . . . . . . . :   *NONE
Close SQL cursor . . . . . . . . . . . . . . . :   *ENDMOD
Hoping it would no longer leave certain files locked when I do simple 
statements like:
select substr(a.upaccd,10,5), a.uptext into :EmpNum, :UserText
  from userlist a
  where a.upuprf=:USERNAME

However it does on occasion.  This same program and this same sql.  I did 
some reading on *ENDMOD and came up with:
*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.

Then I thought, "Aha, sometimes I execute this program from somewhere 
below another SQL program in the call stack and that is what the issue 
is".  So I created a simple sqlrpgle program:
      /DEFINE DSpec
     D TEST            s             10a
     D qcmd            pr                  extpgm('QCMD')
      /UNDEFINE DSpec

     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC

     C/EXEC SQL
     C+ Select adfile into :test
     C+ from acctdel
     C+ where adfile='APTRAN'
     C/END-EXEC

      /free
       qcmd();
       *inlr=*on;
       return;
      /end-free

But, when I execute the qcmd() and try calling my other test program it 
doesn't leave it locked.  Oops, yes it does, if I call the program twice. 
And I tested that with a couple of very simple SQL programs.  You've got 
to call them twice.

Isn't there some (expletive deleted) way to have the program that locks 
the table in the first place not lock the table?  Yo!  I'm done with it! 
When I say *ENDMOD I mean it!  Not, only if there was no SQL program 
further up the call stack or maybe even parralell in the call stack, and 
maybe even hidden in some other activation group.  I'd actually prefer an 
ENDSTMT for some of these.  I don't understand keeping an internal cursor 
opened on a simple select into statement (one with no explicit Declare 
cursor or Open cursor - just a simple select into).  And even if there 
were a point, I don't care!

And trying this in a mixed ile/opm environment is a real joy.  This is one 
time when *new on the activation group really helps.  But if that program 
calls an opm program (rpg3 or rpg4 with dftactgrp(*yes)) then you're right 
back where you started from.

I really don't want to replace all my simple select into's with
declare cursor
open cursor
fetch
close cursor

(I can see Joe Pluta gagging over that, but he probably doesn't use SQL 
except with a declare cursor.)   :-)

V5R2
cume=TL03077
WRKPTFGRP
PTF Group             Level  Status 
SF99519                  47  Installed
SF99502                   6  Installed
SF99271                   7  Installed
SF99190                  10  Installed
SF99169                   8  Installed
SF99149                   3  Installed
SF99148                   3  Installed
SF99098                   8  Installed
SF99085                   3  Installed
SF99039                   2  Installed


Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin