Welcome back to another dieting adventure in IBM-i land. Ever wondered why your IBM i disk space is disappearing faster than expected? Those fat libraries are usually the culprits. Follow my Fat Stuff Blogs on how to use SQL to find the large objects on our IBM-i systems.

Here is a quick and practical lesson on using SQL to find large objects and spot the biggest space hogs on your system.

Fire up Run SQL Scripts in ACS or the terminal in Code for IBM i and try these two statements. They are simple, fast to run, and super effective.

1. Total size of the USER data system (excluding Operating System)

This query gives you the grand total of all storage used by every object on your IBM i, shown in bytes:

with LibrarySizes as (
  select OBJLIB, count(*) as OBJ_CNT, sum(OBJSIZE) as LIB_SIZE
    from table (QSYS2.OBJECT_STATISTICS('*ALLUSR', '*ALL')) as X
    group by OBJLIB
)
select 
    count(*) as TOTAL_LIBRARIES,
    sum(OBJ_CNT) as TOTAL_OBJECTS,
    sum(LIB_SIZE) as TOTAL_SIZE
  from LibrarySizes

Run it once and you will know exactly how much space your whole system is consuming.

2. List all these libraries in descending order

This one groups everything by library, counts the objects, and sorts by size so the fattest libraries appear first:

select OBJLIB as LIBRARY_NAME,
       count(*) as OBJECT_COUNT,
       sum(OBJSIZE) as LIBRARY_SIZE
  from table (
      QSYS2.OBJECT_STATISTICS('*ALLUSR', '*ALL')
    ) as X
  group by OBJLIB
  order by LIBRARY_SIZE desc

You will instantly see which libraries are eating the most space. On a large system these queries scan a lot of objects, so they can take some time. Obviously run this when the machine is not at peak busy time.

If you just want the top 20 fattest libraries, add this line at the end:

FETCH FIRST 20 ROWS ONLY;

Run these regularly as part of your housekeeping routine and you will keep your IBM i running lean and fast.

That is all there is to it!