midrange.com -- source code scratchpad

midrange.com code scratchpad
Name:
Sample SPL
Scriptlanguage:
Plain Text
Tabwidth:
Date:
04/20/2021 11:46:07 am
IP:
Logged
Description:
Sample SQL stored procedure.
You could store this in a source member and use RUNSQLSTM to "compile" it.
Code:

    -- 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;
generated in 0.007s
valid xhtml & css