-
-
Save forstie/cfe2636bf9b13d175b3c830aa1527165 to your computer and use it in GitHub Desktop.
| -- | |
| -- Subject: Is this IBM i at risk of a known defective PTF? | |
| -- Author: Scott Forstie | |
| -- Date : February, 2023 | |
| -- Features Used : This Gist uses qsys2.http_get, a defective PTF service from IBM, CTEs, sysibmadm.env_sys_info, string manipulation BIFs, SYSTOOLS.split | |
| -- | |
| -- Notes: | |
| -- =============================================== | |
| -- 1) The data returned here is the same data you would find when using | |
| -- Go QMGTOOLS/MG option 24 (PTF Menu) --> option 3 (Compare DEFECTIVE PTFs from IBM) | |
| -- | |
| -- 2) If you've never used the QSYS2-based HTTP functions, you might have a decision to make and one time setup. | |
| -- The QSYS2-based HTTP functions rely upon a keystore, where as the SYSTOOLS-based HTTP functions implicitely used the keystore leveraged by Java. | |
| -- The following page in IBM Documentation includes lots of detail about how to use the QSYS2-based HTTP functions: | |
| -- https://www.ibm.com/docs/en/i/7.5?topic=programming-http-functions-overview | |
| -- | |
| -- There's even a section named "SSL considerations" that includes a working setup script. Please read this page. | |
| -- | |
| stop; | |
| -- | |
| -- Show me every defective PTF for IBM i 7.4, and the corrective PTF number (super raw) | |
| -- | |
| values qsys2.HTTP_GET('https://public.dhe.ibm.com/services/us/igsc/has/R740DEFECT.txt', ''); | |
| stop; | |
| -- | |
| -- Show me every defective PTF for IBM i 7.4, and the corrective PTF number (raw) | |
| -- | |
| with raw_webpage (webpage_text) as ( | |
| values qsys2.HTTP_GET('https://public.dhe.ibm.com/services/us/igsc/has/R740DEFECT.txt', '') | |
| ), | |
| def_ptfs (agg_list) as ( | |
| select | |
| cast( | |
| substring(webpage_text, posstr(webpage_text, '----------') + 11, 30000) as varchar(30000) | |
| ccsid 37) as URL_STRING | |
| from raw_webpage | |
| ) | |
| select * | |
| from def_ptfs; | |
| stop; | |
| -- | |
| -- Show me every defective PTF for IBM i 7.4, and the corrective PTF number (raw-ish) | |
| -- | |
| with raw_webpage (webpage_text) as ( | |
| values qsys2.HTTP_GET('https://public.dhe.ibm.com/services/us/igsc/has/R740DEFECT.txt', '') | |
| ), | |
| def_ptfs (agg_list) as ( | |
| select | |
| cast( | |
| substring(webpage_text, posstr(webpage_text, '----------') + 11, 30000) as varchar(30000) | |
| ccsid 37) as URL_STRING | |
| from raw_webpage | |
| ) | |
| select x.* | |
| from def_ptfs, table ( | |
| SYSTOOLS.split(agg_list, x'25') | |
| ) as X where length(rtrim(element)) > 0; | |
| stop; | |
| -- | |
| -- Show me every defective PTF for IBM i 7.4, and the corrective PTF number (refined) | |
| -- | |
| with raw_webpage (webpage_text) as ( | |
| values qsys2.HTTP_GET('https://public.dhe.ibm.com/services/us/igsc/has/R740DEFECT.txt', '') | |
| ), | |
| def_ptfs (agg_list) as ( | |
| select | |
| cast( | |
| substring(webpage_text, posstr(webpage_text, '----------') + 11, 30000) as varchar(30000) | |
| ccsid 37) as URL_STRING | |
| from raw_webpage | |
| ) | |
| select cast('IBM i 7.4' as varchar(9)) as Operating_System_Level, | |
| substring(x.element, 1, 7) as Bad_PTF, substring(x.element, 10, 7) as APAR, | |
| substring(x.element, 19, 7) as LICPGM, | |
| coalesce(substring(x.element, 28, 7), 'MISSING') as Fixing_PTF | |
| from def_ptfs, table ( | |
| SYSTOOLS.split(agg_list, x'25') | |
| ) as X where length(rtrim(element)) > 0; | |
| stop; | |
| -- | |
| -- What verion of the IBM i operating system are we using? | |
| -- | |
| With iLevel(iVersion, iRelease, VRM) AS | |
| ( | |
| select OS_VERSION, OS_RELEASE, | |
| 'R' concat OS_VERSION concat OS_RELEASE concat '0' | |
| from sysibmadm.env_sys_info | |
| ) | |
| select * from iLevel; | |
| stop; | |
| -- | |
| -- Show me every defective PTF for this IBM i, and the corrective PTF number (super fine) | |
| -- | |
| with iLevel (defective_PTF_URL) as ( | |
| select 'https://public.dhe.ibm.com/services/us/igsc/has/R' concat OS_VERSION concat | |
| OS_RELEASE concat '0DEFECT.txt' | |
| from sysibmadm.env_sys_info | |
| ), | |
| raw_webpage (webpage_text) as ( | |
| select qsys2.HTTP_GET(defective_PTF_URL, '') | |
| from iLevel | |
| ), | |
| def_ptfs (agg_list) as ( | |
| select | |
| cast( | |
| substring(webpage_text, posstr(webpage_text, '----------') + 11, 30000) as varchar(30000) | |
| ccsid 37) as URL_STRING | |
| from raw_webpage | |
| ) | |
| select 'V' concat OS_VERSION concat 'R' concat OS_RELEASE as Operating_System_Level, | |
| char(substring(x.element, 1, 7), 7) as Bad_PTF, | |
| char(substring(x.element, 10, 7), 7) as APAR, | |
| char(substring(x.element, 19, 7), 7) as LICPGM, | |
| char(coalesce(substring(x.element, 29, 7), 'MISSING'), 7) as Fixing_PTF | |
| from sysibmadm.env_sys_info, def_ptfs, table ( | |
| SYSTOOLS.split(agg_list, x'25') | |
| ) as X where length(rtrim(element)) > 0; | |
| stop; | |
| -- | |
| -- Show me every defective PTF for this IBM i, and the corrective PTF number (ultra fine) | |
| -- Where this IBM i has the defective PTF applied, but does NOT have the corrective PTF applied | |
| -- | |
| with iLevel (defective_PTF_URL) as ( | |
| select 'https://public.dhe.ibm.com/services/us/igsc/has/R' concat OS_VERSION concat | |
| OS_RELEASE concat '0DEFECT.txt' | |
| from sysibmadm.env_sys_info | |
| ), | |
| raw_webpage (webpage_text) as ( | |
| select qsys2.HTTP_GET(defective_PTF_URL, '') | |
| from iLevel | |
| ), | |
| def_ptfs (agg_list) as ( | |
| select | |
| cast( | |
| substring(webpage_text, posstr(webpage_text, '----------') + 11, 30000) as varchar(30000) | |
| ccsid 37) as URL_STRING | |
| from raw_webpage | |
| ), | |
| defective_ptfs(partition, host, serial_no, OS_level, Bad_PTF, APAR, LICPGM, Fixing_PTF) as ( | |
| select PARTITION_NAME, b.host_name, serial_number, | |
| 'V' concat OS_VERSION concat 'R' concat OS_RELEASE, | |
| char(substring(element, 1, 7), 7), | |
| char(substring(element, 10, 7), 7), | |
| char(substring(element, 19, 7), 7), | |
| case when length(rtrim(substring(char(element,100), 29, 7))) > 0 | |
| then rtrim(char(substring(char(element,100), 29, 7))) | |
| else 'UNKNOWN' end as Fixing_PTF | |
| from qsys2.system_status_info_basic a, sysibmadm.env_sys_info b, def_ptfs, table ( | |
| SYSTOOLS.split(agg_list, x'25') | |
| ) where length(rtrim(element)) > 0) | |
| select * from defective_ptfs | |
| where | |
| -- The Bad PTF is on in any form | |
| bad_PTF in (select PTF_IDENTIFIER from qsys2.ptf_info where PTF_PRODUCT_ID = LICPGM) | |
| and | |
| -- The corrective PTF is not applied | |
| Fixing_PTF not in (select PTF_IDENTIFIER from qsys2.ptf_info where PTF_PRODUCT_ID = LICPGM and | |
| (PTF_LOADED_STATUS like '%APPLIED%' or PTF_LOADED_STATUS like '%SUPERSEDE%')); | |
| stop; | |
| create or replace view systools.defective_ptf_currency for system name def_ptfs | |
| (partition_name for part_name, host_name, serial_number for serial, os_release_level for os_release, defective_ptf for def_ptf, apar_id, product_id for licpgm, fixing_ptf) | |
| as | |
| -- | |
| -- Show me every defective PTF for this IBM i, and the corrective PTF number (ultra fine) | |
| -- Where this IBM i has the defective PTF applied, but does NOT have the corrective PTF applied | |
| -- | |
| with iLevel (defective_PTF_URL) as ( | |
| select 'https://public.dhe.ibm.com/services/us/igsc/has/R' concat OS_VERSION concat | |
| OS_RELEASE concat '0DEFECT.txt' | |
| from sysibmadm.env_sys_info | |
| ), | |
| raw_webpage (webpage_text) as ( | |
| select qsys2.HTTP_GET(defective_PTF_URL, '') | |
| from iLevel | |
| ), | |
| def_ptfs (agg_list) as ( | |
| select | |
| cast( | |
| substring(webpage_text, posstr(webpage_text, '----------') + 11, 30000) as varchar(30000) | |
| ccsid 37) as URL_STRING | |
| from raw_webpage | |
| ), | |
| defective_ptfs(partition, host, serial_no, OS_level, Defective_PTF, APAR, LICPGM, Fixing_PTF) as ( | |
| select PARTITION_NAME, b.host_name, serial_number, | |
| cast('V' concat OS_VERSION concat 'R' concat OS_RELEASE as varchar(10)), | |
| char(substring(element, 1, 7), 7), | |
| char(substring(element, 10, 7), 7), | |
| char(substring(element, 19, 7), 7), | |
| cast(case when length(rtrim(substring(char(element,100), 29, 7))) > 0 | |
| then rtrim(char(substring(char(element,100), 29, 7))) | |
| else 'UNKNOWN' end as char(7)) as Fixing_PTF | |
| from qsys2.system_status_info_basic a, sysibmadm.env_sys_info b, def_ptfs, table ( | |
| SYSTOOLS.split(agg_list, x'25') | |
| ) where length(rtrim(element)) > 0) | |
| select * from defective_ptfs | |
| where | |
| -- The Bad PTF is on in any form | |
| Defective_PTF in (select PTF_IDENTIFIER from qsys2.ptf_info | |
| where PTF_PRODUCT_ID = LICPGM and PTF_LOADED_STATUS not like '%SUPER_EDE%') | |
| and | |
| -- The corrective PTF is not applied | |
| Fixing_PTF not in (select PTF_IDENTIFIER from qsys2.ptf_info where PTF_PRODUCT_ID = LICPGM and | |
| (PTF_LOADED_STATUS like '%APPLIED%' or PTF_LOADED_STATUS like '%SUPERSEDE%')); | |
| stop; | |
| select * | |
| from systools.defective_ptf_currency; |
Thank you :)
You're welcome. Thanks for commenting.
Hi Scott,
Is it possible to use values with the input of an IFS file rather than using HTTP_GET? My customer doesn’t allow web access from their LPARs so I’m looking at manually downloading the txt file in to the IFS and using that instead.
Hello robinsq,
With SQL, so much is possible. We have IFS_READ services and then the structure of the data would need to be pieced out into relational data.
Alternatively, ACS can be used to establish a proxy to the internet and then the SQL in this and similar Gists should run on that IBM i that doesn't allow web access.
IBM in the ACS getting started html file and here https://www.ibm.com/docs/en/i/7.4?topic=xj1-open-source-package-management-interface-proxy-support
9.1.39 HTTPPROXYUI
/PLUGIN=httpproxyui
HTTPPROXYUI will launch a secured HTTP proxy that enables connectivity from an IBM i to the internet via the computer running this proxy. Access to the internet is enabled from any SSH Terminal session that enters the commands provided by the HTTPPROXYUI plug-in.
Note: The SSH Terminal session must be running on the same computer as this proxy.
Alan Seiden also has a page https://www.seidengroup.com/php-documentation/offline-installation-of-communityplus-php/
Thanks Scott,
This is not going to work for me in this environment as the ACS session I’m running is managed by CyberArk and has no internet access. This is a bank so everything is locked down.
I have manual pasted the txt file in to values and that works but an IFS file would be more flexible.
Glenn
Updated on 10/29/2025 to fix an "off by 1 problem".
Also, note that you likely do not need to use this gist anymore, because this view is now shipped by Db2 for i.
https://www.ibm.com/support/pages/systoolsdefectiveptfcurrency
But, this gist is still valuable as a building block, learning SQL gist.****
@forstie Is this and the systools defective ptf currency still working? I had this in place checking 70 LPARs and I've noticed that it no longer returns any results. It was working for many many months. When I try to run items from the gist I get this error:
or this:
Hi!
Which IBM i operating system release are you using?
We switched this service to use the SYSTOOLS-based HTTP rest services to avoid this sort of failure.
Hi! Which IBM i operating system release are you using? We switched this service to use the SYSTOOLS-based HTTP rest services to avoid this sort of failure.
The first error was from a 7.5 system and the second from a 7.4. The new systools view does not return any defective PTFs on either system and when I try to use the gist I get these errors. I find it hard to believe I don't have systems with defective PTFs since when I first turned this on almost every system had one or more.
I was just trying to find a way to test to get some kind of validation.
If you have these PTFs applied, the defective_currency service shipped by Db2 for i should not be using AXISC.
7.4 - 5770SS1 - PTF SI83237
7.5 - 5770SS1 - PTF SI83236
The other way to go is to fix your keystore issue and keep using the AXISC-based solution.
For reference, here is the SYSTOOLS-based source code.
CREATE OR REPLACE VIEW systools.defective_ptf_currency FOR SYSTEM NAME defptf_cur (
partition_name FOR COLUMN part_name, host_name, serial_number FOR COLUMN serial,
os_release_level FOR COLUMN os_release, defective_ptf FOR COLUMN def_ptf, apar_id,
product_id FOR COLUMN licpgm, fixing_ptf) AS
WITH ilevel (defective_ptf_url) AS (
SELECT 'https://public.dhe.ibm.com/services/us/igsc/has/R' CONCAT os_version CONCAT
os_release CONCAT '0DEFECT.txt'
FROM sysibmadm.env_sys_info
),
raw_webpage (webpage_text) AS (
SELECT systools.httpgetclob(defective_ptf_url, '')
FROM ilevel
),
def_ptfs (agg_list) AS (
SELECT
CAST(
SUBSTRING(webpage_text, POSSTR(webpage_text, '----------') + 11, 30000) AS VARCHAR(
30000) CCSID 37) AS url_string
FROM raw_webpage
),
defective_ptfs (partition_name, host_name, serial_number, os_release_level, defective_ptf,
apar_id, product_id, fixing_ptf) AS (
SELECT COALESCE(a.partition_name, ''), COALESCE(b.host_name, ''), a.serial_number, CAST(
COALESCE('V' CONCAT b.os_version CONCAT 'R' CONCAT b.os_release, '') AS VARCHAR(
10)), CHAR(COALESCE(SUBSTRING(c.element, 1, 7), ''), 7), VARCHAR(
COALESCE(RTRIM(SUBSTRING(c.element, 10, 8)), ''), 8), CHAR(
COALESCE(SUBSTRING(c.element, 20, 7), ''), 7),
CHAR(
COALESCE(
CASE
WHEN
SUBSTRING(CHAR(c.element, 40), 29, 7) <> ''
THEN SUBSTRING(CHAR(c.element, 40), 29, 7)
ELSE 'UNKNOWN'
END, ''), 7) AS fixing_ptf
FROM qsys2.system_status_info_basic a, sysibmadm.env_sys_info b, def_ptfs, TABLE (
systools.split(agg_list, X'25')
) c
WHERE LENGTH(RTRIM(c.element)) > 0
)
SELECT *
FROM defective_ptfs
WHERE
-- The Bad PTF is on the system and not superseded
defective_ptf IN (SELECT ptf_identifier
FROM qsys2.ptf_info
WHERE ptf_product_id = product_id AND ptf_loaded_status <> 'SUPERSEDED') AND
-- The corrective PTF is not applied
fixing_ptf NOT IN (
SELECT ptf_identifier
FROM qsys2.ptf_info
WHERE ptf_product_id = product_id AND (ptf_loaded_status LIKE '%APPLIED%' OR
ptf_loaded_status = 'SUPERSEDED'))
RCDFMT defptf_cur;
That worked. Thank you!
Awesome, thanks for the follow-up.
Hi Scott.
This is useful as it gives me "at glance" the groups that I need to download.
normally in the "WHERE" condition I put this: WHERE PTF_GROUP_CURRENCY like '%UPDAT%' .
The point is, that once I've installed new CUM+Groups+Corrections, I perform 2 controls:
Normally to catch this information I use the following link (it's for 7.5, but it will be very useful also for 7.3):
https://www.ibm.com/support/pages/node/6580943 (it's the Summary of HIPER and Defective PTFs).
In the previous document I search for the list of "Service recommendations", and then move this list in my systems in order to find the "orphans" Hipers that I need to have a look.
Don't know if this is paranoid... :)
Just an example of the list that normally I load in systems and I verify for everyone if PTFs is not present.
Thanks for your answer