04 - System Administration (EN)04g - System Admin miscellanea

PTFs and PTF Groups: Are you up to date? Let’s check via SQL

Last Updated on 15 December 2019 by Roberto De Pedrini

Markone’s suggestions n. 5

Check the update status of IBM i via SQL

Which PTFs are installed on my IBM i system? What is the level of the last cumulative installed?
Are there any new updates available to install? An
d is the firmware updated?
These questions can be answered with just a few SQL statements, thanks to the views that IBM makes available through DB2 for i service.

In the “PTF” category there are these 5 views:

- >> SUMMARY DB2 for i SERVICE: PTF CATEGORY

select SERVICE_NAME "Service Name", SERVICE_SCHEMA_NAME "Library", SYSTEM_OBJECT_NAME "System Name", SQL_OBJECT_TYPE "Type", 
  replace (substr (EXAMPLE, 1, position (x'0D ', EXAMPLE) -1),' - Description: ',' ') "Short description"
  from QSYS2 / SERVICES_INFO
  where SERVICE_CATEGORY = 'PTF'
  order by SERVICE_NAME;

Of which we find the documentation at these links:

What’s installed on my IBM i?

For a summary view, first of all we need to check the PTF groups: that is the equivalent of the WRKPTFGRP command

select PTF_GROUP_NAME "Group ID", PTF_GROUP_DESCRIPTION "Description", PTF_GROUP_LEVEL "Level", PTF_GROUP_STATUS "Status", PTF_GROUP_TARGET_RELEASE "Rel."
   from GROUP_PTF_INFO
   order by PTF_GROUP_NAME, PTF_GROUP_LEVEL desc;

If I want to display for each group only the last installed level, or the equivalent of WRKPTFGRP PTFGRPLVL (* INSTALLED), the instruction is little more complex, but it is already ready for use:

with
  LASTLEVEL as
  (select PTF_GROUP_NAME, max (PTF_GROUP_LEVEL) PTF_GROUP_LEVEL
     from GROUP_PTF_INFO
     group by PTF_GROUP_NAME
  )
  DESCPTF as
  (select distinct PTF_GROUP_NAME, PTF_GROUP_DESCRIPTION, PTF_GROUP_STATUS, PTF_GROUP_TARGET_RELEASE
     from GROUP_PTF_INFO
     where PTF_GROUP_DESCRIPTION not like '% CUMULATIVE%'
   )
   DESCCUM as
   (select PTF_GROUP_NAME, PTF_GROUP_DESCRIPTION, PTF_GROUP_STATUS, PTF_GROUP_TARGET_RELEASE
      from GROUP_PTF_INFO
      where PTF_GROUP_DESCRIPTION like '% CUMULATIVE%'
      order by PTF_GROUP_LEVEL desc
      fetch first 1 rows only
   )
   DESCTOT as
   (select *
      from DESCPTF
    union all
    select *
      from DESCCUM
   )
  select L.PTF_GROUP_NAME "Group ID", PTF_GROUP_DESCRIPTION "Description", L.PTF_GROUP_LEVEL "Level", PTF_GROUP_STATUS "Status", PTF_GROUP_TARGET_RELEASE "Rel."
    from LASTLEVEL as L
      inner join DESCTOT
        using (PTF_GROUP_NAME)
    order by L.PTF_GROUP_NAME;

To view only the level and date of the last cumulative. That is the equivalent of the DSPPTF LICPGM command (5770SS1) whose output shows in the first line whose code starts with TC the level of the most recent cumulative

select max (PTF_GROUP_LEVEL) as "Cumulative level",
        date ('20 'concat max (PTF_GROUP_LEVEL)) "Data"
   from GROUP_PTF_INFO
   where PTF_GROUP_DESCRIPTION like 'CUMULATIVE PTF PACKAGE%' and PTF_GROUP_STATUS = 'INSTALLED';

In addition to the groups, I can extract – again with SQL – the details of the individual PTFs installed, or the equivalent of the DSPPTF command:

select PTF_IDENTIFIER "PTF ID", PTF_RELEASE_LEVEL "Rel.PTF", PTF_PRODUCT_ID "Product", PTF_PRODUCT_OPTION "Opt.", PTF_PRODUCT_DESCRIPTION "Product description", PTF_PRODUCT_RELEASE_LEVEL "Rel.prod.",
        PTF_LOADED_STATUS "Status", PTF_IPL_ACTION "IPL Action", PTF_ACTION_PENDING "Pending Action", PTF_ACTION_REQUIRED "Requested Action", PTF_IPL_REQUIRED "IPL Required", timestamp (PTF_TEMPORARY_APPLY_TIMESTAMP, 0) "Application Date / Time Temp",
        PTF_SUPERCEDED_BY_PTF "PTF exceeded by"
   from PTF_INFO
   where PTF_IDENTIFIER like 'MF%'
     --and PTF_LOADED_STATUS <> 'SUPERCEDED' - exclude PTF exceeded
   order by PTF_IDENTIFIER desc;

Are new updates available?

The GROUP_PTF_CURRENCY view is very powerful and convenient, as in addition to showing the level of the groups installed on my IBM i, it consumes a web service that returns the level available in Fix central, thus providing in a few seconds a complete picture of what is needed to update :

