IBM Support

Detecting unused objects on the system

Question & Answer


Question

How to create a report of objects that have not been used recently. 

Answer

With the introduction of the IBM i SQL Service OBJECT_STATISTICS table function  and IFS_OBJECT_STATISTICS table function, you can create SQL reports containing the last used date and size for an object. Use this information to detect objects that have not been used recently or have never been used. 

Authorization:  Refer to the links below:

OBJECT_STATISTICS table function

IFS_OBJECT_STATISTICS table function

For Library type objects:

  1. Open the ACS Run SQL Scripts tool 
  2. Run the following SQL:
SELECT OBJNAME,
       OBJTYPE,
       OBJCREATED,
       LAST_USED_TIMESTAMP,
       DAYS_USED_COUNT,
       (OBJSIZE / 1e+6) AS object_size_megabytes
FROM TABLE (
        QSYS2.OBJECT_STATISTICS('QGPL', 'ALL')  -- QGPL is the library name
    ) A 
WHERE LAST_USED_TIMESTAMP < CURRENT_DATE - 6 MONTHS  -- specify date range
ORDER BY LAST_USED_TIMESTAMP ASC
NOTES: 
  • Adjust the Time and Date field LAST_USED_TIMESTAMP, in this case we are using 6 Months for the example.
  • Replace QGPL with the name of the library containing the objects. The name of the library must be entered in all uppercase characters. 
Sample Results:
image-20250924070121-1
For IFS type objects:
  1. Open the ACS Run SQL Scripts tool 
  2. Run the following SQL:
SELECT PATH_NAME,
       CREATE_TIMESTAMP,
       LAST_USED_TIMESTAMP,
       DAYS_USED_COUNT,
       (DATA_SIZE / 1e+6) AS data_size_megabytes
    FROM TABLE (
            QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/home', SUBTREE_DIRECTORIES => 'YES') --- Replaced /home with the path housing the objects. 
         ) A
    WHERE LAST_USED_TIMESTAMP  < CURRENT_DATE - 6 MONTHS  -- specify date range
    ORDER BY LAST_USED_TIMESTAMP ASC
NOTES: 
  • Adjust the Time and Date field LAST_USED_TIMESTAMP, in this case we are using 6 Months for the example.
  • Replace /home with the directory path containing the objects. The case used on the directory must match the case used to create the directory.
  • Use SUBTREE_DIRECTORIES => 'YES' if you also would like to include objects in the entire subdirectory structure. 
Sample Results:
image-20250924070723-2
Additional Information on when the Last Used date is updated, refer to the following link:

[{"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":"and future releases;7.3.0;7.4.0;7.5.0;7.6.0"}]

Document Information

Modified date:
24 September 2025

UID

ibm17026530

Page Feedback