Re: Locks are held... never released...

It was not so much that we "couldn't be bothered changing all of the OS commands". Instead, much more directly the decision was made not to change them, to ensure that such requests performed "outside of the SQL" would "best not *unnecessarily* impact the SQL".

Some requests more intuitively should change, or otherwise could have some benefit in changing. For example DLTF intuitively should effect the request to release the conflicting locks since obviously by completion of the operation, the TABLE would no longer be useful to the SQL. Another example is CLRPFM as the implementation for a fast-path SQL DELETE without WHERE clause, which would benefit from having only one implementation for its locking versus two.

Allowing the non-SQL to fail due to /seeing/ the files as "in use" was deemed better than signaling the request to release conflicting pseudo-closed locks, both since the SQL may be using the files again soon [at least as perceived by the database SQL for its performance optimization technique of pseudo-closed cursors], and because the cost of requesting & effecting the release is especially significant if the SQL were to soon repeat the open [just as the database perceived & planned for, by effecting pseudo-close], since for lack of an already open cursor, that would effect a full open to establish an entirely new SQL cursor.

Regards, Chuck

Neill Harper wrote:

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 :-)

CRPence wrote:

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