While there are quite a few methods for finding candidates for Reorganise Physical Files (RGZPFM), lots of them are old and some of them take a long time and resources to get results.

So here is a new one using ACS Run SQL Scripts, that may be faster and is more controllable.

Lets start with the SQL

With Tables_Pfs As (
       Select Objlib As Lib,
              Objname As Table_Pf,
              Date(Last_Used_Timestamp) As Last_Used,
              Date(Change_Timestamp) As Last_Changed
         From Table (
                Qsys2.Object_Statistics('*ALLUSR', '*FILE')
              ) X
              Join Qsys.Qadbxref
                On Objlib = Dbxlib
                  And Objname = Dbxfil
                  And Dbxtyp = 'D'
                  And Dbxatr In ('PF', 'TB')
--) select * from TABLES_PFS;
     ),
     Deleted As (
       Select Table_Schema,
Table_Name,
              Last_Used,
              Last_Changed,
              Number_Rows,
              Number_Deleted_Rows,
Cast(Cast(Data_Size As Decimal(15, 5)) / 1024.0 / 1024.0 As Decimal(15, 1))
                As Data_Mb,
Cast(
Cast(Number_Deleted_Rows As Decimal(15, 5)) / (Number_Rows + Number_Deleted_Rows) *
100.0 As Decimal(15, 1)) As Percentage,
Cast(
Cast(Cast(Data_Size As Decimal(15, 5)) / 1024.0 / 1024.0 As Decimal(15, 1)) /
Cast(
Cast(Number_Deleted_Rows As Decimal(15, 5)) /
                      (Number_Rows + Number_Deleted_Rows) * 100.0 As Decimal(15, 1)) As Decimal(15,
2)) As Saving_Mb
         From Tables_Pfs
              Join Qsys2.Systablestat
                On Table_Schema = Lib
                  And Table_Name = Table_Pf
         Where Number_Deleted_Rows <> 0
--) select * from DELETED;
     )
  Select *
    From Deleted
    Where Saving_Mb > 0
          And Percentage > 30
    Order By Saving_Mb Desc;

The above SQL is a CTE (Common Table Expression).

This allows SQL to run as one statement, enabling statements that would otherwise not work as a single select, or to simplify a complex statement. It strings a number of select statements together. Each select usually references one or more previous selects e.g. DELETED uses from TABLES_PFS and the final part (with no name) references DELETED.

Another advantage of CTEs is that you can test each stage as you develop it. The lines that start with a — (double dash comment) are for testing. Remove the –, place the cursor on or above that line, then and use Ctrl+R or Run Selected under the Run menu in Run SQL Scripts. The execution will stop on that line because it ends with a semi colon. If you place the cursor below that line you will get an error “Token ) was not valid.”, because it has come across a semi colon above the cursor. Put back the ‘–’ to continue without the test. You can also change the test statements to any valid “select” that suits your requirements.

The first part of the CTE, called TABLE_PFS, joins OBJECT_STATISTICS to find *FILEs with QADBXREF to reduce the list to Physical Files and Tables. The *ALLUSR can be changed to a single library, *USRLIBL or *CURLIB of the current job, or *ALLUSRAVL if you have separate ASPs. Other available options are probably not useful as they include system libraries, although some *ALLUSR libraries look like system libraries.

The second part of the CTE, finds useful information from SYSTABLESTAT, including the number of records and deleted records.

If you want your MBs to be Decimal rather than Binary, change the 1024s to 1000s (on 2 lines).

Add another divide to get GBs instead. You may also want to change the deleted rows <> 0 to > a number.

Again, in the final part, which does not have a name, you can change the SAVING_MB and PERCENTAGE conditions.

From the 14^th June 2024, if you install 7.4 TR10 or 7.5 TR4, the SQL can be replaced by :-

With Deleted As (
    Select Table_Schema As Library,
           Table_Name As Table_Pf,
           System_Table_Member As Member,
           Date(Last_Used_Timestamp) As Last_Used,
           Date(Last_Change_Timestamp) As Last_Changed,
           Number_Rows,
           Number_Deleted_Rows,
Cast(
Cast(Number_Deleted_Rows As Decimal(15, 5)) / (Number_Rows + Number_Deleted_Rows) *
100.0 As Decimal(15, 1)) As Percentage,
Cast(
Cast(Cast(Data_Size As Decimal(15, 5)) / 1024.0 / 1024.0 As Decimal(15, 1)) /
Cast(
Cast(Number_Deleted_Rows As Decimal(15, 5)) / (Number_Rows + Number_Deleted_Rows) *
100.0 As Decimal(15, 1)) As Decimal(15, 2)) As Saving_Mb
      From Qsys2.Sysmemberstat
      Where Number_Deleted_Rows <> 0
  )
  Select *
    From Deleted
    Where Saving_Mb > 0
          And Percentage > 30
    Order By Saving_Mb Desc;

I’ve not tested this, but I am confident that it is correct.