IBM i: The SQL Way · #6

Check IBM i Group PTF Currency with a Local XML Feed

Use SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL to compare installed IBM i Group PTF levels with IBM service data from a controlled local XML file.

A production IBM i partition may not be allowed to access the internet. SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL lets the system compare installed Group PTF levels with a locally stored copy of IBM’s service-level feed.

The new table function is:

SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL

It compares:

Group PTF levels installed on the partition
against
Group PTF levels described in a local IBM PSP XML file

This is useful for:

• internet-restricted partitions
• controlled maintenance processes
• environments that require an approved offline feed
• repeatable PTF compliance reporting
• comparing several partitions against the same feed snapshot

Basic query

Assume the IBM PSP XML feed has been copied to:

/localpsp/xmldoc.xml

Run:

SELECT *
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/localpsp/xmldoc.xml'
    )
)
ORDER BY
    PTF_GROUP_LEVEL_AVAILABLE
      - PTF_GROUP_LEVEL_INSTALLED DESC;

The largest differences appear first.

Show only groups with an update available

SELECT
    PTF_GROUP_ID,
    PTF_GROUP_TITLE,
    PTF_GROUP_RELEASE,
    PTF_GROUP_LEVEL_INSTALLED,
    PTF_GROUP_LEVEL_AVAILABLE,
    PTF_GROUP_CURRENCY,
    LAST_UPDATED_BY_IBM,
    PTF_GROUP_STATUS_ON_SYSTEM,
    PTF_GROUP_APPLY_TIMESTAMP
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/localpsp/xmldoc.xml'
    )
)
WHERE PTF_GROUP_CURRENCY = 'UPDATE AVAILABLE'
ORDER BY
    PTF_GROUP_LEVEL_AVAILABLE
      - PTF_GROUP_LEVEL_INSTALLED DESC,
    PTF_GROUP_ID;

Main status values

INSTALLED LEVEL IS CURRENT

The installed Group PTF level matches the latest level found in the local feed.

CURRENT AT THE NEXT IPL

The latest level is present but will become current after the required IPL.

UPDATE AVAILABLE

IBM’s feed contains a newer Group PTF level than the one installed.

PSP INFORMATION NOT AVAILABLE

The service could not determine current PSP information.

With the local function, investigate the file path, XML contents, CCSID, and freshness requirements.

Main columns

PTF_GROUP_ID

The seven-character Group PTF identifier.

Examples vary by IBM i release and product.

PTF_GROUP_TITLE

The descriptive name of the Group PTF.

PTF_GROUP_LEVEL_INSTALLED

The newest group level installed on the partition.

PTF_GROUP_LEVEL_AVAILABLE

The group level listed as current in the local IBM feed.

LAST_UPDATED_BY_IBM

The date IBM made the latest group level available.

PTF_GROUP_STATUS_ON_SYSTEM

Typical values include:

INSTALLED
NOT INSTALLED

A group can be present but not fully applied.

PTF_GROUP_APPLY_TIMESTAMP

The timestamp associated with the most recently applied PTF for the group.

The value can be null when the group is not fully applied.

Require a recently updated file

The optional second parameter sets the oldest acceptable modification timestamp for the local XML file.

SELECT *
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/localpsp/xmldoc.xml',
        CURRENT TIMESTAMP - 2 DAYS
    )
);

If the file is older than the supplied timestamp, the function returns an error instead of silently comparing against stale data.

When the parameter is omitted, the default accepts a file changed within the previous seven days.

This is an important control. A successful comparison against an old feed can create false confidence.

Obtain the XML feed from another device

For a partition with no internet access:

1. Download IBM’s PSP XML feed from an approved workstation.
2. Validate the source and transfer process.
3. Copy the file into a controlled IFS directory.
4. Restrict write authority to the update process.
5. Query GROUP_PTF_CURRENCY_LOCAL.
6. Record the feed timestamp used for the report.

IBM’s documentation identifies the feed as:

https://public.dhe.ibm.com/services/us/igsc/PSP/xmldoc.xml

Refresh the file directly from IBM i

When outbound access is permitted, IBM provides an SQL example using IFS_WRITE_UTF8 and HTTPGETCLOB:

