IBM i - Investigate a "high number of jobs in system" situation in your LPAR

High Jobs in System.sql

--
-- Description: Queries for investigating a "high number of jobs in system" situation.
--              This walks through the process of figuring out whether the high number of jobs is being driven by a lot of 
--              active jobs, jobs sitting in JOBQs, or jobs that are in OUTQ status.  Once the source of the high number of 
--              jobs has been determined, there are additional queries which can be used to investigating each of those 
--              possible situations.
--

--Description: Quick overview of the LPAR's "jobs in system" - this will tell you whether there are a lot of jobs in ACTIVE, JOBQ, or OUTQ status
SELECT ACTIVE_JOB_TABLE_ENTRIES AS ACTIVE_JOBS, 
       JOBQ_JOB_TABLE_ENTRIES AS JOBQ_JOBS, 
       OUTQ_JOB_TABLE_ENTRIES AS OUTQ_JOBS, 
       JOBLOG_PENDING_JOB_TABLE_ENTRIES AS JOBLOG_PENDING,
       'TOTAL JOBS--> ' || CHAR(TOTAL_JOBS_IN_SYSTEM) AS TOTAL_JOBS, 
       'MAX JOBS--> ' || CHAR(MAXIMUM_JOBS_IN_SYSTEM) AS MAX_JOBS, 
       'PERCENT--> ' || CHAR(TOTAL_JOBS_IN_SYSTEM / (MAXIMUM_JOBS_IN_SYSTEM/100)) AS PERCENT_FULL
    FROM TABLE ( QSYS2.SYSTEM_STATUS(DETAILED_INFO => 'ALL') );


--Description: get a list of JOBQs that have a lot of jobs waiting on them
SELECT JOB_QUEUE_NAME, JOB_QUEUE_LIBRARY, COUNT(1) AS NO_OF_JOBS
    FROM TABLE ( QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ', JOB_USER_FILTER => '*ALL') )
    GROUP BY JOB_QUEUE_NAME, JOB_QUEUE_LIBRARY
    ORDER BY NO_OF_JOBS DESC;


-- Description: get a distinct list of ACTIVE jobs by job name, and a count of each
SELECT JOB_NAME_SHORT, COUNT(1) AS COUNT_OF_JOBNAME
    FROM TABLE ( ACTIVE_JOB_INFO() )
    GROUP BY JOB_NAME_SHORT
    ORDER BY COUNT(1) DESC;


--Description: get a distinct list of JOBS IN SYSTEM by job name, and a count of each (this query can take 5 minutes or even longer)
SELECT JOB_NAME_SHORT, COUNT(1) AS NO_OF_JOBS
    FROM TABLE ( QSYS2.JOB_INFO(JOB_USER_FILTER => '*ALL') )
    GROUP BY JOB_NAME_SHORT
    ORDER BY NO_OF_JOBS DESC;


--Description: get a list of OUTQs that have a high number of spooled files (typically QEZJOBLOG or PRT99 but can be others)
SELECT *
    FROM QSYS2.OUTPUT_QUEUE_INFO
    ORDER BY NUMBER_OF_FILES DESC
    FETCH FIRST 10 ROWS ONLY;


-- Description: Summarize the spooled files in QEZJOBLOG outq
SELECT SPOOLNAME, USER_NAME, USER_DATA, COUNT(1) AS COUNT_OF_JOBLOG
    FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
    WHERE OUTPUT_QUEUE_NAME = 'QEZJOBLOG' --the OUTQ can be replaced with any outq that has a large number of spooled files
    GROUP BY SPOOLNAME, USER_NAME, USER_DATA
    ORDER BY COUNT(1) DESC;
-- Detail - based on the previous query, find some specific individual jobs in order to inspect their spooled files from the greenscreen.
SELECT JOB_NAME, SPOOLED_FILE_NAME, USER_NAME, USER_DATA, STATUS
    FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
    WHERE OUTPUT_QUEUE_NAME = 'QEZJOBLOG' --update these 3 values
          AND USER_NAME = 'QSYSOPR'       --   based on the results
          AND USER_DATA = 'UKCMPHISTU'    --   of the previous query
    FETCH FIRST 5 ROWS ONLY;


-- Detail - Submit a job to delete the spooled files associated with the offending jobs identified above.  Populate the "offendinguser" and "offendingjobname"
CL:SBMJOB CMD(DLTSPLF FILE(*SELECT) SELECT(offendinguser *ALL *ALL offendingjobname)) JOB(DLTSPLF) JOBQ(QUSRNOMAX);