RGZPFM: Determining the Percentage of Deleted Records for All Files on Your System

You can query QSYS2/SYSPARTITIONSTAT for the columns, etc., that you are interested in, but here is a sample query to get you started.

Note that this query will take a while to run.

It will show the tables that are 'most need' of a RGZPFM, based on the number of deleted rows.

Note that the other columns are applicable as well - the table name and/or total number of rows may show that while the number of deleted rows is a large number, the table is not used much, etc.


select trim(SYSTEM_TABLE_SCHEMA)
        concat '/' concat
       trim(SYSTEM_TABLE_NAME)
        concat '.' concat
       trim(SYSTEM_TABLE_MEMBER) as Lib_File_Member,
       NUMBER_DELETED_ROWS,
       case NUMBER_ROWS
            when 0 then 100.00
            else
            NUMBER_DELETED_ROWS * 100 / (NUMBER_ROWS + NUMBER_DELETED_ROWS)
       end as Percent_Deleted,
       NUMBER_ROWS
  from QSYS2.SYSPARTITIONSTAT
 where NUMBER_DELETED_ROWS > 0
 order by NUMBER_DELETED_ROWS desc



To set up to be advised when a file has reached the percentage set, run the following command:

CHGPF FILE(-your IBM i library-/your IBM i file) DLTPCT(50)

The DLTPCT specifies the maximum percentage of deleted records that any member in the physical file can have.

The percentage is based on the number of deleted records compared with the total record count in a member.
This change takes effect the next time the file is opened and closed.
In this case, 50% was used.
The DLTPCT, deleted-records-threshold-percentage, can have a value ranging from 1 through 100 that specifies the largest percentage of deleted records that any member in the file can have.
If a value is larger than this percentage, a message is sent to the system history log (QHST) when the file is closed.

Note: I am using library and scheme interchangeably as well as table and (physical) file