Question & Answer
Question
How do you find the size of a Library and the number of objects in it.
Answer
With the introduction of the IBM i SQL Service LIBRARY_INFO table function, you can create an SQL report containing the size of a library and the number of objects in it.
The LIBRARY_INFO table function returns a result table that contains information about a specific library.
This information is similar to what is returned by the Retrieve Library Description (QLIRLIBD) API and the Retrieve Library Description (RTVLIBD) command
Authorization: The caller must have *USE authority to the library. The null value is for returned for the OBJECT_AUDIT_CREATE column unless the caller has all object (*ALLOBJ) or audit (*AUDIT) special authority.
To get a listing of the size and the number of objects in one library follow the steps:
1) Open the ACS Run SQL Scripts tool
2) Run the following SQL:
SELECT OBJECT_COUNT AS Number_of_Objects,
(LIBRARY_SIZE/1e+9) AS Library_Size,
LIBRARY_TYPE AS Library_Type,
TEXT_DESCRIPTION AS Text_Description
FROM TABLE (
QSYS2.LIBRARY_INFO('QGPL')
)
Note: The size of the library is displayed in Gigabytes and the name of the library is QGPL.
To get a listing of the size and the number of objects for all libraries on the System follow the steps:
1) Open the ACS Run SQL Scripts tool
2) Run the following SQL:
WITH libs (ln) AS (
SELECT objname
FROM TABLE (
qsys2.object_statistics('*ALLSIMPLE', 'LIB')
)
)
SELECT ln,
ROUND((LIBRARY_SIZE / 1e+9),
2) AS Library_Size,
object_count
FROM libs,
LATERAL (
SELECT *
FROM TABLE (
qsys2.library_info(library_name => ln)
)
)
ORDER BY library_size DESC;
NOTES:
- The size of the libraries are displayed in Gigabytes in descending order.
- The performance of this SQL has been greatly improved on R740, in release R730, the SQL works but takes some time to produce the listing.
- By default, Run SQL scrips, returns 100 rows at a time, to change the number of rows to be retrieved, change the value under Preferences/Results
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHAAA2","label":"Operating System"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.3.0;7.4.0;7.5.0;7.6.0"}]
Was this topic helpful?
Document Information
Modified date:
22 July 2025
UID
ibm16366683