Obtain information about JobLogs

JobLogSpools.sql

-- Purpose: This query retrieves the attributes of each job with spool QPJOBLOG
--          PLEASE NOTE: consider using filters on QSYS2.JOB_INFO 
-- Version: 1.0
-- Date 25/01/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.5.0?topic=services-output-queue-entries-basic-view
--       https://www.ibm.com/docs/en/i/7.5.0?topic=services-job-description-info-view
-- Note: if you want to check obly job with different logging level than specified in the jobd you can add this WHERE clause:
--       X.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT X.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT X.MESSAGE_LOGGING_TEXT <>
--         Z.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_TEXT

SELECT X.JOB_NAME,
       X.JOB_DESCRIPTION_LIBRARY CONCAT '/' CONCAT X.JOB_DESCRIPTION AS JOBD,
       X.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT X.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT X.MESSAGE_LOGGING_TEXT
           AS CURRENT_VALUE,
       Z.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_TEXT AS JOBD_VALUE,
       X.JOB_ACTIVE_TIME,
       X.JOB_END_TIME,
       X.JOB_TYPE,
       X.COMPLETION_STATUS,
       Y.OUTPUT_QUEUE_NAME,
       Y.SPOOLED_FILE_NAME
    FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC Y
         INNER JOIN TABLE (
                 QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*OUTQ', JOB_USER_FILTER => '*ALL')
             ) X
             ON X.JOB_NAME = Y.JOB_NAME
         INNER JOIN QSYS2.JOB_DESCRIPTION_INFO Z
             ON X.JOB_DESCRIPTION_LIBRARY CONCAT '/' CONCAT X.JOB_DESCRIPTION = Z.JOB_DESCRIPTION_LIBRARY CONCAT '/' CONCAT
                     Z.JOB_DESCRIPTION
    WHERE Y.OUTPUT_QUEUE_NAME = 'QEZJOBLOG'
          AND SPOOLED_FILE_NAME = 'QPJOBLOG';
          
    stop;
    
-- Purpose: Maybe you want to check logging configuration for active jobs
--          PLEASE NOTE: consider using filters on QSYS2.JOB_INFO 
-- Version: 1.0
-- Date 25/01/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.5.0?topic=services-job-description-info-view
-- Note: if you want to check obly job with different logging level than specified in the jobd you can add this WHERE clause:
--       X.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT X.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT X.MESSAGE_LOGGING_TEXT <>
--         Z.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_TEXT

SELECT X.JOB_NAME,
       X.JOB_DESCRIPTION_LIBRARY CONCAT '/' CONCAT X.JOB_DESCRIPTION AS JOBD,
       X.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT X.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT X.MESSAGE_LOGGING_TEXT
           AS CURRENT_VALUE,
       Z.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT Z.MESSAGE_LOGGING_TEXT AS JOBD_VALUE,
       X.JOB_ACTIVE_TIME,
       X.JOB_TYPE,
       X.JOB_SUBSYSTEM
    FROM TABLE (
             QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*ACTIVE', JOB_USER_FILTER => '*ALL')
         ) X
         INNER JOIN QSYS2.JOB_DESCRIPTION_INFO Z
             ON X.JOB_DESCRIPTION_LIBRARY CONCAT '/' CONCAT X.JOB_DESCRIPTION = Z.JOB_DESCRIPTION_LIBRARY CONCAT '/' CONCAT
                     Z.JOB_DESCRIPTION;
                     
    stop;