Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
)
SELECT P.*
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,
ptf_creation_timestamp
from systools.group_ptf_details
where upper(ptf_group_description) like '%GROUP HIPER%'
and ptf_status <> 'PTF APPLIED'
order by ptf_identifier;
@chrjorgensen

This comment has been minimized.

Copy link

@chrjorgensen chrjorgensen commented Jun 4, 2020

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,
Christian

@forstie

This comment has been minimized.

Copy link
Owner Author

@forstie forstie commented Jun 4, 2020

Hi,
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.
Best...Scott

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