Please enhance the QSYS2.PTF_INFO SQL service to optionally return all PTFs known to the system, including PTFs related to product options that are not currently installed.
Today, QSYS2.PTF_INFO only returns PTF information for installed products/options. This means it does not expose some PTF information that is visible through DSPPTF, especially for PTFs in statuses such as SUPERSEDED or SAVE FILE ONLYwhen the related product option is not installed.
This creates a gap for SQL-based security, compliance, and automation tooling.
Current behavior
DSPPTF can show PTF information for optional parts of a product even when the specific option is not installed, because the PTF index is stored in the library of the *BASE option of the licensed program product.
However, QSYS2.PTF_INFO currently only returns PTFs for installed products/options.
For example, on an IBM i 7.5 LPAR, DSPPTF can show information for 5770PT1 option 2 PTFs at release V7R4M0, including PTFs shown as SUPERSEDED or SAVE FILE ONLY.
However, SQL queries such as the following return zero rows:
SELECT *
FROM QSYS2.PTF_INFO
WHERE PTF_IDENTIFIER = 'SJ00275';
SELECT *
FROM QSYS2.PTF_INFO
WHERE PTF_IDENTIFIER = 'SJ01500';
At the same time, DSPPTF is able to show information for those PTFs.
Requested enhancement
Please add an option to QSYS2.PTF_INFO to allow the caller to choose whether to return:
Only PTFs for installed products/options, which would preserve the current behavior.
All PTFs known to the system, including PTFs for non-installed product options, matching the broader visibility available through DSPPTF.
For example, this could be implemented as an optional parameter, such as:
QSYS2.PTF_INFO(SCOPE => '*INSTALLED')
QSYS2.PTF_INFO(SCOPE => '*ALL')
The exact parameter name is only a suggestion. The important requirement is to preserve the current default behavior for compatibility and performance, while allowing SQL-based tools to explicitly request the broader PTF view when needed.
Expected value
This enhancement would allow IBM i administrators, service providers, security teams, and tool developers to build more accurate SQL-based PTF and CVE compliance reporting.
It would also make QSYS2.PTF_INFO a more complete SQL interface for PTF information, while still allowing IBM to preserve the current behavior as the default for performance reasons.
Compatibility consideration
The current behavior should remain the default to avoid impacting existing users or performance expectations.
The broader PTF view should only be enabled when explicitly requested by the caller.
Requested outcome
Please enhance QSYS2.PTF_INFO so that it can optionally return all PTFs known to the system, including PTFs for non-installed product options, and document the difference between the default installed-product view and the broader all-known-PTF view.
Only PTFs for installed products/options, which would preserve the current behavior.
All PTFs known to the system, including PTFs for non-installed product options, matching the broader visibility available through DSPPTF.
The CAAC has reviewed this IBM Idea and recommends that IBM view this as a “nice to have” low priority feature.
Background: The COMMON Americas Advisory Council (CAAC) members have a broad range of experience in working with small and medium-sized IBM i customers. CAAC has a key role in working with IBM i development to help assess the value and impact of individual IBM Ideas on the broader IBM i community and has therefore reviewed your Idea.
For more information about CAAC, see www.common.org/caac
Brandon Pederson - CAAC Program Manager
I agree that it should be nice to have an SQL service that returns all released PTF's.
IBM updates a CSV file every week with all (or almost) all PTF's released for a specific release as SF97xxx.csv (where xxx is the release) at https://public.dhe.ibm.com/services/us/igsc/PSP/
The information is currently available for 710, 720, 730, 740, 750 and 760.
If you have the IBM i Open Source repo installed, i.e curl is installed, you can easily create your own table function to download an query this information.
This is a simple example how to create an table function for this, change to fit your needs:
-- description: Create Function to list PTF's released for a specific release
create or replace function MYLIB.PTFLIST (
IBMI_VRM char(3) default '750'
)
returns table (
PRODUCT char(7),
VRM char(6),
PTF char(7),
PKG char(6),
AVAIL_DATE date,
MRI_FEATURE char(4),
REPLACED_BY char(7),
CATEGORIES char(3),
ABSTRACT varchar(120)
)
language SQL
specific PTFLIST
not deterministic
modifies sql data
no external action
disallow parallel
not fenced
begin
declare CMDTEXT varchar(1024);
set CMDTEXT = 'QSYS/ADDENVVAR ENVVAR(QIBM_MULTI_THREADED) VALUE(Y) LEVEL(*JOB) REPLACE(*YES)';
call QSYS2.QCMDEXC(CMDTEXT);
set CMDTEXT = 'QSYS/QSH CMD(''PATH=/QOpenSys/pkgs/bin:$PATH;export PATH;curl -o /tmp/SF97' concat
IBMI_VRM concat
'.csv https://public.dhe.ibm.com/services/us/igsc/PSP/SF97' concat
IBMI_VRM concat '.csv'')';
call QSYS2.QCMDEXC(CMDTEXT);
drop table if exists QTEMP.PTFLIST;
create or replace table QTEMP.PTFLIST (
PRODUCT char(7),
VRM char(6),
PTF char(7),
PKG char(6),
AVAIL_DATE date,
MRI_FEATURE char(4),
REPLACED_BY char(7),
CATEGORIES char(3),
ABSTRACT varchar(120)
);
set CMDTEXT = 'QSYS/CPYFRMIMPF FROMSTMF(''/tmp/SF97' concat IBMI_VRM concat
'.csv'') TOFILE(QTEMP/PTFLIST) MBROPT(*REPLACE) RCDDLM(*LF) STRDLM(*NONE) FROMRCD(2)';
call QSYS2.QCMDEXC(CMDTEXT);
set CMDTEXT = 'QSYS/RMVLNK OBJLNK(''/tmp/SF97' concat IBMI_VRM concat '.csv'')';
call QSYS2.QCMDEXC(CMDTEXT);
return select PRODUCT,
VRM,
PTF,
PKG,
timestamp_format(char(AVAIL_DATE), 'YYYY-MM-DD') as AVAIL_DATE,
MRI_FEATURE,
REPLACED_BY,
CATEGORIES,
ABSTRACT
from QTEMP.PTFLIST
where AVAIL_DATE is not null;
end;
To run the table function, just change the input parameter to the release you are interested in:
-- description: List Available PTF, in this example for V7R6M0
select * from table(MYLIB.PTFLIST('760'));