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

And press Enter to view them all.

On the partition I am using this is what is shown:

Display Problems System: XXXXXXXX Position to . . . . . . . Problem ID Type options, press Enter. 5=Display details 6=Print details Opt Problem ID Status Problem Description _ 2604541539 ANSWERED Fix request _ 2604541494 ANSWERED Fix request _ 2604541448 ANSWERED Fix request _ 2604541400 ANSWERED Fix request _ 2604541357 ANSWERED Fix request _ 2604541300 ANSWERED Fix request _ 2604541249 ANSWERED Preventative fix package request _ 2604541199 ANSWERED Fix request Bottom

All of the problems are displayed, and I can use "5" in the Option column to display each one in turn. I only have a few here, but if there were more it would be harder to search for a pattern of problems using this screen.

The PROBLEM_INFO SQL View provides me the ability to take information from more than one result, and create my own "reports".

PROBLEM_INFO is found in the SYSTOOLS library. The first time I use a View I like to see all the columns and the information contained within. To that end this is the first SQL statement I used with this View:

01 SELECT * FROM SYSTOOLS.PROBLEM_INFO

I am not going to show the results as there are too many to show here.

The first column I checked was the PROBLEM_CATEGORY. This is where I can tell the importance of this problem. There are three categories:

• *CRITICAL:  Critical problem that need immediate attention
• *LOGONLY:  Problem is logged but will not be reported to the service provider
• *REPORT:  Problem should be reported to service provider

Looking at the other columns available, these are the ones I selected for my next SQL statement:

• PROBLEM_CATEGORY:  See above
• PROBLEM_IDENTIFIER:  Id assigned to the problem
• PROBLEM_TYPE:  How the problem was detected
• SEVERITY:  Severity
• PROBLEM_STATUS:  Current status of the problem
• OPEN_TIME:  When the problem was logged
• PROBLEM_MESSAGE_ID:  CPTF message id
• PROBLEM_DESCRIPTION:  Description

Therefore, my next statement is:

01 SELECT PROBLEM_CATEGORY,PROBLEM_IDENTIFIER,PROBLEM_TYPE,SEVERITY,PROBLEM_STATUS, 02 OPEN_TIME,PROBLEM_MESSAGE_ID,PROBLEM_DESCRIPTION 03 FROM SYSTOOLS.PROBLEM_INFO 04 ORDER BY OPEN_TIME DESC 05 LIMIT 5

Line 4: I want the results to have the most recent first.

Line 5: For the sake of this article, I only want to return the five results, i.e the five most recent problems.

Running this statement returns these results:

PROBLEM_ PROBLEM_ PROBLEM_ PROBLEM CATEGORY IDENTIFIER TYPE SEVERITY _STATUS -------- ---------- --------- -------- -------- *REPORT 2604541539 PTF ORDER ANSWERED *REPORT 2604541494 PTF ORDER ANSWERED *REPORT 2604541448 PTF ORDER ANSWERED *REPORT 2604541400 PTF ORDER ANSWERED *REPORT 2604541357 PTF ORDER ANSWERED PROBLEM_ PROBLEM_ OPEN_TIME MESSAGE_ID DESCRIPTION ------------------- ----------- ------------ 2026-02-14 12:05:56 CPX8C21 Fix request 2026-02-14 12:05:09 CPX8C21 Fix request 2026-02-14 12:04:21 CPX8C21 Fix request 2026-02-14 12:03:30 CPX8C21 Fix request 2026-02-14 12:02:45 CPX8C21 Fix request

If I wanted to get a count of the number of problems by Problem Category I could use the following:

01 SELECT PROBLEM_CATEGORY, 02 COUNT(*) AS "Count", 03 MIN(OPEN_TIME) AS "Oldest", 04 MAX(OPEN_TIME) AS "Most recent" 05 FROM SYSTOOLS.PROBLEM_INFO 06 GROUP BY PROBLEM_CATEGORY 07 ORDER BY PROBLEM_CATEGORY

Line 1: I want to show the Problem Category in the first column of the results.

Line 2: Followed by the number of rows of that category.

Line 3: The minimum of Open Time will return the timestamp from the oldest problem.

Line 4: The maximum of the Open Time returns the timestamp for the most recent problem.

Line 6: GROUP BY groups the results, that allows the subtotal to be calculated.

The results are a little disappointing:

PROBLEM_ CATEGORY Count Oldest Most recent -------- ----- ------------------- ------------------- *REPORT 8 2026-02-14 12:00:00 2026-02-14 12:05:56

This partition only has eight *REPORT, that is actually good news. But I wanted there to be more, to show a mixture of categories in these results.

I cannot believe that in the life of this partition there have only been eight problems, with the oldest being on February 14.

Problems are not automatically removed. They have to be manually deleted using the DLTPRB, Delete Problem, command.

If I enter the following on a command line:

DLTPRB

And prompt the command, by pressing F4, I can see all the available parameters I can use delete problems:

Delete Problem (DLTPRB) Type choices, press Enter. Problem identifier . . . . . . . PRBID *ALL Status type . . . . . . . . . . STATUS *ALL + for more values Days . . . . . . . . . . . . . . DAYS 30 Origin: ORIGIN Network identifier . . . . . . *NETATR Control point name . . . . . . *NETATR

I want to delete just the problems that are:

• *CLOSED:  Problem has been closed
• *VERIFIED:  Problem has been resolved and the system operator has verified that the problem is resolved

I would add the following command to a CL program:

01 DLTPRB PRBID(*ALL) STATUS(*CLOSED *VERIFIED) DAYS(30)

That I would schedule to run, say, weekly. And that would only keep the problems that need to be work or verified.

 

You can learn more about this from the IBM website:

• PROBLEM_INFO SQL View
• DSPPRB command
• DLTPRB command

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.