Check Larger Files (Tables) in a library list

Suppose we want to keep files under control (we should get used to calling them Tables, like the rest of the world!) of a list of data libraries, to want to see the size, the number of records and the number of records deleted (calculating a possible size of the object after a possible reorganize (RGZPFM)).

With a simple query like this we can have the list of the top 100 tables that “eat” more space than three libraries indicated in a temporary table to be put in Cross Join Lateral with the UDTF object_statistics and the systablestat:

SELECT * FROM (VALUES ('FAQ400'), ('QGPL'), ('SAMPLEDB')) t (LibraryToCheck) cross join lateral (select objlongschema, objname, objlongname, objtype, objattribute, objowner, objcreated, cast (objsize / 1000000 as integer) as ObjSizeMB, int (objsize / (NUMBER_ROWS + NUMBER_DELETED_ROWS) * NUMBER_ROWS / 1000000) as AfterReorgize, b.last_used_timestamp, number_rows, number_deleted_rows, objtext FROM TABLE (QSYS2.STATTSLibraryToCheck, '* ALL')) a left join qsys2.systablestat b on a.objname = b.system_table_name and a.objlongschema = b.table_schema) order by objsizeMB desc fetch first 100 rows only;