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


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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].