GROUP_PTF_DETAILS view

The GROUP_PTF_DETAILS is a view containing a query which implements a live comparison of the PTFs within PTF Groups installed on the partition against the service levels listed on the IBM® Preventive Service Planning website.

When queried, the view uses the XMLTable() and HTTPGETBLOB() table functions to consume a live XML feed from IBM Preventive Service Planning (PSP). If the partition cannot connect to the PSP website, the query will fail with an SQL4302. When querying this view, the job CCSID cannot be 65535 or the query will fail.

The results of the query show which PTFs from all PTF Groups installed on the partition match the latest level made available by IBM and those which have a more recent version available.

Authorization: See Note below.

The following table describes the columns in the view. The system name is GRPPTFDTL. The schema is SYSTOOLS.

Table 1. GROUP_PTF_DETAILS view
Column name	System column name	Data type	Description
PTF_GROUP_DESCRIPTION	GRPDESC	VARCHAR(100)	Description of the PTF group.
PTF_GROUP_NAME	GRPNAME	CHAR(7)	Name of the PTF group.
PTF_STATUS	PTF_STATUS	VARCHAR(11)	Status of the PTF. PTF APPLIED The PTF has been loaded and applied. PTF LOADED The PTF has been loaded but not applied. PTF MISSING The PTF does not exists on this partition.
PTF_PRODUCT_ID	LICPGM	VARCHAR(7)	The licensed program for this PTF.
PTF_IDENTIFIER	PTFID	VARCHAR(7)	The identifier of the PTF.
APAR_NAME	APAR_NAME	VARCHAR(7)	The APAR name associated with the PTF.
INCLUDED_IN_GROUP	INCLUDED	DATE	The date that this PTF was first made available in a group PTF.
PTF_CUM_PACKAGE	PTF_CUMPKG	VARCHAR(8)	The identifier of the cumulative PTF package containing this PTF.
PTF_PRODUCT_DESCRIPTION	PRODDESC	VARCHAR(132) Nullable	Product description.
PTF_RELEASE_LEVEL	PTFRLS	VARCHAR(6) Nullable	The release level of the PTF.
PTF_PRODUCT_LOAD	PRODLOAD	VARCHAR(4) Nullable	The load ID of the product load for the PTF.
PTF_LOADED_STATUS	LOADSTAT	VARCHAR(19) Nullable	The current loaded status of the PTF. NOT LOADED The PTF has never been loaded. LOADED The PTF has been loaded. APPLIED The PTF has been applied. PERMANENTLY APPLIED The PTF has been applied permanently. PERMANENTLY REMOVED The PTF has been permanently removed. DAMAGED The PTF is damaged. An error occurred while applying the PTF. It needs to be reloaded and applied. SUPERSEDED The PTF is superseded. A PTF will have a status of superseded when one of the following situations occurs: Another PTF with a more recent correction for the problem has been loaded on the system. The PTF ID that has been loaded can be found in the PTF_SUPERSEDED_BY_PTF column. The PTF save file for another PTF with a more recent correction for the problem has been logged into *SERVICE on the system.
PTF_SAVE_FILE	SAVF	VARCHAR(3) Nullable	Indicates whether a save file exists for the PTF. NO The PTF has no save file. YES The PTF has a save file.
PTF_COVER_LETTER	COVER	VARCHAR(3) Nullable	Indicates whether a cover letter exists for the PTF. NO The PTF has no cover letter. YES The PTF has a cover letter.
PTF_ON_ORDER	ONORD	VARCHAR(3) Nullable	Indicates whether the PTF has been ordered. NO The PTF has not been ordered or has already been received. YES The PTF has been ordered.
PTF_IPL_ACTION	IPLACT	VARCHAR(19) Nullable	The action to be taken on this PTF during the next unattended IPL. NONE No action occurs at the next IPL. TEMPORARILY APPLIED The PTF is temporarily applied at the next IPL. TEMPORARILY REMOVED The PTF is temporarily removed at the next IPL. PERMANENTLY APPLIED The PTF is permanently applied at the next IPL. PERMANENTLY REMOVED The PTF is permanently removed at the next IPL.
PTF_ACTION_PENDING	ACTPEND	VARCHAR(3) Nullable	Indicates whether a required action has yet to be performed to make this PTF active. NO No required actions are pending for this PTF. YES A required action needs to occur for this PTF to be active. Check the Activation Instructions section of the cover letter to determine what the action is. If the PTF_ACTION_REQUIRED column is set to `IPL` and the activation instructions have been performed, then the PTF is active. However, this column will not be updated until the next IPL.
PTF_ACTION_REQUIRED	ACTREQ	VARCHAR(12) Nullable	Indicates whether an action is required to make this PTF active when it is applied. See the cover letter to determine what action needs to be taken. NONE No activation instructions are needed for this PTF. EXIT PROGRAM This PTF was shipped with activation instructions in the cover letter. This value is returned for all PTFs that have an exit program to update the status of the PTF after the activation instructions have been performed. IPL This PTF was shipped with activation instructions in the cover letter. No exit program exists to verify the activation instructions were performed.
PTF_IPL_REQUIRED	IPLREQ	VARCHAR(9) Nullable	Indicates whether an IPL is required to apply this PTF. DELAYED The PTF is delayed. The PTF must be applied during an IPL. IMMEDIATE The PTF is immediate. No IPL is needed to apply the PTF. UNKNOWN The type of the PTF is not known.
PTF_IS_RELEASED	RELEASED	VARCHAR(3) Nullable	Indicates whether the PTF save file is available for distribution to another system. This is set to YES only when the System Manager for IBM i licensed program is on the system and the product is supported. The PTF_SAVE_FILE column must have a value of `YES` before using the value in this column. NO The PTF save file cannot be distributed. YES The PTF save file is released and can be distributed to another system.
PTF_MINIMUM_LEVEL	MINLVL	VARCHAR(2) Nullable	The indicator of the lowest level of the product to which this PTF can be applied. The level can be AA to 99. Contains the null value if the product does not have a level.
PTF_MAXIMUM_LEVEL	MAXLVL	VARCHAR(2) Nullable	The indicator of the highest level of the product to which this PTF can be applied. The level can be AA to 99. Contains the null value if the product does not have a level.
PTF_STATUS_TIMESTAMP	STATTIME	TIMESTAMP Nullable	The date and time that the PTF status was last changed. Contains the null value when the status date and time is not available.
PTF_SUPERSEDED_BY_PTF	SUPERSEDE	VARCHAR(7) Nullable	The identifier of the PTF that has replaced this PTF. This field will be blank when the PTF is not superseded or when the superseding PTF has not been loaded on the system.
PTF_CREATION_TIMESTAMP	CRTTIME	TIMESTAMP Nullable	The date and time that the PTF was created. Contains the null value when the creation date and time cannot be determined.
PTF_INCLUDED_IN_GROUP_DATE	PTF_DATE	VARCHAR(10)	The date that this PTF was first made available in a group PTF. Contains the character form of a date formatted as MM/DD/YY.

Notes

The PSP websites used by this service are found based on the PTF groups that are currently installed on the partition. For each distinct PTF group, a unique PSP XML feed is accessed:

http://www.ibm.com/support/docview.wss?uid=nas4<PTF-Group-Name>&aid=1

For example, the JAVA PTF group details can be accessed using:

http://www.ibm.com/support/docview.wss?uid=nas4SF99572&aid=1

To determine the IP address for your geography, ping www.ibm.com.

This view is provided in the SYSTOOLS schema as an example of how to retrieve live data using an SQL view and table function. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar interfaces, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Example

Review the details for the PTFs which have not yet been applied for the PTF groups installed on this partition.
```
SELECT * FROM SYSTOOLS.GROUP_PTF_DETAILS
  WHERE PTF_STATUS <> 'PTF APPLIED'
  ORDER BY PTF_GROUP_NAME
```