TechTip Wednesday: Using SQL to help with your PTFs (or, patch the holes!)

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.

 The Growing Importance of IBM i SQL Services 

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.

Why IBM i Administrators Must Prioritize Patching 

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!)

Understanding Today’s Security Landscape on IBM i 

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.

How IBM FLASH Alerts Highlight Critical Vulnerabilities 

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.

Building a Practical IBM i Patching Strategy 

Here’s an outline to get you started!

1. Sign up for IBM My Notifications. Use the tools in their subscription service to allow you to get the updates that you want to see.
2. Bookmark the IBM pages for recommended fixes for quick reference:
   1. IBM i PTF FAQs
   2. IBM i recommended fixes by version
   3. IBM i latest group PTF levels (this link may be replaced in the future, as noted on the page itself)
   4. IBM i known issues and fix information page (this is supposed to replace the link on item b above)
   5. Fix Level Recommendation Tool (FLRT) – great for matching up your firmware, HMC, and OS levels to get recommended updates
3. Use tools on your partitions to gather your local PTF and firmware information.
   1. Automated listings via SQL – these require that your system have access to reach certain IBM sites for comparisons. If that access is restricted, these two may not work for you.
      1. Firmware currency – example SQL below
      2. Group PTF currency – example SQL below
   2. Manual Listings options
      1. Firmware Check via HMC
      2. Group PTF lists – example SQL below
4. Determine a deployment strategy
   1. This needs to include a full save of the operating system and LIC. You need a recovery point in the rare instance of a failure with the PTFs.
   2. Single system – find a window to get your save completed, and to load/apply the PTFs.
   3. Multi-system/partition – pick a test or development system for the PTFs to be staged initially, then determine a pathway to get those same packages moved up to your production system.
5. Apply your PTFs permanently periodically. If you put on fix packs regularly in a specific cadence, then you have a pattern to move PTFs to *PERM status. For instance, before the next TR (Technology Refresh) comes out that you want to apply, you might set the existing temp PTFs to *PERM status ahead of that TR load.
   1. This helps to make your system more stable should you need to boot to the ‘A’ side, since that only uses permanent PTFs there. If your last OS upgrade was 3 years ago – and you have not perm applied any PTFs since that time, your system is 3 years behind in terms of the ‘A’ side.
   2. It saves disk space as well – temporary PTFs consume a fair amount of disk space.
6. Honorable mention – keep up to date on your open-source products. If it does not have an IBM ‘part’ number, then it will not be patched with PTFs. So, check the repositories for any updates to your open-source products that you might have installed onto your IBM i when you gather new group PTFs.

Using SQL Services to Check Firmware and PTF Currency 

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.

Firmware Currency

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;

Group PTF 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;

Manual Methods for Reviewing PTF Levels 

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;

Bonus SQL Statements for PTF Status Analysis 

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;

Final Thoughts: Treat IBM i Like the Modern Platform It Is 

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!