Re: Exclusive LOCK on a physical file

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.