Re: Exclusive LOCK on a physical file

[Rob Berendt <robertowenberendt@xxxxxxxxx>]

Here's a related procedure I have in SPL to perform a similar task.  Just
modify the SET COMMAND from SBMJOB to ALCOBJ.  If you need to figure out
how to pass it parameters like library and source file, just ask.

CREATE PROCEDURE REMOVE_DELETED_ROWS
        LANGUAGE SQL
        MODIFIES SQL DATA
        SET OPTION DATFMT = *ISO
P1: BEGIN
    DECLARE WORK_SCHEMA_NAME *CHAR*(*10*);
    DECLARE WORK_TABLE_NAME *CHAR*(*10*);
    DECLARE WORK_MEMBER_NAME *CHAR*(*10*);
    DECLARE COMMAND *CHAR*(*200*);
*-- Ensure this is large enough.*    DECLARE END_TABLE *INT* DEFAULT *0*;
    DECLARE C1 CURSOR FOR WITH T1 AS (
                                   SELECT system_table_schema,
                                          system_table_name,
                                          system_table_member,
                                          number_deleted_rows,
                                          number_deleted_rows * avgrowsize
AS deleted_space,
                                          avgrowsize
                                       FROM qsys2.syspartitionstat
                                       WHERE number_deleted_rows > *0*
                               ),
                               t2 AS (
                                   SELECT t1.*,
                                          number_of_locks
                                       FROM T1,
                                            LATERAL (
                                                SELECT *COUNT*(*)
                                                    FROM
 qsys2.object_lock_info l
                                                    WHERE
 t1.system_table_schema = l.system_object_schema
                                                          AND
 t1.system_table_name = l.system_object_name
                                                          AND
 t1.system_table_member = l.system_table_member
                                                          AND
 l.object_type = '*FILE'
                                            ) L1 (number_of_locks)
                               )
        SELECT t2.system_table_schema,
               t2.system_table_name,
               t2.system_table_member
            FROM t2
            WHERE t2.number_of_locks = *0*
            ORDER BY t2.deleted_space DESC
            FETCH first *100* ROWS ONLY
*-- Change for your situation*    ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_TABLE = *1*;

*--  DECLARE EXIT HANDLER FOR SQLEXCEPTION*
*--    SET <insert column here> = NULL;*    OPEN C1;
    FETCH C1 INTO work_schema_name,
                  work_table_name,
                  work_member_name;
    WHILE END_TABLE = *0* DO
        SET COMMAND = 'SBMJOB CMD(RGZPFM FILE(' *CONCAT* *TRIM*
(work_schema_name) *CONCAT* '/' *CONCAT* *TRIM*(work_table_name) *CONCAT*
                    ') MBR(' *CONCAT* *TRIM*(work_member_name) *CONCAT*
')) JOB(' *CONCAT* *TRIM*(work_member_name) *CONCAT*
                    ') JOBQ(ROB/RGZPFM)';
*-- Adjust Job queue accordingly*        CALL QSYS2.QCMDEXC(COMMAND);
        FETCH C1 INTO work_schema_name,
                      work_table_name,
                      work_member_name;
    END WHILE;
    CLOSE C1;
END P1
;

*-- Test*CALL ROB.REMOVE_DELETED_ROWS;

On Tue, Feb 20, 2024 at 11:47 AM Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

> I do    ALCOBJ OBJ((QCLSRC *FILE *EXCL))
>
> This doesn't specify a member, but it appears to put an *EXCL lock on
> only the first member.
>
> WRKOBJLCK OBJ(QCLSRC) OBJTYPE(*FILE) shows just a *SHRRD lock, even
> though I asked for *EXCL:
> Job          User         Lock      Status          Scope
> QPAD155152   LENNONS      *SHRRD     HELD           *JOB
>
> WRKOBJLCK OBJ(QCLSRC) OBJTYPE(*FILE) MBR(*ALL) shows an *EXCL member
> lock on the first member:
> Member      Job         User        Type    Lock     Status
> CRPGDEBUG   QPAD155152  LENNONS     MBR     *SHRRD    HELD
>                                      DATA    *EXCL     HELD
>
> But I can still update other member, e.g., through PDM/SEU.
>
> Is there a way to lock all members in a file with ALCOBJ? (Without
> looping through all members and doing It individually that is.)
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.