Finding out who holds a lock on an object

The direct way, from the command line:

WRKOBJLCK OBJ(PRODLIB/ITEMS) OBJTYPE(*FILE)

The same in SQL, useful when the result has to be collected in a program or several objects looked at together:

SELECT JOB_NAME, LOCK_STATE, LOCK_STATUS, LOCK_SCOPE
  FROM QSYS2.OBJECT_LOCK_INFO
 WHERE SYSTEM_OBJECT_SCHEMA = 'PRODLIB'
   AND SYSTEM_OBJECT_NAME   = 'ITEMS'

For database file members the lock can be on the individual member:

WRKOBJLCK OBJ(PRODLIB/ITEMS) OBJTYPE(*FILE) MBR(*ALL)

Example: the backup that fails at two in the morning

SAVLIB ends with an object not saved because somebody was holding it open. By the next morning the lock is gone (whoever held it has closed the session) and WRKOBJLCK is no help: it shows the situation now, not last night's.

The query, on the other hand, can sit in the backup CL under the MONMSG, and fires at the exact moment the lock is still there. First the table, created from the query itself so names and types come from the view:

CREATE TABLE MYLIB/DIAGLCK AS (
  SELECT CURRENT TIMESTAMP AS TAKEN_AT, SYSTEM_OBJECT_NAME,
         JOB_NAME, LOCK_STATE
    FROM QSYS2.OBJECT_LOCK_INFO
) WITH NO DATA

Then the backup:

SAVLIB LIB(PRODLIB) DEV(TAP01)
MONMSG MSGID(CPF3712) EXEC(DO)
  RUNSQL SQL('INSERT INTO MYLIB/DIAGLCK                             +
              SELECT CURRENT TIMESTAMP, SYSTEM_OBJECT_NAME,         +
                     JOB_NAME, LOCK_STATE                           +
                FROM QSYS2.OBJECT_LOCK_INFO                         +
               WHERE SYSTEM_OBJECT_SCHEMA = ''PRODLIB''') COMMIT(*NONE)
  MONMSG MSGID(CPF0000)
ENDDO

After three nights the table says whether it is always the same job (then it is a program to fix) or different users every time, in which case the problem is when the backup runs.

Releases. WRKOBJLCK exists on any release. The QSYS2.OBJECT_LOCK_INFO view is more recent and arrived with IBM i Services: if it is missing the query answers SQL0204, and the command remains, giving the same information on screen.

Il CL dell'esempio non è stato eseguito. E un punto va detto: la prova copriva la vista filtrata su un oggetto singolo, mentre l'esempio filtra sulla sola libreria. La sintassi è la stessa, ma su un sistema con molti lock quella forma può essere pesante: vale la pena provarla fuori dal salvataggio prima di metterla dentro.