Re: Finding a record lock -- MIDRANGE-L

On 03-Oct-2016 10:36 -0500, Brian Garland wrote:

Is there any API or system table that would tell us the program that is
holding a record lock?

No. Programs do not hold record locks; ¿only? processes [jobs], threads, and lock-spaces hold locks.

The Display Record Locks (DSPRCDLCK) against the physical file will show the job number. From that output, a request can be made to Work With Job (WRKJOB) to ask for the OPTION to show the Program Stack (*PGMSTK); i.e. WRKJOB OPTION(*PGMSTK). However, to best know if the record lock is still held while the programs shown in the stack are still active, then the Job Locks (*JOBLCK) detail is also of interest per Job Record Locks being included; i.e. to get both in one show, use the WRKJOB OPTION(*ALL) OUTPUT(*PRINT).

I know the job number is in the record lock message, but this is
not enough info because sometimes it is a batch job that receives
requests via a data queue and it potentially runs many programs and
could have moved on from the request that left the record lock
originally.

That locks are transient can be an issue that way. But program stacks are similarly transient; i.e. while often enough a Row Level Access (RLA) [and esp. a batch] program might be\remain on the stack whilst holding a record lock, there is really no way to be sure which of\if any of the current programs in the [top of the] stack actually were responsible for obtaining that lock.

But ever since the lock-held condition has passed, knowing what locked the row usually is not much of an issue, so storage that would be required to retain that historical detail is not generally useful and would be expensive; such logging is generally left to the domain of the applications. However, if the lock was held per an UPDATE to the row, then the Journal of the file could be helpful; i.e. the message giving the row number and job also gives a timestamp, such that a Display Journal (DSPJRN) request to review for some activity to that row by that job, spanning a time-frame before and after the conflict [selection only by job and not limited by timestamp is best], could be helpful to identify the program.

Note: How I usually attempt to approach such issues, is to *reverse* the situation. That is, after I increase the Maximum Record Wait Time (WAITRCD) for the file to a ridiculously large value [e.g. *NOMAX], I then obtain and hold a[n update] lock on the record that I expect the other job to try to obtain. In that manner, I have plenty of time to poke about at any\all of the /waiters/ [presented in DSPRCDLCK and showing status Lock Wait (LCKW) on WRKACTJOB], for the lock that I hold; I just release the lock when I am done. Using this method, I can actually *identify the program* that attempts to get the lock; that job shows WAITING on the record lock [in DSPRCDLCK], and the program stack is static, whilst that wait pends [effectively indefinitely, per *NOMAX].

FWiW, here are a command-source and program-source that, with the three job elements as copied\pasted from the DSPRCDLCK output accepted as parameters [in that order], will request the spooled display job:

CMD
PARM KWD(JOBNAM) TYPE(*SNAME) LEN(10)
PARM KWD(JOBUSR) TYPE(*SNAME) LEN(10)
PARM KWD(JOBNBR) TYPE(*DEC ) LEN(6 0)

pgm parm( &jd &ju &jn06p00 )
dcl &jd *char 10
dcl &ju *char 10
dcl &jn06p00 *dec (06 00)
dcl &jn *char 6
chgvar &jn &jn06p00
dspjob &jn/&ju/&jd option(*all) +
output(*print) /* *joblck and *pgmstk */

FWiW: Of possible additional interest for /locking/ /API/ topics:

[web search; link as top hit included next](https://www.google.com/search?q=services+developerworks+%22record+locks%22)

[DB2 for i -> Technology Updates ->‎ DB2 for i -> Services ->‎ QSYS2.RECORD_LOCK_INFO](https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.RECORD_LOCK_INFO)

[Retrieve Record Locks (QDBRRCDL) API](http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/apis/qdbrrcdl.htm)
"The Retrieve Record Locks (QDBRRCDL) API lets you generate a list of jobs, threads and lock spaces that are either waiting for or holding locks on one or more ..."

[Retrieve Job Record Locks (QDBRJBRL) API](http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/apis/qdbrjbrl.htm)
"The Retrieve Job Record Locks (QDBRJBRL) API lets you generate a list of record locks that a specific job or thread is holding or for which it is waiting. ..."

[Retrieve Lock Information (QWCRLCKI) API](http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/apis/qwcrlcki.htm)
"Record lock indicator. The indicator that controls the retrieval of record locks that may exist if the object specified is a file and member. If the object specified is ..."