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 Debugging functions with Visual Studio Code have been available for some time but this new version 2.10.0 simplifies the handling…
Two and a half years after my post Transferring objects with ObjectConnect and Enterprise Extender, I finally got around to…
With a little trick even a simple SELECT statement can execute any system command! Let's see how to do that...
A mini-serial guide to configuring, managing, using, and troubleshooting the IBM i NetServer
A mini-serial guide to configuring, managing, using, and troubleshooting the IBM i NetServer
A mini-serial guide to configuring, managing, using, and troubleshooting the IBM i NetServer