RE: Product ID for PTFs -- MIDRANGE-L

Actually, SQL 1 I got from Scott Forstie when I was working on the "select row #" stmt and SQL 2 I kind of built out a simpler version.

Very Respectfully,
Michael Mayer
IBM i on Power System Admin.
IT Operations.
The Florida Bar
Tallahassee, Florida 32399-2300
mmayer@xxxxxxxxxxxxxx
https://www.floridabar.org
Office: 850.561.5761
Cell: 518.641.8906

1. RE: Product ID for PTFs (Rob Berendt)

----------------------------------------------------------------------

message: 1
date: Thu, 10 Mar 2022 17:08:05 +0000
from: Rob Berendt <rob@xxxxxxxxx>
subject: RE: Product ID for PTFs

I believe these are copied character for character from the examples in Run SQL Scripts

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mayer, Michael via MIDRANGE-L
Sent: Thursday, March 10, 2022 12:05 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Mayer, Michael <MMayer@xxxxxxxxxxxxxx>
Subject: Product ID for PTFs

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

To Rob's point a bit further, " .... Basically you will never get the ability to check individual ptf currency with IBM the way you can check group PTF currency because they will never put the listing of PTF's available at IBM in a xml document consumable by a individual ptf currency service. Reasoning being: "Our platform strategy is for clients to manage IBM i by PTF Groups, not individual PTFs. .... "

On the front page of my IBM i Reference blogspot page are 2 SQL's that can be used to check PTF Group's on your system vs PTF's Group's available FOR your system.

Note: These will run in green screen STRSQL or ACS Run SQL Scripts.
The CCSID of the user profile must be set to 37 on the green screen.
These work on a V7R2 system and should run on V7R3 / R4.

SQL 1:
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 #"

or

SQL 2:
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

Very Respectfully,
Michael Mayer
IBM i on Power System Admin.
IT Operations.
The Florida Bar
Tallahassee, Florida 32399-2300
mmayer@xxxxxxxxxxxxxx
https://www.floridabar.org
Office: 850.561.5761
Cell: 518.641.8906

IBM i Personal Blog Page: https://ibmireference.blogspot.com/

-------------------------------------------------------------------------------------------------

________________________________
Please note: Florida has very broad public records laws. Many written communications to or from The Florida Bar regarding Bar business may be considered public records, which must be made available to anyone upon request. Your e-mail communications may therefore be subject to public disclosure.