IBM i SQL services provide powerful system‑administration capabilities that go far beyond traditional CL commands, allowing administrators to extract, analyze, and export detailed system and IFS data. This article demonstrates how to use IFS_OBJECT_STATISTICS and related services to identify large objects, track storage consumption, and better manage the Integrated File System.
IBM has been leveraging SQL services to gain access to various system functions for several versions now. Often, these services give you access to system administration functions that do not exist in CL commands. For instance, there are many CL commands to view information, but those often only offer a print option to save data for comparison or review later. But there is often an IBM i service offering that will let you run an SQL statement and get that same data into a file or exportable format.
These SQL services are being added to, or enhanced, all the time with the DB2 PTF groups and the Technology Refresh (TR) updates.
https://www.ibm.com/support/pages/ibm-i-services-sql
This series will give a tech note on utilizing some of these IBM i services as a systems administrator.
This week’s entry is a bit off topic to start with, as we will be talking about IBM i patching. (Don’t fret, as there will be some SQL in this article though!)
This is prompted by the state of our world today, as there are exploits and vulnerabilities that are frequently found. Even some that have potentially been around for years and are only now coming to light. For many years, we were granted a level of “security by obscurity”, and while that is not a trustworthy practice in general – it did allow for some apathy to occur in terms of patching. Over time, I often heard the adage, “If the system is running stable, then we don’t need to load PTFs that often.” But the truth is, and always was, quite a distance from the quip.
As the IBM i operating system widens its stance to use more open-source tools and thusly becomes more “modern” in the views of general IT viewpoints, it also becomes more at risk to vulnerabilities in those functions.
At the time of writing this article, I have received no fewer than three ‘IBM FLASH’ alerts for security fixes on Power systems in the past week. One of those was specifically for the IBM i implementation of OpenSSH. Since the beginning of 2026, I counted more than 30 alert emails for urgent fixes related to a CVE for Power systems or one of the operating systems that run on Power. If these emails are being sent, it means that a fix has been generated for a known issue. But it only helps if you load and apply the patches that are generated to fix those issues!
There are as many methods to gather, load, and apply PTFs as one can imagine. What I am offering below is a simple methodology that may work for you, or it might require some modification for your environment. In other words, “your milage may vary”, so make a process that works for you.
Here’s an outline to get you started!
So, I promised some SQL for this article – so here we go. These are all from the ‘Insert from Examples’ within ACS – so nothing new here, but they can be quite helpful.
This one is a SYSTOOLS function that will connect to the internet repository (FLRT) and compare your system firmware level to see if you are current. If your system does not have access to the internet, then you might have to look this up manually with a DSPFMWSTS command (or view via your HMC).
SELECT *
FROM SYSTOOLS.FIRMWARE_CURRENCY;
Same as above, this SYSTOOLS function will reach out to the IBM PSP site as a reference. It will compare your PTF groups that are installed versus the latest available groups.
SELECT *
FROM SYSTOOLS.GROUP_PTF_CURRENCY
ORDER BY PTF_GROUP_LEVEL_AVAILABLE - PTF_GROUP_LEVEL_INSTALLED DESC;
Let’s say that your IBM i partition is not open to the internet, but you want to check your group PTF levels? There’s the plain old WRKPTFGRP command, but we like SQL! So, let’s pull that manual list into something we can store in a spreadsheet if like.
SELECT *
FROM QSYS2.GROUP_PTF_INFO;
Since this is an SQL series, here are a couple of bonus statements that you can use to check on statuses of individual PTFs as well.
What PTFs are going to be affected at your next IPL? Here’s a shorted method to find all of those instead of using DSPPTF:
SELECT PTF_IDENTIFIER, PTF_IPL_ACTION, A.*
FROM QSYS2.PTF_INFO A
WHERE PTF_IPL_ACTION <> 'NONE';
Do you have any PTFs that are loaded, but not applied? Here is a variation of the statement above to look for the ‘LOADED’ status:
SELECT PTF_IDENTIFIER,
PTF_PRODUCT_DESCRIPTION,
A.*
FROM QSYS2.PTF_INFO A
WHERE PTF_LOADED_STATUS = 'LOADED'
ORDER BY PTF_PRODUCT_ID;
This article is really meant to serve as a reminder to patch your systems. We cannot rely on invisibility as a security tool, especially in the age of AI. There are more vulnerabilities being exploited regularly, and there is even a website that includes some decent amount of focus on the IBM i platform. I fear that it will only get worse as time goes on. We strive to make sure that the populace knows that the IBM I is a modern platform, so isn’t time that we (the administrators and programmers) treat it as such?
Patch the holes, and keep the ship afloat!