RE: Question on 'appropriateness' of using QSH commands to

-- category: SPL
-- description: Submit jobs to purge deleted rows
-- Cycle through a list of partitions (members) in a table with numerous deleted rows and submit
-- a job to RGZPFM those deleted rows out, if there are no locks on that member.
-- Cautions:
-- One: If you rely upon relative record number (RRN) then you should not use RGZPFM on that table.
-- Not normally a technique often used anymore.
-- Two: If someone tries to access the file while it is being reorganized it will be locked.
-- Three: There are ways to "reorg while active" but it has side effects you need to understand.
-- I did not try reorg while active for this procedure.

-- Getting the list of members from syspartitionstat is pretty efficient.
-- Adding the process to count locks on that member does add some run time
-- The following got done submitting the jobs to the job queue in 10.62 minutes on my system
-- Power 9 9009-42A lpar running IBM i 7.4 using SSD's hosted by another lpar of IBM i.
-- Development only lpar with few users on it.

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;

Rob Berendt