Obtain information about SSL certificates on IBM i

CertificateExpiration.sql

-- Purpose: This query retrieves certificate information from the IBM i system certificate store, calculating the days remaining until certificate expiration and the impacted applications
-- Version: 2.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- PTF Requirements: IBM i 7.6 SF99960 level 2 or IBM i 7.5 SF99950 level 11
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-certificate-usage-info-view
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-certificate-info-table-function
-- Note: You can specify *NOPWD as *SYSTEM's keystore password

SELECT DESCRIPTION,
       APPLICATION_ID,
       APPLICATION_TYPE,
       CERTIFICATE_STORE,
       CERTIFICATE_LABELS,
       CERTIFICATE_LABEL,
       VALIDITY_START,
       VALIDITY_END,
       TO_CHAR(TIMESTAMPDIFF(16, CHAR(VALIDITY_END - CURRENT_TIMESTAMP))) AS DAYS_TO_EXPIRATION
    FROM QSYS2.CERTIFICATE_USAGE_INFO X
         INNER JOIN TABLE (
                 QSYS2.CERTIFICATE_INFO(CERTIFICATE_STORE_PASSWORD => 'XXXXXXX')
             ) Y
             ON X.CERTIFICATE_LABELS LIKE '%' CONCAT Y.CERTIFICATE_LABEL CONCAT '%'
    WHERE CERTIFICATE_STORE = '*SYSTEM'
          AND CERTIFICATE_LABEL_COUNT > 0