Last Updated on 17 August 2022 by Roberto De Pedrini
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;
—
Roberto De Pedrini
Faq400.com
