Skip to content

Instantly share code, notes, and snippets.

Created June 3, 2020 22:39
Show Gist options
  • Save forstie/045e9918a442dc9913c4a735c6e4a38e to your computer and use it in GitHub Desktop.
Save forstie/045e9918a442dc9913c4a735c6e4a38e to your computer and use it in GitHub Desktop.
In this Gist, there are two queries to tell you 1) If there are IBM i HIPER PTF Group levels missing from your IBM i and 2) Which IBM i HIPER PTFs are not installed? #SQLcandoit
-- Am I current on the IBM i Group Hiper PTF GROUP level?
With iLevel(iVersion, iRelease) AS
select OS_VERSION, OS_RELEASE from sysibmadm.env_sys_info
FROM iLevel, systools.group_ptf_currency P
WHERE ptf_group_release =
'R' CONCAT iVersion CONCAT iRelease concat '0'
and upper(ptf_group_title) like '%GROUP HIPER%'
ORDER BY ptf_group_level_available -
ptf_group_level_installed DESC;
-- Show me the HIPER PTFs that I'm missing
select ptf_group_name, ptf_status, ptf_product_id, ptf_identifier,
apar_name, ptf_included_in_group_date, ptf_cum_package, ptf_product_description,
ptf_release_level, ptf_product_load, ptf_loaded_status, ptf_save_file,
ptf_cover_letter, ptf_on_order, ptf_ipl_action, ptf_action_pending,
ptf_action_required, ptf_ipl_required, ptf_is_released, ptf_minimum_level,
ptf_maximum_level, ptf_status_timestamp, ptf_superseded_by_ptf,
from systools.group_ptf_details
where upper(ptf_group_description) like '%GROUP HIPER%'
and ptf_status <> 'PTF APPLIED'
order by ptf_identifier;
Copy link

Hi Scott.

There is a problem with the second SQL - at least on our IBM i partition running 7.3 DB PTF level 19:

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable PTF_SUPERSEDED_BY_PTF not found.

The column returned by SYSTOOLS.GROUP_PTF_DETAILS is called PTF_SUPERCEDED_BY_PTF with a "C", so there is apparently a spelling mistake here somewhere...
(I'm not fluent in English / American, so I can't say which is correct - the view or your code)

Best regards,

Copy link

forstie commented Jun 4, 2020

We had a strong request to fix the column name, as the previous name was misspelled.
So, it depends upon your code level to determine which column name to use. :)
I guess the safe path would be for me to have the example use SELECT *, but that seems too raw to my liking.
Thanks for pointing this out Christian.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment