Re: Locked and yet not locked . . . .

On 07-Oct-2016 20:39 -0500, 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.

So the full RUNSQLSTM request [showing any defaults not as-shipped; e.g. if CHGCMDDFT set 'COMMIT(*NO)', then if the parameter keyword was omitted on the request, then the full request must explicitly show COMMIT(*NO) was the effect] was?

Perhaps?: RUNSQLSTM SRCMBR(VIEWNAME) SRCFILE(MYLIB/MYSRCF)

And the actual source in member VIEWNAME was?

Perhaps just the following; or perhaps much more than that transpired, and if so, what was included may be relevant?:

CREATE VIEW VIEWNAME AS ( SELECT * FROM BASED_ON )
;

And at the end <ed: of the CLP>, 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)

where &LIB is the library, &MBRNAME is the name of both the member
and the SQL View that member generates, and &PFNAME is the name of
thebased-on PF.

The program has been in service for years, and this particular
statement for a year and a half, without any problems.

So what version/release/modification, TR, cumulative, and groups; at least the VRM level [or in the new IBM i naming, the V.R level]

And yet recently, it's been going into a lock wait,

If possible, during this LCKW, from a job witnessing that LCKW [e.g. from WRKACTJOB] request a WRKJOB OPTION(*JOBLCK), and then from the file showing an *EXCL lock in WAIT status, issue the 8=Work With Object Locks [copy the screen], and then from there F6=Work With Member Locks [copy the screen]. Consider changing the CLP, any statement before the GRTOBJAUT request, to add a request to CHGJOB DFTWAIT(180) so if the conflicting lock is long-held, then that could give more time to catch the lock-wait condition.

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)

The spooled joblog and the spooled SQL script output please; the joblog generated with LOG(4 0 *SECLVL) for the job plus [and the CLP allowing] LOGCLPGM(*YES)

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

Locks are transient\temporal. But if presumed still possibly to be present after the failure, then be sure to specify MBR(*ALL) on an invocation of Work With Object Locks when reviewing locks for a database *FILE object; or after invocation with MBR(*NONE), be sure to review additionally, what shows with the F6=Work With Member Locks.

Anybody have any idea what could cause that?

A conflicting lock, of course ;-) Sorry, but no idea what was the message identifier nor any other contextual details of the "in use" message; partly, that is likely easily inferred though, to be the msg CPF3202 F/QDBGRTFI -- but if not, then important to know. Also no idea what the script did, if anything, beyond the CREATE VIEW; nor what the CLP did other than the Run SQL Statement and the Grant Object Authority request; might the CLP have run any queries, or directly or indirectly submitted any jobs or started any threads?

Does the system have an HA feature? Often such features will do /work/ on an object that was just created; the feature wants to ensure the object gets registered/copied wherever. The creators of objects in such environments have to understand that the conflicts are possible, if even likely, and react accordingly. For example, by increasing Maximum File Wait Time (WAITFILE) and Default Wait Time (DFTWAIT) values to minimize the conflicts that might otherwise end up causing timeout errors.

FWiW: Part of the performance tooling, there was [from what I recall] a lock report that can be produced. I have little recollection of the details, I recall additionally that [from the same low-level features there is also an option of the Trace facility to get a "Component" trace that might assist to find what job obtains a conflicting lock for the database *FILE or the Member (*MEM) object; i.e. IIRC, the pertinent parameters of the Start Trace (STRTRC) are TRCTYPE(*OBJLCK), and that AIUI could be directed against all jobs on the system with JOB(*ALL) and JOBTYPE(*ALL), and with sufficient storage. If activated, then if the problem does not happen, end the trace without data collection; try again until the problem occurs, and then end the trace with output that can be pored over for an origin.