Seeing which jobs use the most CPU

SELECT JOB_NAME, AUTHORIZATION_NAME, SUBSYSTEM,
       ELAPSED_CPU_PERCENTAGE, CPU_TIME, FUNCTION
  FROM TABLE(QSYS2.ACTIVE_JOB_INFO())
 WHERE JOB_TYPE <> 'SYS'
 ORDER BY ELAPSED_CPU_PERCENTAGE DESC
 FETCH FIRST 15 ROWS ONLY

Run it twice. ELAPSED_CPU_PERCENTAGE is the CPU percentage over the measurement interval, and IBM says the first call to ACTIVE_JOB_INFO in a connection always resets the statistics: the first time, the interval has only just started and the percentages say nothing. Wait a few seconds and run the same query again, in the same Run SQL Scripts session: the second run shows who consumed in between, that is who is consuming now. CPU_TIME is instead the total since the job started.

To look at a single subsystem:

SELECT JOB_NAME, ELAPSED_CPU_PERCENTAGE, FUNCTION
  FROM TABLE(QSYS2.ACTIVE_JOB_INFO(SUBSYSTEM_LIST_FILTER => 'QBATCH'))
 ORDER BY ELAPSED_CPU_PERCENTAGE DESC

Changing the filter resets the statistics again: here too, the second run is the one that counts.

Example: "yesterday at three it was crawling"

WRKACTJOB shows now; the problem was yesterday. A job scheduled every five minutes that writes the top five consumers leaves a trail. The table is created from the query itself, so the service decides the column names and types:

CREATE TABLE MYLIB/CPUHIST AS (
  SELECT CURRENT TIMESTAMP AS TAKEN_AT, JOB_NAME,
         AUTHORIZATION_NAME, ELAPSED_CPU_PERCENTAGE, FUNCTION
    FROM TABLE(QSYS2.ACTIVE_JOB_INFO())
) WITH NO DATA

Then the CL to schedule. For the same reason as above it reads twice in the same job: the first call sets the start of the interval, the second, a minute later, measures:

RUNSQL SQL('CREATE TABLE QTEMP/START AS                     +
            (SELECT JOB_NAME FROM TABLE(QSYS2.ACTIVE_JOB_INFO())) +
            WITH NO DATA') COMMIT(*NONE)
DLYJOB DLY(60)
RUNSQL SQL('INSERT INTO MYLIB/CPUHIST                        +
            SELECT CURRENT TIMESTAMP AS TAKEN_AT, JOB_NAME,   +
                   AUTHORIZATION_NAME, ELAPSED_CPU_PERCENTAGE, FUNCTION +
              FROM TABLE(QSYS2.ACTIVE_JOB_INFO())            +
             WHERE JOB_TYPE <> ''SYS''                       +
             ORDER BY ELAPSED_CPU_PERCENTAGE DESC            +
             FETCH FIRST 5 ROWS ONLY') COMMIT(*NONE)

The next day:

SELECT * FROM MYLIB/CPUHIST
 WHERE TAKEN_AT BETWEEN '2026-08-25-14.30.00' AND '2026-08-25-15.30.00'
 ORDER BY TAKEN_AT, ELAPSED_CPU_PERCENTAGE DESC

Dates and times are an example.

Releases. The named parameters of QSYS2.ACTIVE_JOB_INFO arrived over time. If the call with SUBSYSTEM_LIST_FILTER is rejected, run the function with no parameters and filter on SUBSYSTEM in the WHERE.

Sources

• IBM: ACTIVE_JOB_INFO table function