Re: Locked and yet not locked . . . .

On 08-Oct-2016 10:14 -0500, CRPence wrote:

On 08-Oct-2016 00:21 -0500, Hauser wrote:

On 08.10.2016 03:39 (GMT+01:00) James H. H. Lampert wrote:

This is weird.

I've got a CL program that runs RUNSQLSTM on a member containing
code to generate a view with the same name as the member. And at
the end, to ensure that the view has the same authorities as its
based-on PF, I've got the statement:

GRTOBJAUT OBJ(&LIB/&MBRNAME) OBJTYPE(*FILE) +
REFOBJ(&LIB/&PFNAME)

[…] recently, it's been going into a lock wait, and then throwing
a chain of exceptions:

File FOO in library BAR in use.
Function not done for user profile BAZ.
One or more errors occurred during processing of command.
CPF2227 received by PLUGH at 7500. (C D I R)

(FOO being the SQL View we just created) And yet, WRKOBJLCK
doesn't find any locks on FOO.

Anybody have any idea what could cause that?

Check whether the RUNSQLSTM is performed under commitment control
or not.

Whether or not the Run SQL Statement feature, or specifically the
CREATE VIEW request itself, had performed with Commitment Control
active should be immaterial, for two reasons […]

First, [despite there not having been added a new parameter to
prevent the changed effect,] the RUNSQLSTM has long [for many
releases] been issuing an implicit COMMIT [or ROLLBACK] for RUNSQLSTM
requests made with anything other than COMMIT(*NONE); my recollection
is that long ago, if a COMMIT or ROLLBACK were not coded explicitly
within [i.e. at the end of] the script, or instead either was
requested after the completion of the RUNSQLSTM processing, then the
work performed under isolation remained pending. Additionally with
the implicit request made by the SQL, was the recording of the effect
of that implicit request, as logged in the output /listing/ for the
script activity [normally] as a msg SQL7960 "Commit completed." or
[abnormally] as a msg SQL7961 "Rollback completed."

Second, even if there was no implicit COMMIT [or ROLLBACK], then the
[thread scoped] locks for the isolation will be held by the same
process in which the CLP runs [i.e. the CLP that issued the
RUNSQLSTM]. So typically [as a process with a single thread] there
would be no *lock conflict* for a prior request made under isolation
within that same process. There would be *a conflict* however, if the
CREATE had run with CmtCtl but not been committed or rolled back, but
that conflict would be exposed by the msg CPF325E F/QDBFIXIT
"Uncommitted changes pending for file FOO in library BAR." rather
than what is presumably shown in the OP as a msg CPF3202 F/QDBGRTFI.
Similarly, if the request had been started with COMMIT(*NONE) but the
script had included a request to `SET TRANSACTION ISOLATION LEVEL
value-other-than-none` [for which the SQL would not do implicit
COMMIT, because the activation of the script was with COMMIT(*NONE)].

And if there were a lock left on the file due to a pending CREATE, then the error for a lock conflict [per a different process or thread holding that lock for that unit of work performed under isolation], that would have been manifest as *only* the msg CPF2211 F/QSYGRAUT "Not able to allocate object FOO in BAR type *FILE." In that case, there would be an *EXCL lock on the *FILE object, and thus the conflicting lock would have been caught prior to the Database [feature\component (DB) as the /owner/ of the database *FILE complex (vs simple) object type] ever having been invoked by the Security feature\component (SY) to perform the component-specific aut-granting activity. That is, there would not be all three of the message shown in the OP, only that one message CPF2211 being issued.

The failure shown in the OP with three messages is consistent with either:
• the *FILE having not been locked but with the data\member having been locked [i.e. broken lock protocol; either by a defect or a usage error; yet then I would have expected a msg MCH2601 "Lock enforcement rules broken when trying to access object &1" shown logged according to the OP]
• the *FILE having been locked per standard protocol with merely a *SHRRD lock for which the DB _would be invoked_ by the SY; e.g. the file was locked by a request that had opened the file. In this case, the SY defers to the DB to decide according to their locking protocol vs the standard\simple simple-object locking protocol.

Noting however: A pseudo-closed open in another job or thread would normally release the lock for the pseudo-closed open per a request by the database-grant to close the open-cursor [similar to how Allocate Object (ALCOBJ) using the CONFLICT(*RQSRLS) would Request Release as action of the other job(s)]. Thus such locks should not result in a conflict given none of
• an exit program had denied the close request
• the thread holding the lock for pseudo-closed open was not available to process the event requested to force the close
• a new open was immediately [re-]obtained for which a lock, both since the RqsRls and prior to the allocation\lock attempt by QDBGRTFI to initiate the component-specific granting activity, was [re-]obtained

Any of those above three cases would have experienced the same effect as described in the OP, as if the conflicting lock had never been dropped; i.e. as if the file had never been closed by that job. In the two former examples, that the file was never closed is the actual scenario. In the latter example, only "as if" the file had never closed.

But as was noted in response to the OP, what other thread [in another process] might have held a conflicting lock is anyone's guess; that some HA software [or similar designed sfw] is known to give rise to such conflicts by the nature of their performing work against just-created object [usu. per tracking T-CO . There was nothing given to indicate what the script or the CLP did, or if what either might have done might have caused another process|thread to obtain the lock.