IBM i - Various uses for the ACTIVE_JOB_INFO() table function

Active Jobs.sql

---------------------------------------------------------------------
-- Description: Various uses for the ACTIVE_JOB_INFO() table function
---------------------------------------------------------------------

--Description: check whether a job (by job name) is currently active in a particular subsystem
SELECT JOB_NAME
    FROM TABLE ( QSYS2.ACTIVE_JOB_INFO( JOB_NAME_FILTER       => :JobName, 
                                        SUBSYSTEM_LIST_FILTER => :SubsystemName ) );

--Description: Jobs in a subsystem that are in a specific status
SELECT *
    FROM TABLE ( QSYS2.ACTIVE_JOB_INFO( SUBSYSTEM_LIST_FILTER => :SubsystemName ) )
    WHERE JOB_STATUS=:JobStatus;

--Description: Netserver jobs
SELECT * FROM TABLE ( QSYS2.ACTIVE_JOB_INFO( SUBSYSTEM_LIST_FILTER => 'QSERVER', 
                                             JOB_NAME_FILTER       => 'QZLSFILE*' ) );

--Description: active jobs that came from a specific JOBQ (give the "subsystem list filter" to make this run in a reasonable time)
SELECT *
    FROM TABLE ( QSYS2.ACTIVE_JOB_INFO( DETAILED_INFO         => 'ALL', 
                                        SUBSYSTEM_LIST_FILTER => :SubsystemName ) )
    WHERE JOB_QUEUE_LIBRARY = :JobqLib
          AND JOB_QUEUE = :JobqName;

--Description: how many jobs are active in each subsystem?
SELECT SUBSYSTEM, COUNT(1) AS JOBCOUNT
    FROM TABLE ( QSYS2.ACTIVE_JOB_INFO() )
    GROUP BY SUBSYSTEM
    ORDER BY COUNT(1) DESC;

--Description: all active jobs, sorted by subsystem
SELECT * 
    FROM TABLE ( QSYS2.ACTIVE_JOB_INFO() ) 
    ORDER BY SUBSYSTEM;