Retrieving the CPU usage information via SQL

One of the many additions to the last release and Technology Refresh, IBM i 7.5 and 7.4 TR6, was a SQL View that returns one row of results of the usage of the CPU.

To most people it may not sound the most interesting information, but I have been asked if there was a way to get to this information so it could be written to an outfile, by a couple of people. Previously the only way I knew how to show the information was the Work with System Activity command, WRKSYSACT.

Work with System Activity DEV750 DD/DD/DD TT:TT:TT Automatic refresh in seconds . . . . . . . . . . . . . . . . . . . 5 Job/Task CPU filter . . . . . . . . . . . . . . . . . . . . . . . . .10 Elapsed time . . . . . . : 00:00:02 Overall CPU util . . . . : 80.1 Overall SQL CPU util . . . : 30.4 Average CPU rate . . . . . : 101.2 Current processing capacity: 1.00

The View, SYSTEM_ACTIVITY_INFO, allows me to get to the needed information. It has a single optional parameter:

DELAY_SECONDS:  The number of seconds that separates the collection of the results. If not given it uses a default value of 1.

In this first example I am not going to give a number of seconds to delay, therefore, a delay of one second is used:

SELECT * FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO())

The View returns four columns:

AVERAGE_ AVERAGE_CPU_ MINIMUM_CPU_ MAXIMUM_CPU_ CPU_RATE UTILIZATION UTILIZATION UTILIZATION -------- ------------ ------------ ------------ 100.00 4.36 4.36 4.36

1. AVERAGE_CPU_RATE:  Average CPU rate shown as a percentage of its nominal frequency, which would be shown as 100%. If a value is greater than 100%, for example 120%, then the processor is running at (120% – 100%) 20% faster than its nominal speed
2. AVERAGE_CPU_UTILIZATION:  Average CPU utilization for all of the active processors
3. MINIMUM_CPU_UTILIZATION:  CPU utilization of the CPU that reported the minimum amount of CPU utilization
4. MAXIMUM_CPU_UTILIZATION:  CPU utilization of the CPU that reported the maximum amount of CPU utilization

If I wanted to use the delay seconds I could include the parameter name:

SELECT * FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO(DELAY_SECONDS => 30))

Or just the number of seconds:

SELECT * FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO(30))

The results for the above statement are:

AVERAGE_ AVERAGE_CPU_ MINIMUM_CPU_ MAXIMUM_CPU_ CPU_RATE UTILIZATION UTILIZATION UTILIZATION -------- ------------ ------------ ------------ 98.80 11.54 11.54 11.54

As I am not the only person using this partition are the differences in results caused by the delay seconds or the fluctuations of demands made by other users and jobs? I tried to test this with the following statement:

SELECT * FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO()) UNION SELECT * FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO(30))

I am using a UNION clause to combine the results from the two statements into one set of results. I know it is not perfect, but it was best way I could think to test if there were really any difference in the results they return. I ran this four times over an hour and the results were:

AVERAGE_ AVERAGE_CPU_ MINIMUM_CPU_ MAXIMUM_CPU_ CPU_RATE UTILIZATION UTILIZATION UTILIZATION -------- ------------ ------------ ------------ 100.00 1.79 1.79 1.79 98.73 2.85 2.85 2.85 98.86 6.11 6.11 6.11 98.85 42.03 42.03 42.03 98.79 31.74 31.74 31.74 98.75 3.43 3.43 3.43 98.86 4.65 4.65 4.65 100.00 5.81 5.81 5.81

Just with this small sample of results it would appear that there is not much of a difference between the two. From this point forward I am going to use this statement with the default, one second.

If I am going to capture this information into an "outfile" I am going to need a table into which to insert the data.

01 CREATE TABLE MYLIB.TESTTABLE 02 (PARTITION,TIMESTAMP,CPU_RATE,AVERAGE_CPU) 03 AS 04 (SELECT CHAR(CURRENT_SERVER,10), 05 CURRENT_TIMESTAMP, 06 DEC(AVERAGE_CPU_RATE,5,2), 07 DEC(AVERAGE_CPU_UTILIZATION,5,2) 08 FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO())) 09 DEFINITION ONLY ;