CALL QSYS2.IFS_WRITE_UTF8(
    '/localpsp/xmldoc.xml',
    SYSTOOLS.HTTPGETCLOB(
        'https://public.dhe.ibm.com/services/us/igsc/PSP/xmldoc.xml',
        ''
    ),
    OVERWRITE => 'REPLACE'
);

Then query the local copy:

SELECT *
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/localpsp/xmldoc.xml'
    )
);

For production automation, add:

• error handling
• connection and TLS validation
• file ownership checks
• logging
• age validation
• alerting when the refresh fails

Job CCSID limitation

The job CCSID cannot be:

65535

The service parses XML text. Use a compatible text CCSID.

Compare several systems consistently

A useful enterprise process is:

Approved feed downloaded once
        ↓
Feed distributed to each partition
        ↓
Same SQL query run everywhere
        ↓
Results written to a central table
        ↓
Differences reviewed by maintenance owners

Using the same feed removes timing differences between systems that query IBM independently.

Save the comparison result

Create a history table:

CREATE TABLE MYADM.PTF_CURRENCY_HISTORY
(
    CAPTURE_TIMESTAMP          TIMESTAMP NOT NULL,
    SYSTEM_NAME                VARCHAR(8),
    PTF_GROUP_ID               CHAR(7),
    PTF_GROUP_TITLE            VARCHAR(1000),
    PTF_GROUP_RELEASE          VARCHAR(6),
    LEVEL_INSTALLED            INTEGER,
    LEVEL_AVAILABLE            INTEGER,
    CURRENCY_STATUS            VARCHAR(46),
    STATUS_ON_SYSTEM           VARCHAR(20),
    LAST_UPDATED_BY_IBM        DATE,
    APPLY_TIMESTAMP            TIMESTAMP
);

Insert a snapshot:

INSERT INTO MYADM.PTF_CURRENCY_HISTORY
SELECT
    CURRENT TIMESTAMP,
    HOST_NAME,
    PTF_GROUP_ID,
    PTF_GROUP_TITLE,
    PTF_GROUP_RELEASE,
    PTF_GROUP_LEVEL_INSTALLED,
    PTF_GROUP_LEVEL_AVAILABLE,
    PTF_GROUP_CURRENCY,
    PTF_GROUP_STATUS_ON_SYSTEM,
    LAST_UPDATED_BY_IBM,
    PTF_GROUP_APPLY_TIMESTAMP
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/localpsp/xmldoc.xml'
    )
) AS P
CROSS JOIN
(
    SELECT HOST_NAME
    FROM QSYS2.SYSTEM_STATUS_INFO_BASIC
) AS S;

Adjust the system-name source if your environment uses a different naming standard.

Native command versus SQL

Use:

WRKPTFGRP

when:

• reviewing one partition interactively
• applying or inspecting PTF group status
• working directly from a green screen

Use SQL when:

• comparing installed and available levels
• filtering only outdated groups
• creating a report
• saving history
• checking multiple partitions
• feeding a dashboard or alert

Local versus live service

SYSTOOLS.GROUP_PTF_CURRENCY uses a live IBM feed.

SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL uses the file supplied by the caller.

Choose the local version when:

• the partition has no internet access
• the organization requires a controlled feed
• comparisons must use the same snapshot
• an audit requires evidence of the exact source file used

Authority and IFS security

The caller needs authority to read the XML file and use the interfaces behind the SYSTOOLS function.

Protect the IFS file from unauthorized replacement.

If someone can alter the feed file, they can influence the reported PTF currency.

Recommended controls include:

• restricted directory ownership
• read-only authority for report users
• controlled feed-update profile
• file hash or transfer validation
• retention of the downloaded source file

A practical workflow

1. Download or refresh the IBM PSP XML feed.
2. Record the source and refresh timestamp.
3. Store it in a protected IFS directory.
4. Reject a file older than the approved threshold.
5. Query GROUP_PTF_CURRENCY_LOCAL.
6. Filter UPDATE AVAILABLE and CURRENT AT THE NEXT IPL.
7. Review superseded and required PTFs.
8. Plan installation and IPL activity.
9. Save evidence of the review.
10. Repeat on the maintenance schedule.

Final takeaway

SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL brings PTF currency checking to systems that cannot—or should not—query IBM directly.

The quality of the result depends on the quality and freshness of the local XML feed.

Protect the file, validate its age, and treat the SQL result as an input to a controlled IBM i maintenance process.