Using SQL to make a list of defective PTFs

This is something I know that System Admins should be checking, defective PTFs in their partitions. As part of the latest Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a SQL View that allows me to generate a list of defective PTFs. Previously this was only available in QMGTOOLS, alas I am not authorized to that in any of the partitions I have access to, therefore, you'll have to take my word that option 24, PTF Menu, then option 3, "Compare defective PTFs from IBM", takes you to the same information.

The View DEFECTIVE_PTF_CURRENCY is found in the SYSTOOLS library. It returns a list of defective PTFs that do not have the corrective PTF applied.

If this is the first time you have encountered this View I suggest you use the following statement to see what defective PTFs there are in your partition, and what columns of information are returned to you:

SELECT * FROM SYSTOOLS.DEFECTIVE_PTF_CURRENCY

I am only interested in a subset of the columns:

01 SELECT DEFECTIVE_PTF, 02 APAR_ID, 03 PRODUCT_ID, 04 FIXING_PTF 05 FROM SYSTOOLS.DEFECTIVE_PTF_CURRENCY

Lines 1 – 4: These are the columns I am interested in:

• DEFECTIVE_PTF:  Id of the PTF that is defective
• APAR_ID:  APAR associated with the fixing PTF
• PRODUCT_ID:  Product id
• FIXING_PTF:  PTF id of the corrective PTF. If "UNKNOWN" is returned the corrective PTF cannot be identified

It took me a while to find a partition I can use that has a defective PTF. On this partition there is one:

DEFECTIVE APAR PRODUCT FIXING _PTF _ID _ID _PTF --------- ------- ------- ------- SI79339 SE79905 5770SS1 SI83580

If I wanted to learn more about this defective PTF I would go to another PTF View, PTF_INFO.

I am only interested in three columns from PTF_INFO:

01 SELECT PTF_IDENTIFIER, 02 PTF_LOADED_STATUS, 03 PTF_TEMPORARY_APPLY_TIMESTAMP 04 FROM QSYS2.PTF_INFO 05 WHERE PTF_IDENTIFIER = 'SI79339'

Lines 1 – 3: These columns are:

• PTF_IDENTIFIER:  PTF id
• PTF_LOADED_STATUS:  Loaded status
• PTF_TEMPORARY_APPLY_TIMESTAMP:  Date and time of when the PTF was temporarily applied. If null then the PTF has not been temporarily applied

Line 5: I am only interested in the one damaged PTF which I give its id in the Where clause.

My results are:

PTF_ PTF_ LOADED PTF_TEMPORARY_ IDENTIFIER _STATUS APPLY_TIMESTAMP ---------- -------- -------------------------- SI79339 APPLIED 2023-05-23 13:28:10.000000

IMHO it would be more valuable to combine the results of the two Views into one statement:

01 SELECT A.DEFECTIVE_PTF, 02 A.APAR_ID, 03 A.PRODUCT_ID, 04 B.PTF_LOADED_STATUS, 05 B.PTF_TEMPORARY_APPLY_TIMESTAMP, 06 A.FIXING_PTF 07 FROM SYSTOOLS.DEFECTIVE_PTF_CURRENCY A 08 JOIN QSYS2.PTF_INFO B 09 ON A.DEFECTIVE_PTF = B.PTF_IDENTIFIER

Lines 1 – 6: Columns starting with "A" are from DEFECTIVE_PTF_CURRENCY. Those prefixed with "B" are from PTF_INFO.

Lines 7 – 9: I have just a regular join between DEFECTIVE_PTF_CURRENCY and PTF_INFO using the defective PTF id to join the two.

The results are:

PTF_ DEFECTIVE APAR PRODUCT LOADED PTF_TEMPORARY_ FIXING _PTF _ID _ID _STATUS APPLY_TIMESTAMP _PTF -------- ------- ------- ------- -------------------------- ------- SI79339 SE79905 5770SS1 APPLIED 2023-05-23 13:28:10.000000 SI83580

I will tell my lead Sys Admin to run this on a regular basis to determine if there are any remedial PTFs we should be applying to replace the defective ones.

 

You can learn more about the DEFECTIVE_PTF_CURRENCY SQL View from the IBM website here.

 

This article was written for IBM i 7.5 TR2 and 7.4 TR8.