IBM Support

How do you find the size of a Library and the number of objects in it.

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')
        )

image 7032


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;

image 9105

Note:  The size of the libraries are displayed in Gigabytes in descending order.
Note 2: 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. 


[{"Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"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"}]

Document Information

More support for:
IBM i

Component:
Operating System

Software version:
7.3.0, 7.4.0

Operating system(s):
IBM i

Document number:
6366683

Modified date:
31 March 2021

UID

ibm16366683

Manage My Notification Subscriptions

Page Feedback