Logging locks on system objects

LogLock.sql

-- Purpose: List locks on my system
-- Version: 1.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-object-lock-info-view

SELECT SELECT SYSTEM_OBJECT_SCHEMA,
                   SYSTEM_OBJECT_NAME,
                   SYSTEM_TABLE_MEMBER,
                   OBJECT_TYPE,
                   ASPGRP,
                   MEMBER_LOCK_TYPE,
                   LOCK_STATE,
                   LOCK_STATUS,
                   LOCK_SCOPE,
                   JOB_NAME,
                   PROGRAM_LIBRARY_NAME,
                   PROGRAM_NAME,
                   MODULE_LIBRARY_NAME,
                   MODULE_NAME,
                   PROCEDURE_NAME,
                   STATEMENT_ID
                FROM QSYS2.OBJECT_LOCK_INFO

    stop;

-- Purpose: Log lock on specified objects
-- Version: 1.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-object-lock-info-view
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-object-statistics-table-function

CREATE PROCEDURE SQLTOOLS.LOGLOCK (
                IN LIB VARCHAR(10),                    --Library of object to be monitored
                IN OBJ VARCHAR(10) DEFAULT '*ALL',     --Object to be monitored
                IN OBJTYPE VARCHAR(10) DEFAULT '*ALL', --Object type to be monitored
                IN MINWAIT INTEGER DEFAULT '5',        --Collection duration
                IN OUTLIB VARCHAR(10),                 --Library in which to place the report
                IN OUTFILE VARCHAR(10)                 --Report
            )
        LANGUAGE SQL
        SPECIFIC SQLTOOLS.LOGLOCK
        NOT DETERMINISTIC
        MODIFIES SQL DATA
        CALLED ON NULL INPUT
        CONCURRENT ACCESS RESOLUTION USE CURRENTLY COMMITTED
        SET OPTION ALWBLK = *ALLREAD,
                   ALWCPYDTA = *OPTIMIZE,
                   COMMIT = *NONE,
                   DECRESULT = (31,
                   31,
                   00),
                   DYNDFTCOL = *NO,
                   DYNUSRPRF = *USER,
                   SRTSEQ = *HEX
BEGIN
    DECLARE CONTEGGIO INT;
    --Checking for temporary file
    SELECT COUNT(*)
        INTO CONTEGGIO
        FROM TABLE (
                QSYS2.OBJECT_STATISTICS('QTEMP', '*FILE', 'LOCKINFO')
            );
    IF CONTEGGIO > 0 THEN
        DROP TABLE QTEMP.LOCKINFO;
    END IF;
    --Creating temporary file
    CREATE OR REPLACE TABLE QTEMP.LOCKINFO AS
                (SELECT SYSTEM_OBJECT_SCHEMA,
                        SYSTEM_OBJECT_NAME,
                        SYSTEM_TABLE_MEMBER,
                        OBJECT_TYPE,
                        ASPGRP,
                        MEMBER_LOCK_TYPE,
                        LOCK_STATE,
                        LOCK_STATUS,
                        LOCK_SCOPE,
                        JOB_NAME,
                        PROGRAM_LIBRARY_NAME,
                        PROGRAM_NAME,
                        MODULE_LIBRARY_NAME,
                        MODULE_NAME,
                        PROCEDURE_NAME,
                        STATEMENT_ID,
                        CURRENT TIMESTAMP AS TIMESTAMP
                        FROM QSYS2.OBJECT_LOCK_INFO)
                WITH NO DATA;
    SET CONTEGGIO = 0;
    --Loop for collection duration
    WHILE CONTEGGIO < MINWAIT * 2 DO
        --Insert into temporary file locks info
        INSERT INTO QTEMP.LOCKINFO
            SELECT SYSTEM_OBJECT_SCHEMA,
                   SYSTEM_OBJECT_NAME,
                   SYSTEM_TABLE_MEMBER,
                   OBJECT_TYPE,
                   ASPGRP,
                   MEMBER_LOCK_TYPE,
                   LOCK_STATE,
                   LOCK_STATUS,
                   LOCK_SCOPE,
                   JOB_NAME,
                   PROGRAM_LIBRARY_NAME,
                   PROGRAM_NAME,
                   MODULE_LIBRARY_NAME,
                   MODULE_NAME,
                   PROCEDURE_NAME,
                   STATEMENT_ID,
                   CURRENT TIMESTAMP
                FROM QSYS2.OBJECT_LOCK_INFO
                WHERE SYSTEM_OBJECT_SCHEMA = UPPER(TRIM(LIB))
                      AND (
                      CASE
                          WHEN UPPER(TRIM(OBJTYPE)) <> '*ALL' THEN OBJECT_TYPE = UPPER(TRIM(OBJTYPE))
                          ELSE OBJECT_TYPE LIKE '%'
                      END)
                      AND (
                      CASE
                          WHEN TRIM(OBJ) = '*ALL' THEN SYSTEM_OBJECT_NAME LIKE '%'
                          ELSE
                              CASE
                                  WHEN RIGHT(OBJ, 1) = '*' THEN SYSTEM_OBJECT_NAME LIKE REPLACE(TRIM(OBJ), '*', '') CONCAT '%'
                                  ELSE SYSTEM_OBJECT_NAME = TRIM(OBJ)
                              END
                      END);
        CALL QSYS2.QCMDEXC('DLYJOB 30');
        SET CONTEGGIO = CONTEGGIO + 1;
    END WHILE;
    --Copy temporary file to specified file
    CALL QSYS2.QCMDEXC(
        'CPYF FROMFILE(QTEMP/LOCKINFO) TOFILE(' CONCAT TRIM(OUTLIB) CONCAT '/' CONCAT TRIM(OUTFILE) CONCAT
            ') FROMMBR(*FIRST) MBROPT(*REPLACE) CRTFILE(*YES)'
    );
END;