RE: Locks are held... never released...

Thanks Chuck

That has helped bring clarity to the situation.

It does raise one question though.... When should you use ALCOBJ
CONFLICT(*RQSRLS)?

Cheers

Neill

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx]
On Behalf Of CRPence
Sent: 21 March 2010 17:59
To: java400-l@xxxxxxxxxxxx
Subject: Re: Locks are held... never released...

The same effect as ALCOBJ CONFLICT(*RQSRLS) is part of the SQL
ALTER TABLE request, at the very beginning, so issuing the ALCOBJ
explicitly would be redundant. Most every database action that
requires exclusive use of the TABLE will similarly effect that
request; albeit using a method within the OS database [SQL] programs
rather than via the CL command. There should be no need to issue
that CL request to effect removal of the locks held to maintain
"pseudo closed cursors".

FWiW it should be obvious in review, that for the SQL to require
an ALCOBJ [an action outside of the SQL] would be daft if truly
there was nobody actively using the TABLE, since those locks
remained [after cursor close] solely for the database to give better
performance; i.e. neither the user nor the programmer asked for
those locks to remain, so those locks should not [without good
reason] interfere with either the application or other database
requests outside that application, after the application completes
its activity against the TABLE. Use of that CL request is best
recommended solely to determine or confirm if, those locks which
were held had actually arisen from pseudo-closed cursors; those
locks which disappeared during that CL request having run to
completion, were most likely to have been held as a result of
pseudo-closed cursors. Locks that remain may have a different
origin and would require further investigation.

If the claim that "we can't" perform an ALTER TABLE "with these
locks" active is based on an actual error versus just perception
[i.e. assumed that a lock would preclude the request], then the
locks either are not the result of pseudo-closed cursors or are
possibly a side effect of the environment not being allowed or being
unable to effect the close by those other jobs which are holding the
pseudo-open\pseudo-closed SQL cursors. For example, if the DFTWAIT
setting for the job is too small to allow all of the jobs that hold
pseudo-closed cursors to effect their close prior to the wait time
being exceeded in the requesting job, the request to perform [a
conflicting request such as] an ALTER TABLE would be prevented by a
-913 [SQL0913]. As well there is an exit point [IIRC it is:
QIBM_QDB_CLOSE DBCL0100] which can be registered to explicitly
prevent a close being effected on a pseudo-closed cursor depending
on whatever criteria is coded for the exit program. Note that even
the run priority or anything else impacting the capability of the
lock-holding-job to perform its close in the allotted time limit is
a potential issue [e.g. job is held, the already mentioned exit
program, et al] for the requester of an operation which conflicts
with the held locks.

So anyhow... that CL request should not be [inferred to be]
recommended, neither as a corrective nor as a preventive action.
There should be few [if any] operations by the SQL database against
a TABLE that would be impacted by an actual pseudo-closed cursors;
except for example, the impacts noted in the previous paragraph.

Regards, Chuck

Neill Harper wrote:

I believe this is called the pseudo closing of locks. SQL "soft
closes" the file as it thinks you are going to read from it again
soon.

ALCOBJ CONFLICT(*RQSRLS) can remove the locks.

Not sure if there is any other way around it.

May be a google of pseudo close might reveal more.

Davor wrote:

we're having some problems with shared read locks on AS400
V5R4, accessed with jt400.

The problem is described here

http://stackoverflow.com/questions/2476470/db2-jdbc-driver-does-not-release-
table-locks

To cut the story short,

- we (Spring) issue an update statement, and
table obtains [sic: is allocated with] a shared read lock
(normal)
- after we close the statement, the lock is still there
(i guess normal)
- after we commit, the locks disappears
(normal)
- then, when we issue a SELECT statement, the lock
re-appears... and
- after closing the ResultSet, Statement, and Connection
(Spring does that), the lock remains
(NOT normal)

Do you have any idea, some kinda clue - where is the source of
these "never going away locks"? These are shared read locks,
and they don't affect our application and user experience in no
way, but from time to time, we have to issue some ALTER TABLE
statements, and with these locks, we can't.