Pages

Thursday, October 27, 2022

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.