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