04g - System Admin miscellanea

Check Larger Files (Tables) in a library list

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;
Related Posts
Alternative to OVRDBF in F specifications (dcl-f free)

It is not new to RPG but every now and then it is useful to remember that if we want Read more

ILE Debugger (STRDBG) and Screen Size

When we use the 5250 ILE Debugger interface (waiting for a Debugger for VsCode or a faster version of the Read more

Security pills: Check User with the default password

I welcome Steve Pitcher's suggestion from this Linkedin Post: a simple SQL statement to get the list of users with Read more

RPG IV free cheatsheet

RPG IV free cheatsheet

--- Roberto De Pedrini Faq400.com
About author

Founder of Faq400 Srl, IBM Champion, creator of Faq400.com and blog.faq400.com web sites. RPG developer since I was wearing shorts, strong IBM i supporter, I have always tried to share my knowledge with others through forums, events and courses. Now, with my company Faq400 Srl, I help companies to make the most of this great platform IBM i.

Leave a Reply

Your email address will not be published.