Use SQL to analyze Jobs and Subsystems

Recently, a client came to me with a request that sounds trivial but is actually quite common: he wanted to know which jobs had passed through one specific subsystem, and when. Not the jobs running right now, anyone can get those with WRKSBSJOB, but the full history: everything that started under, say, QBATCH, whether it was still active, already finished, or just sitting on an output queue.

The active picture is easy. The historical one is where things get interesting, because once a job ends it disappears from the job tables. So where do we look? The answer, as almost always on IBM i, is the history log (QHST), and the SQL service that lets us read it without ever touching DSPLOG: QSYS2.HISTORY_LOG_INFO().

Let me walk you through the idea and then share the function.

Every time a job starts and ends in a subsystem, the system writes a message to QHST. The two we care about are:

• CPF1124 – Job started. This is logged when the job begins running in the subsystem. Crucially, its message tokens carry the subsystem name and library the job started in.
• CPF1164 – Job ended/completed. Logged when the job finishes.

So the plan is simple: read the history log for the time window we want, keep only CPF1124 and CPF1164, then pair each “started” with its matching “ended” by qualified job name. If a start has no matching end, the job is still active.

The one detail that isn’t obvious is how to know which subsystem a CPF1124 refers to. That information is not exposed as a dedicated column, it lives inside MESSAGE_TOKENS, the raw replacement data of the message. By slicing that field at fixed offsets we can rebuild the LIBRARY/SUBSYSTEM value:

TRIM(SUBSTR(S.MESSAGE_TOKENS, 69, 10)) CONCAT '/' CONCAT
TRIM(SUBSTR(S.MESSAGE_TOKENS, 59, 10)) AS SBS

That gives us something like QSYS/QBATCH, which is exactly what we later filter on.

Wrapping it all in a table function

Rather than running an ad-hoc query every time, I packaged the whole thing as a SQL table function, SQLTOOLS.GET_SBS_JOB. That way the client can just do a SELECT ... FROM TABLE(...) and pass the subsystem and the time window as parameters.

What happens, step by step

Let’s break the body down, because there is a bit more going on than a single SELECT.

1. A working table. The function first drops and recreates a global temporary table, SESSION.DSPLOG. It is declared WITH NO DATA from the shape of HISTORY_LOG_INFO() (the WHERE 1 = 0 trick just borrows the column definitions without reading a single row), and then filled in a separate INSERT. Reading the history log once into a temp table means we can scan it twice afterwards, once for the starts, once for the ends, without hitting QHST again.

2. Reading the history log for the window. The INSERT calls HISTORY_LOG_INFO() with the START_TIME and END_TIME parameters built from the input strings, and keeps only the two message IDs we discussed. This is where the time window passed by the caller actually bites: everything else is discarded. During this step we also compute the SBS value from the message tokens, so each row already knows which subsystem it belongs to.

3. Pairing starts with ends. The final RETURN builds two derived tables from the temp table: one of CPF1124 rows (the starts) and one of CPF1164 rows (the ends). They are joined with a LEFT JOIN on the qualified job name, so a start that has no corresponding end simply comes back with a NULL end timestamp, meaning the job never completed in the window and is therefore still active.

4. Enriching with JOB_INFO. A second LEFT JOIN, this time against QSYS2.JOB_INFO, pulls the job queue for jobs still known to the system. This is what lets the function tell apart a job that is truly gone from one whose spooled output is still hanging around.

5. Deriving the status. The CASE expression turns all of the above into a friendly status:

• *ACTIVE – there is a start but no end: the job is still running.
• *END – the job ended and JOB_INFO no longer reports a job queue for it: fully finished and cleaned up.
• *OUTQ – the job ended but is still associated with a job queue: its output/entry is still present on the system.

6. Filtering by subsystem. The final WHERE keeps only the rows whose reconstructed SBS matches the library/subsystem the caller asked for.

One subtle line worth pointing out is AND MESSAGE_SECOND_LEVEL_TEXT IS NULL on the CPF1124 side. It’s there to keep a single clean “start” row per job and avoid the function multiplying rows when the history log carries the message in more than one form.

Using it

Calling the function is now as simple as it gets. Give it the subsystem library, the subsystem name and a start time; the end time defaults to “forever”, so you can leave it out if you just want everything from a point in time onwards:

SELECT *
  FROM TABLE (
        SQLTOOLS.GET_SBS_JOB(
            INSBSLIB    => 'QSYS',
            INSBS       => 'QBATCH',
            INSTARTTIME => '2026-07-14 10:00:10')
       );

And there you have it: every job that went through QBATCH since 10 a.m., each one with its start and end timestamp, its job queue and a status telling you whether it’s still running, already gone or still sitting on an OUTQ, all from a single SELECT, no DSPLOG, no green screen.

A couple of things worth keeping in mind. First, everything depends on the retention of your history log: if QHST doesn’t go back far enough, neither will the function. Second, the token offsets used to rebuild the subsystem (positions 59 and 69) match the CPF1124 layout, so double-check them if you ever adapt this to a different message.

And you, how do you keep an eye on what runs through your subsystems?

You can get source code clicking on this link.

Andrea