select PTF_GROUP_ID "Group ID", PTF_GROUP_TITLE "Description", PTF_GROUP_CURRENCY "Update status", PTF_GROUP_STATUS_ON_SYSTEM "Installation status", PTF_GROUP_LEVEL_INSTALLED "Installed level", PTF_GROUP_LEVEL_AVAILABLE "Available level", PTF_GROUP_LAST_UPDATED_BY_IBM "Update date"
   from SYSTOOLS / GROUP_PTF_CURRENCY
   --where PTF_GROUP_CURRENCY = 'UPDATE AVAILABLE' - only groups to update
   order by PTF_GROUP_CURRENCY desc, PTF_GROUP_ID;

There is also the possibility to automate the control of PTF groups that must be updated via scripts with the Administration Runtime Expert product (for “ARE” friends) of which I speak more in detail in this article https://www.markonetools.it / administration-runtime-expert-are / e that I presented at the ONL-i Days event last June 2019. Everything is much simpler than it may seem.

To go deeper …

I can also extract the details of the missing PTFs on the system compared to those available in Fix Central and not yet present in a cumulative:

select PTF_IDENTIFIER "PTF ID", PTF_GROUP_NAME "Group ID", PTF_GROUP_DESCRIPTION "Description", PTF_PRODUCT_ID "Product", 
     PTF_STATUS "Inst. Status", 
     APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Disp. in a group from (MM / DD / YY) ", PTF_CUM_PACKAGE" Included in cumulative "
   from SYSTOOLS / GROUP_PTF_DETAILS
   where PTF_STATUS = 'PTF MISSING' and PTF_CUM_PACKAGE = 'NONE'
   order by PTF_GROUP_NAME, PTF_IDENTIFIER;

Or even extract the details of the PTFs installed for a specific group (eg the DB2):

select PTF_IDENTIFIER "PTF ID", PTF_GROUP_NAME "Group ID", PTF_GROUP_DESCRIPTION "Description", PTF_PRODUCT_ID "Product", 
     PTF_STATUS "Inst. Status", PTF_LOADED_STATUS "Current Status",
     APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Disp. in a group from (MM / DD / YY) ", PTF_CUM_PACKAGE" Included in cumulative ", 
     PTF_IPL_ACTION "IPL Action", PTF_ACTION_PENDING "Action outstanding", PTF_ACTION_REQUIRED "Action requested", PTF_IPL_REQUIRED "IPL Required",
     PTF_SUPERCEDED_BY_PTF "Passed by", timestamp (PTF_CREATION_TIMESTAMP, 0) "Date / time creaz. PTF "
   from SYSTOOLS / GROUP_PTF_DETAILS
   where PTF_STATUS <> 'PTF MISSING'
     and PTF_GROUP_NAME = 'SF99703' - DB2 group
   order by PTF_IDENTIFIER;

Through the view GROUP_PTF_DETAILS I can also identify the PTFs that are waiting for an action. That is the equivalent of DSPPTF SELECT (* ACTRQD). In a “normal” situation this query should not return any records.

select PTF_IDENTIFIER "PTF ID", PTF_GROUP_NAME "Group ID", PTF_GROUP_DESCRIPTION "Description", PTF_PRODUCT_ID "Product", 
     PTF_STATUS "Inst. Status", PTF_LOADED_STATUS "Current Status",
     APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Disp. in a group from (MM / DD / YY) ", PTF_CUM_PACKAGE" Included in cumulative ", 
     PTF_IPL_ACTION "IPL Action", PTF_ACTION_PENDING "Action outstanding", PTF_ACTION_REQUIRED "Action requested", PTF_IPL_REQUIRED "IPL Required",
     PTF_SUPERCEDED_BY_PTF "Passed by", timestamp (PTF_CREATION_TIMESTAMP, 0) "Date / time creaz. PTF "
   from SYSTOOLS / GROUP_PTF_DETAILS
   where PTF_STATUS <> 'PTF MISSING' and (PTF_ACTION_PENDING <> 'NO' or PTF_ACTION_REQUIRED not in ('NONE', 'IPL') or PTF_IPL_ACTION <> 'NONE')
   order by PTF_IDENTIFIER;

And the firmware?

To check the status of the firmware update (let’s not forget that in addition to the operating system it is important to keep the lower level of the software components of our IBM i up to date) the view FIRMWARE_CURRENCY (available from IBM i 7.3 TR6) comes to the rescue. That is the equivalent of the DSPFMWSTS command. To use this view, you must be authorized to use the DSPHDWRSC and DSPFMWSTS commands.

select *
   from SYSTOOLS / FIRMWARE_CURRENCY;

WARNING! At the time of publication of this article due to a problem of changing the format of the data returned by the web service this view does not return data. (Cfr. APAR SE72579 dated 3/12/2019 https://www-01.ibm.com/support/docview.wss?uid=nas2SE72579).

Download the complete script with all the SQL statements presented in the article from here.

These and many other SQL prêt-à-porter instructions on the MarkOneTools blog https://www.markonetools.it/sql-pret-a-porter/

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

IBM i's mentor IBM Champion 2021 for Power Systems

Leave a Reply

Your email address will not be published. Required fields are marked *