View IBM i health - using SQL!

IBM has made it possible to retrieve many details about how your IBM i is running using SQL. When I first heard of this ability I was certainly surprised to say the least. Previously the only way to get this information was through APIs. Though this was useful it involved quite a lot of programming. Using SQL may not seem intuitive, after all it is not really data from a relational database, but it works very well through simple and easy to understand SQL statements. A further advantage is that if you wanted to go further you could execute the SQL statements from a generic dashboard function. The dashboard will display the information in graphic form and provide alerts before resources run out. Let’s get to some examples.

Active Jobs

// Top 10 jobs;
SELECT JOB_NAME, authorization_name, function_type, function, temporary_storage, elapsed_cpu_percentage
FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'WORK')) X
ORDER BY ELAPSED_CPU_PERCENTAGE desc fetch first 10 rows only;

The above command works in a similar way to WRKACTJOB (With with Active Jobs) and shows the top 10 jobs by CPU usage. If a program is running away with the CPU then it will show here. If you want to reset the statistics, to reset the elapsed time for example, then use reset_statistics=>'YES' :

SELECT JOB_NAME, authorization_name, function_type, function, temporary_storage, elapsed_cpu_percentage
FROM TABLE (QSYS2.ACTIVE_JOB_INFO(reset_statistics=>'YES', DETAILED_INFO => 'WORK')) X
ORDER BY ELAPSED_CPU_PERCENTAGE desc fetch first 10 rows only;

There is more information at on the active job functions here.

System Status

If you just want a simple overview of the system similar to WRKSYSSTS (Work with System Status) then try this:

SELECT system_asp_used, total_jobs_in_system, active_jobs_in_system, elapsed_cpu_used FROM QSYS2.SYSTEM_STATUS_INFO_BASIC;

On our development system, this query returned the following:

The first and last values are percentages. You can see that we have a lot of jobs running, these are all our development and testing systems. However, the IBM i is a high performance machine and so the CPU usage is very low.

There is more information about these commands here.

This article has just shown a couple of simple examples of what is possible with IBM i Work Management SQL queries. There are many other SQL statements and a great many fields that can be retrieved. Why not give it a try yourself?

PacSol have been working with and supporting document management services on IBM i (as/400) power systems since 1995 and our collective experience with IBM platforms extends for far longer.

Mark Wheadon. Managing Director at PacSol. January 2024

#pacsoluk #ibmi #systemperformance #documentmanagement #reporting