Track Group PTFs with SQL

I have mentioned the SQL View GROUP_PTF_INFO a couple of times in passing in other posts on this website, I have not explained what this View gives us.

A PTF Group is a bundle of related PTFs which are designed and managed as a single group. This simplifies PTF updates as all the necessary individual PTFs for a specific group are applied together. Examples of PTF groups are:

• SF99760 for cumulative PTFs
• SF99960 for Db2 for i
• SF99962 for the IBM HTTP server

There are several ways I can check on the PTF Groups on my IBM i partition:

• DSPPTFGRP:  Display PTF Group command
• WRKPTFGRP:  Work with PTF Group command
• GROUP_PTF_INFO:  SQL View

In all of my examples I am going to use the PTF Group for Db2 for i, SF99960, on a partition that is running IBM i 7.6 .

When I use the commands DSPPTFGRP and WRKPTFGRP I am frustrated that I cannot get an output file of the information it retrieves. With WRKPTFGRP I can only display the results:

New Englanders flock to join the RPGPGM.COM-unity

I handed out more of the RPGPGM.COM-unity ribbons at the NEUGC 2026 conference. You can see those I gave them to here.

If you see me at an IBM i event feel free to introduce yourself to me. In all likelihood I will have a RPGPGM.COM-unity ribbon on me, and you can become a member. All I ask in return is a photograph of you with it.

If you would like to learn more about the RPGPGM.COM-unity click here.

Spring 2026 IBM i TRs coming later

On the usual schedule there is a Technology Refresh at about the time of the COMMON PowerUp conference. This year will be different.

I was told by an IBM insider, last week, that the release date of the TRs has been postponed until July of this year. They would not say why. I assumed that news of the delay was business proprietary information, and was not going to share it.

This morning I see a couple of other websites running the story that the TRs are being delayed until the time more of the new IBM Power11 servers are announced.

As others are reporting this news, I am publishing what I know.

New Job columns added to the Record Lock View

In my last post I explained how the parts of the IBM i job name had been added as separate columns to the OBJECT_LOCK_INFO SQL View. I am pleased to say that the same three columns have been added to the RECORD_LOCK_INFO View too:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Before these columns were added, if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause. With the addition of these three columns, I will show much easier this becomes.

If I wanted to check for record locks, not using RECORD_LOCK_INFO, I would use the Display Record Locks command, DSPRCDLCK. For example, if I wanted to check for record locks on TESTFILE, in my library, I would use the following:

01 DSPRCDLCK FILE(TESTFILE)

Which will show me the following:

New columns added to the Object Lock View

While the addition of these three new columns to the OBJECT_LOCK_INFO SQL View may not appear to be significant, I know it will make it easier for me to process data for jobs.

The three new columns are all part of the long job name, and now have their own columns:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Prior to the addition of these columns if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause.

Before I show any SQL statements I need to explain the scenario I created. I have two programs:

1. Program 1: This RPG program opens the file TESTFILE for update and then pauses for ten minutes
2. Program 2: The second program uses a display file, TESTDSPF, and uses the EXFMT operation code to show it on the screen

April presentations

I will be presenting at two in-person conferences this month, and look forward to seeing you at one or both of them.

The first is in Leominster, MA, for the Northeast User Group Conference, NEUGC, Tuesday April 7 - Thursday April 9.

This is my first time at this conference. I will be making eight presentations, starting with a two hour presentation on Tuesday night about SQL Views. You can see the complete schedule here.

To register go here.

Then on Monday April 27 - Thursday April 30 I will be in New Orleans, LA, for COMMON PowerUp 2026. This is the biggest IBM i conference of the year.

I will be busy here too making seven presentations. You can see the event's schedule here.

And register here.

Viewing the job's overrides

I have been burned before by file overrides, which point input, output, update, etc. both to an expected file. Anything new that comes along to help me show overrides makes me happy. The Fall 2025 Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, has done that with a new SQL View: OVERRIDE_INFO. This view will return information about the overrides for the current job only.

I can override all kinds of files:

• OVRDBF:  database files
• OVRDSPF:  display files
• OVRMSGF:  message files
• OVRPRTF:  printer files
• OVRSAVF:  save files
• OVRTAPF:  tape files

I readily admit I have not used all of these commands.

There are three different levels I can scope an override:

Update to the technical resources roadmap

Yesterday an updated version of the IBM i technical resource roadmap was published. This web page contains a list of useful links for all things IBM i, from both IBM and other sources too. You will even find a link to this website in the Blogs section.

You can visit the page using the link here.

I have also updated the link, number 6 in the "Links to useful sites", on the right.

I am sure you will find something of interest on that page.

Using SQL to view system problems

Just a few weeks ago someone asked me if there was a way, using SQL, he could view problems on his system. What we mean by "problems" are not just error messages. These are the kinds of problems/errors that create a "problem" on your partition that you would use the DSPRPB, Display Problem, command to view.

Fortunately in the Fall 2025 Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, was a new SQL View that displays this information: PROBLEM_INFO

Before I describe PROBLEM_INFO, I want to explain the methodology that was used before. IBM i has a Problem Management function that alerts when there are issues with hardware and system software. If I wanted to see what problems there are on this partition I would use the DSPPRB, Display Problem, command. I can enter the following on any command line:

DSPPRB

Wisconsinites join the RPGPGM.COM-unity

At my first conference of the year, WMCPA iCon, I handed out 51 ribbons to the newest members of the RPGPGM.COM-unity.

You can see photographs of these new members here.

If you see me at an IBM i event feel free to introduce yourself to me. In all likelihood I will have a RPGPGM.COM-unity ribbon on me, and you can become a member. All I ask in return is a photograph of you with it.

If you would like to learn more about the RPGPGM.COM-unity click here.

Reducing the amount of unnecessary Index Advisor data

I do check the Index Advisor on a regular basis to see what is recommended. I have found that this perspective can become overwhelmed by old data. Who cares what index were advised over a year ago? I either addressed that advice, or the program(s) were changed that used the files in that way.

Every six months I go into the tool and clear the data. This way I will only see the recent recommendations. I have an entry in my calendar to remind me to do this. Wouldn't it be nice if this could be automated.

As part of the latest Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7 a new Global variable is introduced that will automate the purging for me.

Limiting the amount of data SELF retains

While I love the amount of information that the IBM i will retain for us, there are times where it can become overwhelming, or redundant. An example of this is how much SELF, SQL Error Logging Facility, data do I wish to retain.

Prior to the latest Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, I was responsible for purging the SELF error log table, QSYS2/SQL_ERRORT. As part of these TRs a new Global variable was introduced.