Obtain information about Jobs that were executed in a subsystem

JobSbsHistory.sql

-- Purpose: Obtain information about Jobs that were executed in a subsystem
-- Version: 1.0
-- Date 17/07/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-job-info-table-function
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-history-log-info-table-function

CREATE OR REPLACE FUNCTION SQLTOOLS.GET_SBS_JOB (
            INSBSLIB VARCHAR(10),
            INSBS CHAR(10),
            INSTARTTIME VARCHAR(50),
            INENDTIME VARCHAR(50) DEFAULT '9999-12-30-00.00.00'
        )
    RETURNS TABLE (
        JOB VARCHAR(28),
        STATUS VARCHAR(10),
        JOBQ VARCHAR(21),
        SBS VARCHAR(21),
        STARTTIME TIMESTAMP,
        ENDTIME TIMESTAMP
    )
    LANGUAGE SQL
    SPECIFIC SQLTOOLS.GETSBSJOB
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    SET OPTION ALWBLK = *ALLREAD,
               ALWCPYDTA = *OPTIMIZE,
               COMMIT = *NONE,
               DBGVIEW = *LIST,
               DECRESULT = (31,
               31,
               00),
               DYNDFTCOL = *NO,
               DYNUSRPRF = *USER,
               SQLCURRULE = *STD,
               SRTSEQ = *HEX
    BEGIN
        DROP TABLE IF EXISTS SESSION.DSPLOG;
        DECLARE GLOBAL TEMPORARY TABLE DSPLOG AS
                    (SELECT S.MESSAGE_TIMESTAMP,
                            S.FROM_JOB,
                            MESSAGE_ID,
                            MESSAGE_SECOND_LEVEL_TEXT,
                            TRIM(SUBSTR(S.MESSAGE_TOKENS, 69, 10)) CONCAT '/' CONCAT TRIM(SUBSTR(S.MESSAGE_TOKENS, 59, 10)) AS SBS
                            FROM TABLE (
                                    QSYS2.HISTORY_LOG_INFO()
                                ) S
                            WHERE 1 = 0)
                    WITH NO DATA;
        INSERT INTO SESSION.DSPLOG
            SELECT S.MESSAGE_TIMESTAMP,
                   S.FROM_JOB,
                   MESSAGE_ID,
                   MESSAGE_SECOND_LEVEL_TEXT,
                   TRIM(SUBSTR(S.MESSAGE_TOKENS, 69, 10)) CONCAT '/' CONCAT TRIM(SUBSTR(S.MESSAGE_TOKENS, 59, 10)) AS SBS
                FROM TABLE (
                        QSYS2.HISTORY_LOG_INFO(START_TIME => TIMESTAMP(TRIM(INSTARTTIME)), END_TIME => TIMESTAMP(TRIM(INENDTIME)))
                    ) S
                WHERE S.MESSAGE_ID IN ('CPF1124', 'CPF1164');
                
        RETURN (SELECT S.JOB,
                       CASE
                           WHEN E.ENDTIME IS NULL THEN '*ACTIVE'
                           ELSE
                               CASE
                                   WHEN JOB_QUEUE_LIBRARY CONCAT '/' CONCAT X.JOB_QUEUE_NAME IS NULL THEN '*END'
                                   ELSE '*OUTQ'
                               END
                       END AS STATUS,
                       JOB_QUEUE_LIBRARY CONCAT '/' CONCAT X.JOB_QUEUE_NAME AS JOBQ,
                       S.SBS,
                       S.STARTTIME,
                       E.ENDTIME
                    FROM (
                             SELECT MESSAGE_TIMESTAMP AS STARTTIME,
                                    FROM_JOB AS JOB,
                                    SBS
                                 FROM SESSION.DSPLOG
                                 WHERE MESSAGE_ID = 'CPF1124'
                                       AND MESSAGE_SECOND_LEVEL_TEXT IS NULL
                         ) S
                         LEFT JOIN (
                                 SELECT MESSAGE_TIMESTAMP AS ENDTIME,
                                        FROM_JOB AS JOB
                                     FROM SESSION.DSPLOG
                                     WHERE MESSAGE_ID = 'CPF1164'
                             ) E
                             ON E.JOB = S.JOB
                         LEFT JOIN TABLE (
                                 QSYS2.JOB_INFO(JOB_SUBSYSTEM_FILTER => '*ALL', JOB_USER_FILTER => '*ALL')
                             ) X
                             ON X.JOB_NAME = S.JOB
                    WHERE SBS = TRIM(INSBSLIB) CONCAT '/' CONCAT TRIM(INSBS));
    END;
    
    stop;
    
    -- Example:
    SELECT *
    FROM TABLE (
            SQLTOOLS.GET_SBS_JOB(INSBSLIB => 'QSYS', INSBS => 'QBATCH', INSTARTTIME => '2026-07-14 10:00:10')
        );
        
    stop;