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 Early April saw the release of the "Spring Version" of ACS Access Client Solution, version 1.1.9.5 Interesting new features especially…
If the packed agenda of sessions at Common Europe Congress 2024, June 3-6 Milan, wasn't enough for you, here's another…
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