Track Group PTFs with SQL

I have mentioned the SQL View GROUP_PTF_INFO a couple of times in passing in other posts on this website, I have not explained what this View gives us.

A PTF Group is a bundle of related PTFs which are designed and managed as a single group. This simplifies PTF updates as all the necessary individual PTFs for a specific group are applied together. Examples of PTF groups are:

• SF99760 for cumulative PTFs
• SF99960 for Db2 for i
• SF99962 for the IBM HTTP server

There are several ways I can check on the PTF Groups on my IBM i partition:

• DSPPTFGRP:  Display PTF Group command
• WRKPTFGRP:  Work with PTF Group command
• GROUP_PTF_INFO:  SQL View

In all of my examples I am going to use the PTF Group for Db2 for i, SF99960, on a partition that is running IBM i 7.6 .

When I use the commands DSPPTFGRP and WRKPTFGRP I am frustrated that I cannot get an output file of the information it retrieves. With WRKPTFGRP I can only display the results:

WRKPTFGRP PTFGRP(SF99960)

Shows that there have been two releases of this group PTF, and I can select the PTF group to display more information about it:

Work with PTF Groups System: XXXXXXXX Type options, press Enter. 1=Order 4=Delete 5=Display 6=Print 8=Display special handling PTFs 9=Display related PTF groups 10=Display PTF apply information Opt PTF Group Level Status __ SF99960 2 Installed __ SF99960 1 Installed

With the DSPGRPPTF I have the option for either displaying the results on the screen or to generate a spool file. For example:

DSPPTFGRP PTFGRP(SF99960) OUTPUT(*PRINT)

The above will create a QSYSPRT spool file to contain the results.

Neither of these are useful if I want to do something with the results returned by the commands. This where the View GROUP_PTF_INFO is better.

If I wanted to return the summary information returned by the both of the commands I could use the following SQL statement:

01 SELECT PTF_GROUP_NAME AS "Group", 02 PTF_GROUP_LEVEL AS "Level", 03 PTF_GROUP_APPLY_TIMESTAMP AS "Applied", 04 PTF_GROUP_STATUS AS "Status", 05 PTF_GROUP_TARGET_RELEASE AS "Release" 06 PTF_GROUP_APPLY_TIMESTAMP AS "When applied" 07 FROM QSYS2.GROUP_PTF_INFO 08 WHERE PTF_GROUP_NAME = 'SF99960' 09 ORDER BY PTF_GROUP_APPLY_TIMESTAMP DESC ;

Lines 1 – 6: Rather than use the View's column names for the column headings, I have given my own.

Line 8: I am only interested in the Group PTFs for Db2 for i.

Line 9: This is the new column that was added in IBM i 7.6 TR1 and 7.5 TR7, it is the timestamp of when the most recent of the PTFs that make up this group was applied. By sorting in descending order, the most recent group will come first.

My results are:

Group Level Applied Release Release When applied ------- ----- ------------------- --------- ------- ------------------- SF99960 2 2025-12-18 08:48:57 INSTALLED V7R6M0 2025-12-18 08:48:57 SF99960 1 2025-04-26 19:37:23 INSTALLED V7R6M0 2025-04-26 19:37:23

If I wanted to drill down and list the individual PTFs that make up this group I would use the PTF_GROUP_DETAIL and PTF_INFO Views. I decided to use a Common Table Expression, CTE to make the statement easier to understand.

