Last active
January 10, 2024 13:58
-
-
Save forstie/cfe2636bf9b13d175b3c830aa1527165 to your computer and use it in GitHub Desktop.
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…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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