PTFs should help, not hurt. That's the credo, goal, and expectation. But... sometimes things go the wrong way. This gist shows how to use SQL to consume an IBM provided resource, compare what you have locally and most importantly, tell you if you are exposed to a known defective PTF. Please use this gist to gain skills with SQL, but more importa…

Defective PTF Currency.sql

--
-- 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, 28, 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), 28, 7))) > 0 
              then rtrim(char(substring(char(element,100), 28, 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), 28, 7))) > 0 
              then rtrim(char(substring(char(element,100), 28, 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;

Hi Scott, just a silly correction in your last "big" and GREAT sql stmt.
In row 207 there is a closed parenthesis more ")" to be removed.
Always in row 207 I've added another condition "and PTF_LOADED_STATUS not like '%SUPERCEDE%'"
In row 211 I've added an additional condition "or PTF_LOADED_STATUS like '%SUPERSEDE%'"

### Here the last big script (just copy/paste)

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
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), 28, 7))) > 0
then rtrim(char(substring(char(element,100), 28, 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 '%SUPERSEDE%' and PTF_LOADED_STATUS not like '%SUPERCEDE%')
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%' or PTF_LOADED_STATUS like '%SUPERCEDE%'));

Thanks dad71.
Oh, perfection is so elusive.
I changed line 271 to work with either SUPERCEDE or SUPERSEDE.

    where PTF_PRODUCT_ID = LICPGM and PTF_LOADED_STATUS not like '%SUPER_EDE%')

Thanks a lot Scott! Great as usual :)
May I ask you a thing?
Is it feasible doing the same check also on Hipers?
I mean if there would be a similar page with Hipers per OS version, it would be great!
What do you think?

Hi again @dad71.
Wouldn't this suffice?

-- Are we current with the IBM i PTF GROUP for Hiper PTFs?
select *
from systools.group_ptf_currency
where upper(ptf_group_title) like '%HIPER%';

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:

• first one on the defective ptfs (that is the script you provided in this topic... and it's great)
• another one is on new HIPERs detail... and this comparison gives me an overview if I'm missing particular PTFs that "it'd be better" having installed.

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.

---

       DATE        APAR   VRM PTF/LIC FIX   LIC PGM      CUM PKG

77. 10 Mar 2023 MA50081 750 MF70799 5770999
78. 02 Mar 2023 SE79163 750 SI82509 5770SS1
79. 24 Feb 2023 MA50069 750 MF70751 5770999
80. 21 Feb 2023 MA50059 750 MF70757 5770999
81. 10 Feb 2023 MA50049 750 MF70715 5770999
82. 03 Feb 2023 SE79251 750 SI82617 5770SS1
83. 01 Feb 2023 MA50026 750 MF70673 5770999
84. 01 Feb 2023 MA50027 750 MF70672 5770999
85. 01 Feb 2023 SE79238 750 SI82519 5770SS1
86. 01 Feb 2023 MA50028 750 MF70689 5770999
87. 27 Jan 2023 MA50030 750 MF70675 5770999
88. 27 Jan 2023 MA50021 750 MF70662 5770999
89. 26 Jan 2023 SE79143 750 SI82517 5770SS1
90. 24 Jan 2023 MA50031 750 MF70676 5770999
91. 18 Jan 2023 MA50014 750 MF70651 5770999
92. 13 Jan 2023 SE78750 750 SI81580 5770SS1
93. 11 Jan 2023 SE79096 750 SI82396 5770TC1
94. 26 Dec 2022 MA49927 750 MF70450 5770999
95. 26 Dec 2022 MA49976 750 MF70564 5770999
96. 15 Dec 2022 MA49969 750 MF70545 5770999
97. 15 Dec 2022 MA49967 750 MF70540 5770999
98. 15 Dec 2022 MA49958 750 MF70529 5770999
99. 14 Dec 2022 SE78740 750 SI81554 5770SS1
100. 12 Dec 2022 MA49952 750 MF70538 5770999
101. 12 Dec 2022 MA49963 750 MF70530 5770999
102. 01 Dec 2022 SE78676 750 SI81438 5770SS1
103. 30 Nov 2022 MA49951 750 MF70511 5770999
104. 29 Nov 2022 SE78279 750 SI80776 5770TS1
105. 29 Nov 2022 SE78918 750 SI81867 5770SS1
106. 28 Nov 2022 SE78813 750 SI81706 5770SS1
107. 22 Nov 2022 SE78893 750 SI81854 5770SS1
108. 22 Nov 2022 SE78771 750 SI81800 5770SS1
109. 22 Nov 2022 SE78907 750 SI81841 5770SS1
110. 16 Nov 2022 MA49947 750 MF70501 5770999
111. 09 Nov 2022 SE78809 750 SI81698 5770SS1
112. 04 Nov 2022 SE78658 750 SI81417 5770DG1
113. 01 Nov 2022 SE78587 750 SI81292 5733SC1
114. 26 Oct 2022 MA49907 750 MF70407 5770999
115. 26 Oct 2022 SE78586 750 SI81457 5770SS1
116. 25 Oct 2022 MA49903 750 MF70403 5770999
117. 17 Oct 2022 MA49883 750 MF70401 5770999 C2321750
118. 17 Oct 2022 MA49797 750 MF70160 5770999 C2321750
119. 04 Oct 2022 MA49852 750 MF70298 5770999 C2321750
120. 03 Oct 2022 MA49869 750 MF70335 5770999 C2321750
121. 03 Oct 2022 SE78577 750 SI81289 5770SS1

Thanks for your answer

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