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

You are right Chuck, the Doc that Davor sent states.

"Some operating system commands and all SQL statements that require an
exclusive lock (DLTF, DROP TABLE, CLRPFM, and so on) will cause a pseudo
closed cursor to be hard closed so that the lock is released"

It kind of feels like to me, someone couldn't be bothered changing all of
the OS commands when pseudo closing was introduced :-)



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

Neill Harper wrote:

That has helped bring clarity to the situation.

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

CRPence wrote:

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

<<SNIP>>

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.

I should have written "Most every database *SQL* action that
requires exclusive use of the TABLE will similarly effect" the
equivalent of CONFLICT(*RQSRLS), similar to my mention of "operation
by the SQL database" in the summary paragraph.

The CL command ALCOBJ would be used mostly for non-SQL operations
that will still conflict in some manner with the pseudo-closed held
locks, but which are operations deemed unworthy for the database to
request the release of those locks to perform that operation, since
the requests are outside of the SQL and thus best not unnecessarily
impact the SQL. Examples might be: RNMOBJ versus SQL RENAME,
ENDJRNPF vs no SQL equivalent, RSTOBJ vs no SQL equivalent, CLRPFM
vs SQL DELETE with no or high cardinality WHERE clause, GRTOBJAUT vs
SQL GRANT etc.. I am unsure if some less critical SQL DDL requests
such as COMMENT ON or LABEL ON might be impacted\prevented by
pseudo-closed held locks, but I suspect that even these do not
require the ALCOBJ. Being an SQL request [versus a non-SQL database
request which is typically CL-invoked], the SQL will attempt to
perform as if there were no such thing as the pseudo-closed held
locks, in order to work just like any other database; i.e. if after
multiple repeated SELECT & close cursor requested in a
thread\process on another database would still allow LABEL ON in any
other thread\process, then so too should the DB2 for i SQL [barring
limitations imposed upon effecting the closes in the other threads
or processes].

Regards, Chuck