Wednesday, July 26, 2023

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.