On 29-Oct-2015 14:19 -0500, darren wrote:
<<SNIP>> I think you're saying that the locks I'm seeing are the
result of leaving cursors open.
Presumed to be, that the cursors are pseudo-closed. The
pseudo-closed cursor is not the same as /leaving cursors open/; instead,
that the SQL has explicitly decided to override the request to CLOSE,
and effect instead of the requested full-close, effect a pseudo-close
whereby the ODP remains.
That is not the case. This issue happens when cursors are closed,
The request to CLOSE is not always honored by the SQL, specifically
to avoid the overhead of a full-open after a full-close; i.e. the SQL,
for performance reasons [by minimizing resource utilization], will both
leave the ODP active *and* give a successful return code on the CLOSE.
That effect, a successful close while the ODP remains active, is
referred to as a pseudo-closed cursor. The intention being, that there
might be an additional future OPEN, for which having left that cursor
remain /effectively/ open [despite having told the program that the
cursor was closed], could benefit from being re-used rather than the ODP
having been destroyed and then created-again later /from scratch/.
and we've also taken the added precaution of using the following
parm in our CRTSQLRPGI - CLOSSQLCSR(*ENDMOD).
I do not have much experience with that specific choice, but I recall
that the /Upon End Of Module/ close choice is more aggressive at
effecting full-close than the other options given the scope being so
much more limited; of course at the cost of the additional overhead, as
compared with effecting pseudo-close *if* the cursor could have been
reused instead of being re-created. Even so, while that scope is not
considered ended, there can still be pseudo-closed cursors held for what
is the scope of the /module/.
Many of our users stay logged in for days at a time, and if you wish
to add a field to a file, you need exclusive access to the file, or
so I thought until just now (reference next paragraph). There may be
no one in the program, but they used it once some time ago, so there
is this ODP lock and an exclusive lock cannot be obtained.
For SQL work for which an exclusive lock is required [e.g. as with an
ALTER, perhaps to ADD COLUMN], the SQL statement being performed will
request via the close-event, implicitly, that the holding job(s) should
release their lock; i.e. no need to issue the ALCOBJ CONFLICT(*RQSRLS),
as that is requested implicitly by the SQL.
For non-SQL work however, the onus is on the user [¿often or always?;
perhaps a DLTF or CHGPF SRCFILE(specified) might, whereas a generic
object action such as RNMOBJ or GRTOBJAUT might not? I do not recall] to
request the native allocation [i.e. ALCOBJ] with the request to have the
job holding the lock to drop their lock(s) being held on that member
solely as the effect of a pseudo-closed cursor.
I did not know about the extended parm on the ALCOBJ command for
CONFLICT (*RQSRLS). I did a quick test, and it does appear to release
the lock in another job where the lock is due to the SQL ODP, so that
gives me something interesting to play with. Perhaps the ODP can be
cleared without ending a job? That's a new concept to me.
That was what I suggested is already known by many, that the locks
held for a pseudo-closed cursor are generally inconsequential, because
the SQL just trucks-on, despite those apparently-conflicting locks;
because they are implicitly dropped by the holding-jobs, upon request by
the SQL being requested in another thread or process. And then for any
non-SQL work, they know that the same /close-event/ can be signaled to
the jobs holding those pseudo-closed cursors, so the informed-job
momentarily stops what is being done, to effect the full-close of that
ODP. So indeed, the locks on a member due to an ODP that remains solely
due to the effect of a pseudo-closed cursor, those locks can be cleared
without ending the job holding such locks; the so-called database
close-event [IMO best would have been called the SQL close-event;
anyhow, there is at least one exit program for the feature, but note,
that the database open exit has no relation].
midrange.com
