We are always trying to find the large libraries on the machine!

Hunting down the big libraries in an IBM i system (the artist formerly known as AS/400 or iSeries) is a quest with several critical motives:

1. Identifying system usage and resource consumption:
   • Large libraries can indicate areas of the system that are consuming significant storage space and system resources.
   • Analyzing the contents of these large libraries can help you understand which applications, data, or other components are taking up the most space.
2. Optimizing system performance:
   • Large libraries may contain unused or outdated data, programs, or objects that are no longer needed.
   • Identifying and cleaning up these unused or obsolete items can help free up system resources and improve overall performance.
3. Ensuring data integrity and security:
   • Large libraries may contain sensitive or critical data that needs to be properly managed and secured.
   • Reviewing the contents of these libraries can help you identify any potential data integrity issues or security risks that need to be addressed.
4. Capacity planning and growth management:
   • Monitoring the size and growth of libraries over time can assist in capacity planning and forecasting future storage and resource requirements.
   • This information can help you make informed decisions about hardware upgrades, storage expansion, or other system enhancements.
5. Compliance and regulatory requirements:
   • Certain industries or organizations may have specific compliance or regulatory requirements related to data management and retention.
   • Identifying and properly managing large libraries can help ensure that you are meeting these requirements and mitigating any potential risks.

Diving into the vast ocean of libraries in your IBM i system with a proactive snorkel can unearth treasures of insights on system usage.

SQL is that Snorkel!

IBM i SQL offers some performance-boosting magical lotion, a shield for data integrity and security, and a crystal ball for foreseeing growth and capacity.

How about an answer to this question – Show me a list of libraries, excluding the IBM Operating system ones, including library description, iASP and then show it as a spreadsheet in descending size sequence!

That sort of question would normally have me diving into SQL and doing a few DSPOBJD OUTFILE(something) and then a SQL select and sequence but with the marvelous QSYS2.LIBRARY_INFO and QSYS2.OBJECT_STATISTICS table functions we can answer this with one SQL statement.

Just copy/paste this into the IBM i ACS SQL Explorer:

select A.OBJNAME,
       A.OBJTEXT,
       A.IASP_NUMBER,
       (select LIBRARY_SIZE
           from table (
               QSYS2.LIBRARY_INFO(A.OBJNAME)
             ))
  from table (
      QSYS2.OBJECT_STATISTICS('*ALL', 'LIB')
    ) A
  where A.OBJNAME not like 'Q%'
  order by LIBRARY_SIZE desc;

IBM i SQL is pretty magical and can turn someone like me into a seeming wizard of IBM i management and maintenance in no time. (Well… that’s they think! #PulledTheWoolOverTheirEyesPhew)

And this returns this lovely list of libraries in FAT to THIN sequence:

Now you just click the little DOWNLOAD button and *boom* a lovely spreadsheet.

Well done IBM i SQL, Well done!