Line 1: The table will be created in my library.

Line 2: These are the names of the Table's columns:

1. PARTITION:  Name of the partition
2. TIMESTAMP:  Timestamp of when the row was inserted into the Table
3. CPU_RATE:  Average CPU rate
4. AVERAGE_CPU:  Average CPU utilization

Line 4 - 8: I am creating the Table "on the fly" using a SQL Statement.

I have redefined several of the columns to make them a more suitable size for what I want.

• CURRENT_SERVER by using the CHAR scalar function I convert it from VARCHAR(28) to CHAR(10)
• AVERAGE_CPU_RATE and AVERAGE_CPU_UTILIZATION by using the DEC scalar function I converted them from DEC(20,2) to DEC(5,2)

Line 9: DEFINITION ONLY means that the Table will be created, but not populated with data.

I can insert a row of data with the following statement:

01 INSERT INTO TESTTABLE 02 SELECT CURRENT_SERVER, 03 CURRENT_TIMESTAMP, 04 AVERAGE_CPU_RATE, 05 AVERAGE_CPU_UTILIZATION 06 FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO())

As I have already "resized" those columns in the Table I do not have to do so again in the Insert statement. The result from this insert looks like:

SELECT * FROM TESTTABLE ; CPU_ AVERAGE PARTITION TIMESTAMP RATE _CPU --------- -------------------------- ------ ------- DEV750 YYYY-MM-DD 10:47:32.416071 98.74 4.17

To update this "outfile" I am going to use a RPG program:

01 **free 02 ctl-opt option(*srcstmt:*nodebugio) dftactgrp(*no) ; 03 dcl-pr sleep extproc('sleep') ; 04 *n uns(10) value ; 05 end-pr ; 06 dcl-s Seconds uns(10) ; 07 exec sql SET OPTION COMMIT = *NONE ; 08 Seconds = 5 * 60 ; // 5 minutes in seconds 09 dow (*on) ; 10 exec sql INSERT INTO TESTTABLE SELECT CURRENT_SERVER, CURRENT_TIMESTAMP, AVERAGE_CPU_RATE, AVERAGE_CPU_UTILIZATION FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO()) ; 11 sleep(Seconds) ; 12 enddo ; 13 *inlr = *on ;

Line 1`: Why would anyone in 2022 not use totally free RPG.

Line 2: My favorite control options, and I need the DFTACTGRP as I am going to call an external procedure.

Lines 3 - 5: I am going to call the sleep C function to pause the program by the number of seconds I want. This is the prototype definition for the function.

Line 6: Variable to contain the number of seconds to "sleep" this program.

Line 7: I do not want to commit the data from the Insert, therefore, I make sure that commitment control is turned off with this statement.

Line 8: I am just being lazy here. I am calculating the number of second five minutes is, 300. Why did I do this? If I need to change the number of minutes to 75 or 13 or 123 I don't have to do the calculation to translate that number of minutes to seconds. This calculation does it for me.

Line 9 – 12: This is a never-ending loop.

Line 10: The SQL Insert statement into the "outfile" Table.

Line 11: Now I sleep for the period I desire.

And after its sleep the program loops again.

After compiling the program, I submitted to batch and left it run for 25 minutes. These were the results in the "outfile" Table:

CPU_ AVERAGE PARTITION TIMESTAMP RATE _CPU --------- -------------------------- ------ ------- DEV750 YYYY-MM-DD 11:39:40.932170 100.00 2.54 DEV750 YYYY-MM-DD 11:44:42.003215 98.63 28.61 DEV750 YYYY-MM-DD 11:49:43.170619 100.00 1.35 DEV750 YYYY-MM-DD 11:54:44.266721 98.86 50.48 DEV750 YYYY-MM-DD 11:59:45.337086 98.73 53.00

A simple View and a simple RPG program to capture this information.

 

You can learn more about the SYSTEM_ACTIVITY_INFO SQL View from the IBM website here.

 

This article was written for IBM i 7.5 and 7.4 TR6.