Markone’s suggestions n. 5
Index
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:
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;
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.
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;
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/
Early April saw the release of the "Spring Version" of ACS Access Client Solution, version 1.1.9.5 Interesting new features especially…
If the packed agenda of sessions at Common Europe Congress 2024, June 3-6 Milan, wasn't enough for you, here's another…
Debugging functions with Visual Studio Code have been available for some time but this new version 2.10.0 simplifies the handling…
Two and a half years after my post Transferring objects with ObjectConnect and Enterprise Extender, I finally got around to…
With a little trick even a simple SELECT statement can execute any system command! Let's see how to do that...
A mini-serial guide to configuring, managing, using, and troubleshooting the IBM i NetServer