01 WITH T0 (GROUP,LEVEL,APPLIED,STATUS,RELEASE,PTF_ID) AS 02 (SELECT A.PTF_GROUP_NAME,A.PTF_GROUP_LEVEL,A.PTF_GROUP_APPLY_TIMESTAMP, 03 A.PTF_GROUP_STATUS,A.PTF_GROUP_TARGET_RELEASE, 04 B.PTF_IDENTIFIER 05 FROM QSYS2.GROUP_PTF_INFO A, SYSTOOLS.GROUP_PTF_DETAILS B 06 WHERE A.PTF_GROUP_NAME = B.PTF_GROUP_NAME 07 AND A.PTF_GROUP_NAME = 'SF99960' 08 AND A.PTF_GROUP_LEVEL = 2), 09 T1 (GROUP,LEVEL,APPLIED,STATUS,RELEASE,PTF_ID,PTF_APPLIED) AS 10 (SELECT T0.*,C.PTF_TEMPORARY_APPLY_TIMESTAMP 11 FROM T0 LEFT OUTER JOIN QSYS2.PTF_INFO C 12 ON T0.PTF_ID = C.PTF_IDENTIFIER 13 WHERE DATE(APPLIED) = DATE(C.PTF_TEMPORARY_APPLY_TIMESTAMP)) 14 SELECT * FROM T1 15 ORDER BY PTF_ID ;

Lines 1 – 8: The first part of the CTE extracts information from GROUP_PTF_INFO and joins it to GROUP_PTF_DETAILS to get the individual PTF ids.

Line 1: All CTEs start with the word "WITH" followed by a temporary file name. I like to keep my simple so I call it T0. What follows is a list of the columns that will be in the temporary table T0.

Lines 2 – 8: This is the SQL statement that makes the results for T0.

Lines 2 – 4: The columns I want in T0 are:

• PTF group name
• PTF group level
• The timestamp the PTF group was applied
• The status, in other words does the system consider the PTF group installed or not
• Operating system release
• Individual PTF id, which comes the GROUP_PTF_DETAILS

Line 8: This line ends with a comma ( , ) as there is another definition of a temporary file that follows.

Lines 9 – 13: This part of the CTE defines a second temporary table, T1, that is based upon the temporary table T0 and the View PTF_INFO.

Line 9: T1 contains one additional column, when compared to T0, the PTF applied timestamp for the individual PTFs.

The problem I have is that neither GROUP_PTF_DETAILS nor PTF_INFO contain the Group PTF level, therefore, I cannot say without certainty which PTFs belong to a particular Group PTF level. The only way I could think to do this is to match the date from the Group PTF application timestamp to the date from the individual temporary apply timestamp from PTF_INFO. It is not perfect, but is the best I could come up with.

Line 10: All the columns from T0 and the individual PTF's temporary apply timestamp.

Line 11: I need to perform a LEFT OUTER JOIN to match rows in PTF_INFO to the rows in T0.

Line 12: T0 and PTF_INFO are joined on the individual PTF ids.

Line 13: This is my not perfect way of only getting rows for the second PTF group level for SF99960. I am matching the date from T0 application timestamp to PTF_INFO temporary apply timestamp's date. If you know of a better method of identifying which rows in PTF_INFO are for SF99960 level 2 please let me know.

Line 14 and 15: This is the final part of the statement that lists all the columns to display, and the order of the results.

The first results from the above CTE is:

GROUP LEVEL APPLIED STATUS RELEASE PTF_ID PTF_APPLIED ------- ----- ------------------- --------- ------- ------- -------------------------- SF99960 2 2025-12-18 08:48:57 INSTALLED V7R6M0 MJ04828 2025-12-18 08:41:57.000000 SF99960 2 2025-12-18 08:48:57 INSTALLED V7R6M0 MJ04892 2025-12-18 08:41:48.000000 SF99960 2 2025-12-18 08:48:57 INSTALLED V7R6M0 MJ04893 2025-12-18 08:41:48.000000 SF99960 2 2025-12-18 08:48:57 INSTALLED V7R6M0 MJ04938 2025-12-18 08:41:57.000000 SF99960 2 2025-12-18 08:48:57 INSTALLED V7R6M0 MJ04970 2025-12-18 08:41:57.000000

While the statement is not perfect it does give the results I wanted.

 

You can learn more about the GROUP_PTF_INFO command from the IBM website here.

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.