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 :-)
Neill Harper wrote:
That has helped bring clarity to the situation.
It does raise one question though.... When should you use ALCOBJ CONFLICT(*RQSRLS)?
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".
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].
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.