Sooner or later the question arrives, usually from an auditor or from a security team that has just discovered your IBM i exists: which known vulnerabilities affect this system, and are we covered?
Answering it by hand is not hard, it is just tedious. You open the IBM Support security bulletins page, you filter for IBM i, you open one bulletin at a time, you read the “Affected Products and Versions” table, then the “Remediation/Fixes” table, you write down the PTF numbers, and finally you go back to the system and check whether those PTFs are applied. Multiply that by a few dozen CVEs and by a few dozen partitions and you have lost your afternoon.
So I did what I usually do in these cases: I moved the whole thing into SQL. The result is four functions that build on each other, and the last one answers the auditor’s question with a single SELECT.
Let me walk you through them one at a time.
1. Getting the CVE list: SQLTOOLS.CVE_LIST
IBM Support exposes its security bulletin search as a JSON endpoint:
That is all we need. We download it with QSYS2.HTTP_GET and we shred the JSON with JSON_TABLE, mapping each field of the response to a column of the returned table.
A note on the choice of function, because it matters. I am using QSYS2.HTTP_GET, not the old SYSTOOLS.HTTPGETCLOB. I already wrote about this in Stop using SYSTOOLS.HTTP, long life QSYS2.HTTP: the SYSTOOLS versions go through Java, they are much slower, and their certificates live in the Java keystore, which gets replaced every time you apply the Java Group PTF. The QSYS2 versions use DCM and they are dramatically faster. In a function that is going to fire a lot of HTTP requests, that difference is not academic.
Here a comparison between SYSTOOLS version and mine:
The signature is simple:
CREATE OR REPLACE FUNCTION SQLTOOLS.CVE_LIST (
IBMI_RELEASE CHAR(3) CCSID 1208 DEFAULT (
SELECT SYSIBMADM.ENV_SYS_INFO.OS_VERSION CONCAT '.' CONCAT
SYSIBMADM.ENV_SYS_INFO.OS_RELEASE
FROM SYSIBMADM.ENV_SYS_INFO)
)
The default value is the part I like most. If you call the function without arguments, it reads SYSIBMADM.ENV_SYS_INFO and filters on the release of the system you are currently connected to. If you want to check a different release before an upgrade, you just pass it: SQLTOOLS.CVE_LIST('7.6').
The filtering itself happens at the end of the body, on the field_affected_products value returned by IBM:
WHERE CVE_AFFECTED_PRODUCTS LIKE '%' CONCAT IBMI_RELEASE CONCAT '%'
One more thing about the HTTP call. I pass '{"sslTolerate":"true"}' as options, which skips the certificate check. It is convenient while you are testing, but the right thing to do on a production partition is to import the CA into DCM and drop that option.
2. Finding the corrective PTFs: SQLTOOLS.GET_CVE_PTFS
The JSON endpoint tells us that a CVE exists and gives us the URL of its bulletin, but it does not tell us which PTFs fix it. That information only lives in the HTML page.
Now, parsing HTML with SQL is not something I would normally recommend, and I want to be honest about it. The page is static and its structure is stable enough to work with, so I went ahead, but this is the fragile part of the whole chain. If IBM redesigns those pages, this function stops working.
The logic goes like this:
Download the page.QSYS2.HTTP_GET again, cast to a CLOB(2M) in CCSID 1208.
Locate the two sections we care about. A small VALUES table defines them by their headings:
section 1 goes from Affected Products and Versions to Remediation/Fixes
section 2 goes from Remediation/Fixes to Workarounds and Mitigations
REGEXP_INSTR gives us the start position (with return option 1, so we land just after the heading) and the end position, searching forward from the start. SUBSTR then cuts the page between the two.
Extract the tables. Section 1 has one table, section 2 has one table per affected component. To get “the Nth occurrence of a <table>” I use a small numbers table built with ROWNUMBER() over QSYS2.SYSTABLES, limited to 50 rows, and cross join it with the sections. The same trick comes back twice more, for the <tr> rows and for the PTF numbers inside a row. It is a classic, and on IBM i it costs nothing.
Read the licensed program from the header. Each remediation table has the product written in the header of the PTF column, in the form 5770-SS1 PTF Number(s). A REGEXP_SUBSTR picks up the nnnn-XXX pattern and a REGEXP_REPLACE removes the dash, so the value matches what QSYS2.SOFTWARE_PRODUCT_INFO returns. That small normalisation is what makes the join in the next function possible.
Clean the cells. For each row I take the first two <td> or <th> cells, strip the tags, replace and   with a real blank, collapse repeated whitespace and trim. Nothing clever, just three nested REGEXP_REPLACE.
Tell rows apart. In the affected products table the version is in the second cell, in the remediation table the release is in the first one. Testing that cell against ^[0-9]+\.[0-9]+$ does two jobs at once: it identifies the release and it silently discards the header row.
Pull the PTF numbers. Each remediation row can list several fixes, so I read up to twenty occurrences of ([A-Z]{2}[0-9]{5}|SF99[0-9]{3}). When the match starts with SF99 we are looking at a group PTF, and in that case the required level is written next to it as Level nnn, which a second REGEXP_SUBSTR extracts into GROUP_LEVEL.
Deduplicate. Every PTF appears twice in the page, once as text in the cell and once inside its download link, so the final SELECT is a SELECT DISTINCT.
Two parameters, the URL of the bulletin and an optional release filter:
SELECT * FROM TABLE (
SQLTOOLS.GET_CVE_PTFS('https://www.ibm.com/support/pages/node/7283578', '7.6')
);
3. Putting the two together: SQLTOOLS.CVE_LIST_DETAILED
At this point we have a function that lists the CVEs and a function that reads the PTFs out of a bulletin. Joining them is the obvious next step, and SQL makes it painless because CVE_LIST already returns the bulletin URL:
FROM TABLE (SQLTOOLS.CVE_LIST(IBMI_RELEASE)) C
LEFT JOIN TABLE (
SQLTOOLS.GET_CVE_PTFS(C.IBM_SUPPORT_URL, C.IBMI_RELEASE)
) P ON 1 = 1
The LEFT JOIN is deliberate. If a bulletin has no parsable remediation table, or if the page layout has changed, the CVE still shows up in the result with a null PTF instead of disappearing from the report. In a security context, losing a row silently is much worse than showing an incomplete one.
The result is one row per CVE and per corrective PTF, with score, title, publication date, licensed program, affected products, bulletin URL and X-Force link.
4. The actual answer: SQLTOOLS.CVE_STATUS
This is the function that answers the auditor. It takes what we have collected from IBM and compares it against the system you are running on.
Three ingredients:
the current release, read from the QSS1MRI data area in QUSRSYS through QSYS2.DATA_AREA_INFO, and reformatted as V.R
the applied PTFs, from QSYS2.PTF_INFO, keeping the ones in status APPLIED, PERMANENTLY APPLIED or SUPERSEDED
the installed licensed programs, from QSYS2.SOFTWARE_PRODUCT_INFO
Both lists go into global temporary tables, then CVE_LIST_DETAILED is joined against them and two statuses are derived:
AFFECTED_STATUS answers “does this CVE concern me at all”. If the licensed program named in the bulletin is installed on the system, the answer is AFFECTED. If it is not installed, it is NOT AFFECTED, and you can stop worrying about it. Note that when the parser could not determine the LPP, the row is treated as AFFECTED. Again, better a false positive than a missed vulnerability.
COVERED_STATUS answers “have I already fixed it”. If the corrective PTF is in the applied list, the row comes back as COVERED, together with the date it was applied, which is handy when someone asks when you patched. Otherwise it is NOT COVERED.
Three optional parameters let you slice the result:
COMMENT ON PARAMETER SPECIFIC ROUTINE SQLTOOLS.CVESTATUS (
IS_AFFECTED IS 'AFFECTED, NOT AFFECTED or *ALL (default)',
IS_COVERED IS 'COVERED, NOT COVERED or *ALL (default)',
SCORE_CRIT IS 'Critical, High, Medium, Low or *ALL (default)'
);
Using it
The full picture for the system you are connected to:
SELECT * FROM TABLE (SQLTOOLS.CVE_STATUS());
And the query that really matters, the one I would run before a patching window:
Swap Medium for Critical or High and you have your priority list, with the PTF numbers already in the result. From there it is a short step to feeding those numbers into an SNDPTFORD, or to scheduling the query and mailing the output every Monday morning.
A few caveats
Before you copy this into production, keep two things in mind.
The first one I have already mentioned: GET_CVE_PTFS reads an HTML page written for humans. It works today, it may break the day IBM changes that template. Treat the function as something to keep an eye on, not as a documented API.
The second is the meaning of COVERED. The check is done on the single PTF number, so a fix delivered only inside a group PTF that you have not applied at the required level will show up as not covered even if the individual PTF is superseded elsewhere. The GROUP_LEVEL column is there exactly to let you make that call yourself.
All the functions use SET OPTION USRPRF = *USER and DYNUSRPRF = *USER, so whoever calls them needs the authority to read PTF_INFO and SOFTWARE_PRODUCT_INFO, plus outbound HTTPS access from the partition.
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:
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?
Recently, a client reported performance issues with some of his new SQLRPGLE programs that were retrieving data via REST APIs provided by the other party.
Now, after doing a bit of analysis, we immediately identified the cause of that slowness: the SYSTOOLS HTTP functions were being used to call the APIs. These functions were fine, but that was before the introduction in 2021 of the new functions in QSYS2, which allow you to achieve the same result in much, much less time. Here’s an example: as you can see, the version using SYSTOOLS (with the same results) is significantly longer.
The reason for the slowness lies in the fact that the functions in SYSTOOLS are based on Java services that are used specifically to process the data exposed by the endpoint. Of course, it’s also important to keep in mind that on IBM i, only one JVM is allowed per job—a detail to keep in mind when launching JVMs with custom parameters.
So there’s the performance issue, but there’s also a security-related problem. In fact, in an increasingly interconnected world, even APIs rightly respond over HTTPS, and this requires that the CA of our endpoint be trusted. The problem, however, is that the functions in SYSTOOLS are tied to Java, so the CAs must be imported into the Java keystore, which, by default, is replaced every time Java Group PTFs are installed. Functions in QSYS2, on the other hand, use the DCM keystore or a custom keystore; in either case, the entries imported into it remain persistent, at least until the certificates become deprecated, etc.
But now, how can I tell if I have any programs or jobs that use these functions in SYSTOOLS? It’s simple, let’s put these objects under audit.
The theory is simple: you run the CHGOBJAUD command on the objects in SYSTOOLS called HTTP….. Too, too easy—because, as shown in the screenshot, there are two types of functions with the same name: the wrapper function, which is called by passing an XML parameter, and the actual function, which actually refers to a Java class, meaning it’s an externally defined procedure. In this case, there is no object in SYSTOOLS, so…
However, I have verified that in this case, the Java classes are located at the path /qibm/userdata/OS400/SQLLib/Function/jar/SYSTOOLS/DB2RESTUDF.jar
So the solution is running following commands: CHGOBJAUD OBJ(SYSTOOLS/HTTP) OBJTYPE(ALL) OBJAUD(*ALL) and CHGAUD OBJ('/qibm/userdata/OS400/SQLLib/Function/jar/SYSTOOLS/DB2RESTUDF.jar') OBJAUD(*ALL)
The good news is that our objects are now being audited, so I can query the QAUDJRN to check if the objects are being used… The bad news is that, since they’re in a JVM, the entry is generated only once per job, so it’s not possible to determine if the function is used multiple times within the same job.
This SQL query retrieves all ZR-type entries (object reads) from the last hour; as you can see, one of them is from me:
Managing objects in the IBM i QSYS file system has always been a challenge. The traditional approach requires switching between multiple CL commands ‘WRKOBJ’, ‘WRKJOB’, ‘WRKSPLF’, ‘DSPOBJ’, each with its own interface and limited filtering capabilities. You find yourself jumping between screens, remembering obscure command parameters, and repeating the same tedious navigation patterns over and over.
What if you could manage 22 different IBM i object types through a single, unified interface inside VS Code? Welcome to Code4iFS, a VS Code extension that modernizes IBM i systems management by bringing the power of the QSYS file system directly into your editor. You can install it using this link, or it’s automatically installed if you already have IBM i Development Pack.
The Problem: Fragmented Object Management
IBM i systems administrators and developers face a critical workflow challenge. When you need to:
– View the contents of a save file
– Monitor active jobs and their resource usage
– Manage spooled files across multiple output queues
– Inspect data queues for debugging
– Work with data areas and user spaces
– Query database files directly
…you’re forced to use different command-line tools, each with its own syntax and limitations. This fragmentation creates context-switching overhead and makes automation difficult. Moreover, searching across multiple objects requires manual effort—there’s no unified search or filtering experience.
The Solution: Comprehensive Object Management in Your Editor
Code4iFS extends the Code for IBM i extension by providing a unified interface for 22 different IBM i object types with modern, interactive views. Instead of memorizing CL commands, you work with intuitive, searchable interfaces that feel native to VS Code.
Key Features: What Makes Code4iFS Different
1. Unified Interactive Views
Instead of juggling multiple CL commands, Code4iFS provides five main interactive views:
Display Object Information (DSPOBJ) — A modern replacement for the traditional `WRKOBJ`/`DSPOBJD` command. Available for all 22 object types, this view provides:
– Object Browser context menu → Display Object Information
– Editor toolbar icon
Work with Active Jobs (WRKACTJOB) — Real-time monitoring of all active jobs with:
– Subsystem, job name, user, type, and status
– CPU and I/O metrics
– Searchable interface with auto-refresh
– Actions: hold, release, end, or debug jobs
Work with Job (WRKJOB) — Deep job analysis including:
– Job Information tab (status, times, attributes, resource usage)
– Job Statistics tab with four sub-sections:
– Call Stack — Program call chain
– Locks — All locks held by the job
– Open Files — Files opened and their access patterns
– Spooled Files — Output generated by the job with download/delete actions
– Job Log — Complete message history with severity and timestamps
– Auto-refresh every 30 seconds
Work with Spooled Files (WRKSPLF) — Centralized spool file management:
– Search across all spool files by name, user, status, or user data
– Pagination for large result sets
– Actions: open in editor, download as PDF, delete
– Real-time filtering as you type
Work with User Jobs (WRKUSRJOB) — Comprehensive job view:
– All jobs (active and inactive) in a single searchable interface
– Job status, type, completion status
– Conditional actions based on job state
– Join between active and historical job data
2. Search and Filter Everywhere
Every view supports real-time search across all columns:
– Type to filter job names, object names, statuses, or descriptions
– No need to memorize SQL syntax or CL WHERE clauses
– Results update instantly as you type
– Search context is preserved across pagination
3. Native SQL Integration
For advanced users, many actions leverage SQL table functions to provide programmatic access:
– Query journal entries using SQL instead of tape searching
– Inspect database file contents with `SELECT` queries
– Access system information through `QSYS2.*` table functions
– Translate legacy `*QRYDFN` objects to modern SQL
4. Multi-Tab Interface with Contextual Organization
Each view uses a modern webview-based UI with:
– Sortable, sticky-header tables
– Collapsible sections for detailed information
– Color-coded status indicators
– Dark/Light theme support matching VS Code
5. Multilingual Support
Code4iFS ships with 9 languages using VSCode’s built-in localization framework:
– 🇬🇧 English (default)
– 🇮🇹 Italian
– 🇫🇷 French
– 🇩🇪 German
– 🇪🇸 Spanish
– 🇯🇵 Japanese
– 🇰🇷 Korean
– 🇧🇷 🇵🇹 Brazilian Portuguese
– 🇨🇳 Simplified and Traditional Chinese
With 1200+ translated strings, all UI elements adapt to your VS Code language automatically.
Why This Matters: Bringing IBM i Management into the Modern Era
IBM i systems are often perceived as “legacy,” but their reliability and performance are unmatched. The challenge isn’t the platform—it’s the user experience. By bringing object management into VS Code, Code4iFS:
1. Reduces Context Switching — No more Alt-Tabbing between 5×25 screens and your IDE
2. Enables Discoverability — Modern search interfaces replace memorized commands
4. Improves Accessibility — Intuitive UI lowers the barrier for new IBM i developers
5. Preserves Power — Advanced users still have SQL and CL command access
If you’re an IBM i systems administrator or developer, what’s your biggest pain point in object management? Is it the command-line interface, the lack of search capabilities, the overhead of context-switching, or something else entirely? How would a modern, integrated experience change your daily workflows?
I often find myself in situations that require quick action and a strong commitment to system analysis.
When there is a sudden spike in system temporary storage growth—often caused by an abnormal increase in temporary storage consumption—it becomes difficult to pinpoint which jobs are actually responsible for the increased resource consumption.
Before reaching critical situations, it can therefore be very useful to promptly identify the jobs that are consuming the most temporary storage.
In this case, the SQL services provided by IBM come to our aid. Using the ACTIVE_JOB_INFO table function, it is possible to obtain detailed information about active jobs and analyze their temporary storage consumption.
Basic Usage Example
SELECT JOB_NAME,
SUBSYSTEM,
TEMPORARY_STORAGE
FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) X
ORDER BY TEMPORARY_STORAGE DESC
FETCH FIRST 10 ROWS ONLY
This table function—like many others provided by IBM—allows you to simplify and automate numerous tasks that would otherwise have to be performed manually.
In this specific case, the query returns the 10 jobs that are using the most temporary storage, sorted in descending order. This makes it possible to quickly identify the main cause of the increase in temporary storage usage and take action before the situation becomes critical.
This can, of course, be implemented to support other types of monitoring (such as SYSBAS disk space monitoring) and QCMDEXC to immediately HOLD the jobs causing the temporary storage increase (although this could be particularly dangerous).
Deep Dive: When the Culprit is Known
But what if the “culprit” is already known?
In this case, you can retrieve additional information to understand why the situation occurred, so as to prevent it from happening again in the future.
From the same family of SQL services, you can retrieve the SQL statement currently being executed:
SELECT JOB_NAME,
SQL_STATEMENT_TEXT
FROM TABLE(
QSYS2.ACTIVE_JOB_INFO(
DETAILED_INFO => 'ALL',
SUBSYSTEM_LIST_FILTER => 'SBS',
CURRENT_USER_LIST_FILTER => 'USER'
)
) WHERE JOB_NAME LIKE '%NAME%';
This allows you to identify the SQL query that the job has currently running—or possibly waiting—and can provide very useful information for debugging the issue.
Beyond This Use Case
Of course, this is just one of the many possible uses of SQL in an IBM i environment. The SQL services provided by IBM offer extremely powerful tools for performing a wide variety of tasks, such as remotely executing commands directly via SQL.
But that could be a topic for a future discussion.
What do you think about this topic? Let me know!
In the meantime, the official IBM documentation on SQL services is available here, complete with examples and use cases:
