Re: SQL - File staying locked.

I have never posted to this particular list before but I thought I should
sign up and explain a discovery since I was the one Rob was helping with
these problems.  We were having problems with a never-ending job keeping
files open and it was probably due to *endactgrp keeping files locked.
In experimenting, I made a discovery I'm not sure you know about.  I think
it has to do with looking for SQL higher in the call stack for *endmod and
*endpgm.  It shows cases where another SQL option created could save jobs.

Let's say program A has SQL and with closqlcsr *endmod and it calls program
B with SQL with closqlcsr *endactgrp.  A is true ILE with let's say
actgrp('QILE') and B is OPM.  Also, we have program C with closqlcsr
*endpgm as an OPM RPG.  Now, the programs have to actually have SQL
statements in order to have the SQL options on the compile.  Otherwise,
they are compiled as RPGLE or RPG.

Program A brings up a command line.  Now, we call program B and files are
locked after exit.  Now, we call progam C and its files are now locked
because of B's *endactgrp SQL state.  Now, we are back in program A at a
command line with B and C's files locked.  We exit program A and C's files
are now deleted but not B's since we are still in the default activation
group.  Now, we change program A to not use any SQL and repeat the process
again.  This time when we exit program A, all files are still locked.
Let's verify with 2 more simple tests.  We just call the non-SQL program A
then program C now.  Back in program A, no files locked.  Now, start over
with SQL program A instead of non-SQL program A.  Just call program C
again. We have C's files open.  Exit program A and C's files close.

So, in theory, if we were to change a non-SQL program to use some dummy SQL
statement, all programs with *endmod and *endpgm with open files in that
job would be closed after exiting that first SQL program except the files
still open by the *endactgrp.  Furthermore, if the original was non-SQL,
then program B with *endactgrp would have to have been called in order to
keep C's files open.  One program defined wrong ruins the whole job.
Actually, any program with *endmod or *endpgm would keep files open until
that first SQL program exits.  Sound crazy?  When *endmod and *endpgm are
the best options, yeah, it sounds crazy as Rob was implying.  Especially
when the first program is not SQL and the files stay open until the end of
the job when they were OPM (default activation group).  That is
unacceptable, especially when the job is a never-ending job!  Shouldn't
there be an SQL option for close the files no matter what?

Instead of making sure there were no programs with *endactgrp, the plan is:
1. Use ActGrp(*NEW) for program A then make sure program B or C is *CALLER
if true ILE.  This takes care of files opened by ILE SQL.
2. Execute RCLRSC.  This cleans up files opened by OPM SQL.

Thanks,
Craig Strong

** Rob wrote:

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