IBM-i Cleanup – Unused Programs?

“Show me Programs that haven’t been used for a year!”

… said the System Housekeeping Project Manager!

OK then… let’s provide an SQL statement to query the OBJECT_STATISTICS table for programs that haven’t been used in over a year.

-- Query programs not used in over 1 year from OBJECT_STATISTICS
SELECT 
    OBJNAME,
    OBJTYPE,
    OBJLIB,
    LAST_USED_TIMESTAMP,
    DAYS_USED_COUNT,
    DAYS(CURRENT_TIMESTAMP) - DAYS(LAST_USED_TIMESTAMP) AS DAYS_SINCE_LAST_USE
FROM 
    QSYS2.OBJECT_STATISTICS
WHERE 
    OBJTYPE IN ('*PGM', '*SRVPGM')
    AND LAST_USED_TIMESTAMP IS NOT NULL
    AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEAR
ORDER BY 
    LAST_USED_TIMESTAMP ASC;

Key Points:

• QSYS2.OBJECT_STATISTICS – IBM i Services view containing object usage statistics
• LAST_USED_TIMESTAMP – Timestamp of last program execution
• OBJTYPE IN ('*PGM', '*SRVPGM') – Filters for programs and service programs
• CURRENT_TIMESTAMP - 1 YEAR – Calculates date one year ago
• DAYS_SINCE_LAST_USE – Calculated column showing days since last use

Optional Enhancements:

-- Include library filter and additional details
SELECT 
    OBJNAME,
    OBJTYPE,
    OBJLIB,
    OBJOWNER,
    OBJCREATED,
    LAST_USED_TIMESTAMP,
    DAYS_USED_COUNT,
    DAYS(CURRENT_TIMESTAMP) - DAYS(LAST_USED_TIMESTAMP) AS DAYS_SINCE_LAST_USE,
    OBJSIZE,
    OBJTEXT
FROM 
    QSYS2.OBJECT_STATISTICS
WHERE 
    OBJTYPE IN ('*PGM', '*SRVPGM')
    AND OBJLIB NOT IN ('QSYS', 'QSYS2', 'QGPL')  -- Exclude system libraries
    AND LAST_USED_TIMESTAMP IS NOT NULL
    AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEAR
ORDER BY 
    DAYS_SINCE_LAST_USE DESC;

This query helps identify unused programs for potential archival or removal.