I use the following for Group PTF:
-- category: IBM i Services
-- description: PTF - Group PTF Currency
--
-- Derive the IBM i operating system level and then
-- determine the level of currency of PTF Groups
--
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'
ORDER BY ptf_group_level_available -
ptf_group_level_installed DESC;
With iLevel(iVersion, iRelease) AS
(
select OS_VERSION, OS_RELEASE from sysibmadm.env_sys_info
)
SELECT VARCHAR(GRP_CRNCY,26) AS "GRPCUR",
GRP_ID, VARCHAR(GRP_TITLE, 20) AS "NAME",
GRP_LVL, GRP_IBMLVL, GRP_LSTUPD,
GRP_RLS, GRP_SYSSTS
FROM iLevel, systools.group_ptf_currency P
WHERE ptf_group_release =
'R' CONCAT iVersion CONCAT iRelease concat '0'
ORDER BY ptf_group_level_available -
ptf_group_level_installed DESC;
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Gad Miron
Sent: Wednesday, November 1, 2023 1:57 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Check PTFs currency (up-to-date-ness)
________________________________
CAUTION: This email originated from outside of the PENCOR network. Do not click on any links or open attachments unless the sender is known, and the content is verified as safe.
________________________________
Hello guys
For some years I use the following SQLs to check if my Group PTFs are current
Select Row_Number() over (ORDER BY PTF_GROUP_CURRENCY DESC, PTF_GROUP_ID
ASC) as "Row #",
PTF_GROUP_CURRENCY, PTF_GROUP_ID,
PTF_GROUP_TITLE, PTF_GROUP_LEVEL_INSTALLED,
PTF_GROUP_LEVEL_AVAILABLE,PTF_GROUP_LAST_UPDATED_BY_IBM,
PTF_GROUP_RELEASE, PTF_GROUP_STATUS_ON_SYSTEM
From SYSTOOLS.GROUP_PTF_CURRENCY
with ilevel (iversion, irelease) as (
select os_version, os_release
from sysibmadm.env_sys_info
)
select Row_Number() over (Order by ptf_group_level_available - ptf_group_level_installed desc, ptf_group_id) as "Row #", p.* from ilevel, systools.group_ptf_currency p where ptf_group_release = 'R' concat iversion concat irelease concat '0'
order by "Row #"
but recently I get a confusing (to me) error:
Query cannot be run. See lower level messages
Message ID . . . . . . : CPF503E Severity . . . . . . . : 30
Message type . . . . . : Diagnostic Date sent . . . . . . : 01/11/23
Time sent . . . . . . : 07:51:19 Message . . . . : User-defined
function error on member GRPPTFCUR. Cause . . . . . : An error occurred
while invoking user-defined function HTTPGETBLOB in library SYSTOOLS. The
error occurred while invoking the associated external program or service
program B2RESTUDF: in library SYSTOOLS.D, program entry point or external
name com.ibm.db2.rest.DB2UDFWrapper.httpGetBlob, specific name
HTTPG00001. The error occurred on member GRPPTFCUR file GRPPTFCUR in
library SYSTOOLS. The error code is 1. The error codes and their meanings
follow: 1 -- The external program or service program returned SQLSTATE
38000. The text message returned from the program is:
SYSTOOLS.HTTPGETBLOB HTTPG00001 kjava.io.IOException:Server returned HTTP
response code: 403 for URL:
Any help will be gratefully received
Gad
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
