IBM i - find files that have a large number or percentage of deleted records

Deleted Records.sql

--
-- Description: When a record is deleted from a file/table, it continues to take up space on disk.
--              This is because the space has been allocated by the file, and that space only gets
--              released when the file is reorganized (RGZPFM).  If the file is configured to 
--              "reuse deleted records" this is not a concern.  The next record that is written to
--              the file will reuse the space that was being used by the deleted record.  However,
--              files created from DDS will not reuse deleted records by default.  This can be
--              changed (with caution) using the CHGPF command.  A file can consist of 100% deleted
--              records, meaning that it contains no actual but consumes a large amount of storage.
--              The goal of this gist is to find the files in your system with a lot of deleted 
--              records, or a high percentage of deleted records.  With this information you could
--              decide to reorg those files, or to change the files to reuse deleted records.
--

stop;
--NOTE - This can take a very long time to run against large libraries (1-2 hours+).
stop;

--Description: list the top 50 files with the most deleted records
SELECT TABLE_SCHEMA,
       TABLE_NAME, 
       NUMBER_ROWS, 
       NUMBER_DELETED_ROWS, 
       DATA_SIZE
    FROM QSYS2.SYSTABLESTAT
    WHERE TABLE_SCHEMA IN ('PRODLIB1', 'PRODLIB2')
    ORDER BY NUMBER_DELETED_ROWS DESC
    FETCH FIRST 50 ROWS ONLY;


--Description: list the top 50 files with the highest PERCENTAGE of deleted records
SELECT TABLE_SCHEMA, 
       TABLE_NAME, 
       NUMBER_ROWS, 
       NUMBER_DELETED_ROWS, 
       DATA_SIZE, 
       1.0 * NUMBER_DELETED_ROWS / (NUMBER_ROWS + NUMBER_DELETED_ROWS) AS PERCENT_DELETED --multiply by 1.0 to force the precision
    FROM QSYS2.SYSTABLESTAT
    WHERE TABLE_SCHEMA IN ('PRODLIB1', 'PRODLIB2')
        AND (NUMBER_ROWS + NUMBER_DELETED_ROWS) <> 0 --avoid division by zero
    ORDER BY 6 DESC
    FETCH FIRST 50 ROWS ONLY;