Find Large Objects on my IBM i System – Use SQL to find those FAT FILES

Object size management is crucial on IBM i systems for several reasons:

1. Performance Optimization: Large objects can slow down system performance, especially during backup and recovery operations. Efficiently managing object sizes helps maintain optimal system performance
2. Storage Management: AS/400 systems have finite storage resources. Monitoring and managing the size of objects ensures that storage is used efficiently, preventing unnecessary consumption of disk space
3. System Stability: Overly large objects can lead to system instability or crashes. By keeping object sizes in check, you can enhance the overall stability and reliability of the system
4. Backup and Recovery: Smaller, well-managed objects make backup and recovery processes faster and more efficient. This is particularly important for minimizing downtime during maintenance
5. Cost Efficiency: Efficient storage management can reduce the need for additional hardware investments, leading to cost savings for the organization

By regularly monitoring and managing object sizes, you can ensure that your IBM-i System runs smoothly and efficiently, providing reliable service to your organization.

Want to find LARGE Objects on your IBM i System?

To find large objects on an IBM i system using SQL, you can use the rather spiffy QSYS2.OBJECT_STATISTICS table function.

Here’s an example query that lists objects along with their sizes scoped across ALL USERS LIBRARIES on the system. I’ve also selected to just see the top 10 fatties, but you could easily ask for top 20, 30 or whatever:

select OBJNAME as OBJECT,
       OBJLONGSCHEMA as LIBRARY,
       OBJATTRIBUTE as ATTRIBUTE,
       OBJSIZE as SIZE,
       LAST_USED_TIMESTAMP as LAST_DATE
  from table (
      QSYS2.OBJECT_STATISTICS('*ALLUSR', 'ALL')
    )
  order by OBJSIZE desc
  fetch first 10 rows only;

This query retrieves the largest objects in all user libraries, sorted by size in descending order

NOTE: It does take a few minutes to run. On my little P10, at a quiet time of day, it took around 3 minutes. So don’t run this at peak system load:

All objects have their own size limits.

For example — if you need to find large objects specifically in data queues, you can use a query like this:

SELECT 
    data_queue_name, 
    data_queue_library, 
    data_queue_type, 
    current_messages 
FROM 
    QSYS2.DATA_QUEUE_INFO 
ORDER BY 
    current_messages DESC 
FETCH FIRST 10 ROWS ONLY;

This will list the data queues with the most messages