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.