I often need to find out if a particular PTF has been applied to the IBM i I work upon. Most of the time I can use the DSPPTF command to display the PTFs, but there is one instance of IBM i I am not authorized to that command. I think the head IBM i operator is tired of receiving emails from me asking him to check if a certain PTF has been loaded and applied. I needed to find another way I could display a PTF and whether it had been applied on my own.
Fortunately there is a SQL view I can search for this information. According to the IBM documentation this view has been available since IBM i 6.1. The only servers I have access to are running 7.1 and 7.2, so I cannot vouch for whether this will work in 6.1.
The view PTF_INFO in the library contains the following columns/fields:
| Field name | Alternative name |
| LICPGM | PTF_PRODUCT_ID |
| PRODOPT | PTF_PRODUCT_OPTION |
| PRODRLS | PTF_PRODUCT_RELEASE_LEVEL |
| PRODDESC | PTF_PRODUCT_DESCRIPTION |
| PTFID | PTF_IDENTIFIER |
| PTFRLS | PTF_RELEASE_LEVEL |
| PRODLOAD | PTF_PRODUCT_LOAD |
| LOADSTAT | PTF_LOADED_STATUS |
| SAVF | PTF_SAVE_FILE |
| COVER | PTF_COVER_LETTER |
| ONORD | PTF_ON_ORDER |
| IPLACT | PTF_IPL_ACTION |
| ACTPEND | PTF_ACTION_PENDING |
| ACTREQ | PTF_ACTION_REQUIRED |
| IPLREQ | PTF_IPL_REQUIRED |
| RELEASED | PTF_IS_RELEASED |
| MINLVL | PTF_MINIMUM_LEVEL |
| MAXLVL | PTF_MAXIMUM_LEVEL |
| STATTIME | PTF_STATUS_TIMESTAMP |
| SUPERCEDE | PTF_SUPERCEDED_BY_PTF |
| CRTTIME | PTF_CREATION_TIMESTAMP |
| TRPTF | PTF_TECHNOLOGY_REFRESH_PTF |
If I am looking to see if a PTF has been installed I only need to check the following columns:
- PTFID / PTF_IDENTIFIER
- LOADSTAT / PTF_LOEADED_STATUS
I can then use the following SQL statements to check if the PTF I want has been installed on this IBM i:
SELECT PTF_IDENTIFIER,PTF_LOADED_STATUS
FROM QSYS2/PTF_INFO
WHERE PTF_IDENTIFIER = 'SI48504'
|
Rather than use the Alternative names I could just use the Field names:
SELECT PTFID,LOADSTAT
FROM QSYS2/PTF_INFO
WHERE PTFID = 'SI48504'
|
The output clearly shows that the PTF has been applied and I can use whatever was within it.
PTF_IDENTIFIER LOADSTAT
SI48504 PERMANENTLY APPLIED
|
How does this compare to using the DSPPTF command?
When I type DSPPTF and press F4 to prompt it I am presented with:
Display Program Temporary Fix (DSPPTF)
Type choices, press Enter.
Product . . . . . . . . . . . . *ALL F4 for list
PTF numbers to select . . . . . *ALL Character value, *ALL...
Release . . . . . . . . . . . . *ALL *ALL, VxRxMx
Cover letter only . . . . . . . *NO *NO, *YES
Output . . . . . . . . . . . . . * *, *PRINT, *OUTFILE
|
I can never remember or do not know what the "Product" code is. I cannot just enter the PTF number and leave the "Product" code as "*ALL". So I have to enter "*ALL" and "*ALL" and then search for the PTF I am interested in.
By using the SQL view PTF_INFO I can just search for the PTF by its number and not need the "Product" code.
You can learn more about these on the IBM website:
This article was written for IBM i 7.2, and it should work with 6.1 and 7.1 too.
This is brilliant, Simon! It's going to come in very handy for me - assuming, of course, I have access to the table in QSYS2 at the client sites where I need it. Thanks for this.
ReplyDeletefwiw I tried it on a 6.1 system and it didn't work - the table didn't exist. Then I saw some documentation that suggested that 6.1 would need a DB PTF Group (SF99601 Level 29) to enable it. Kind of ironic that the way I know I must not have that PTF group installed is because I can't see whether the PTF is installed...
I agree that is ironic. And thank you for sharing the PTF group for this in 6.1 ,
DeleteHere's a handy link to IBMs DB2 views, looks like they're creating views from APIs. They have been updating the site monthly lately. https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20-%20Services
ReplyDeleteThanks for the link. I can see some more posts coming from that list of tables.
DeleteThanks for the info included here. Something else I've done on occasion is to DSPPTF *ALL OUTPUT(*PRINT) then search the spool file, especially when I'm looking for several PTF and don't know the product ID. This SQL statement is even better.
ReplyDeleteSimon, This was present by ITech Solutions recently in their monthly IBM i System Admin newsletter. It is brilliant and thank you for bringing it to everyone's attention. I tried this about a month ago with many different SQL statements. It works in a lot of ways. If you want the SQL's I wrote to present to our user community, let me know and I'll post.
ReplyDeleteJust curious.. does anyone know what the co-requisite columns in this would be? Or is it even available. I've been dealing with PTF issues, and I could use that column in this sql search.
ReplyDeleteGreat article Simon, you rule Sir.
ReplyDeleteFabulous! Saved me hours of frustration. Cheers Simon. Thanks for sharing.
ReplyDeleteHi Simon, how can I know what ptf id corresponds to specific option in the license product and the installation status? e.g. LICPGM(5770SS1) option 39 (International Components for Unicode)?
ReplyDeleteThe best I can do is the column PTF_PRODUCT_DESCRIPTION
Delete