Re: AW: Locked and yet not locked . . . .

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 RUNSQLSTMT is Performance 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; well, for most anyone but JHHL I suppose, often running on extremely old releases, therefore possibly only one reason.

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