Capturing system statistics using SQL

I thought this question would be a good reason to introduce another Db2 for i view that I find useful when monitoring IBM i:

I would like to collect the percentage CPU used and percentage system ASP used (from WRKSYSSTS) every day at 00:00 hours and save it in an outfile or print it to an spool file or send the values as a message to a user.

I could dump the contents of WRKSYSSTS to spool file, copy that to a data file, and then extract the information I want from the data file, or I could use an API. But why would I bother to go through all that effort to get the same information I can get from the Db2 for i view: SYSYTEM_STATUS_INFO

What information do I want? On the WRKSYSSTS display I only want the following:

Work with System Status DEV730 % CPU used . . . . : 14.0 Auxiliary storage: % system ASP used . . : 48.0259 Total . . . . . . . . : 205.0 G

Using a simple SQL Select statement I can get the same information:

SELECT CAST(HOST_NAME AS CHAR(10)) AS SYSTEM, ELAPSED_CPU_USED, SYSTEM_ASP_USED, TOTAL_AUXILIARY_STORAGE FROM QSYS2.SYSTEM_STATUS_INFO

Which returns to me:

SYSTEM ELAPSED_CPU SYSTEM_ASP TOTAL_AUXILIARY _USED _USED _STORAGE ------ ----------- ---------- --------------- DEV730 14.00 48.02 205,069

The View holds a lot more information that I am going to use in this example. If you are interested in learning more about this view click on the link at the bottom of this post for IBM's documentation.

The view only contains one row, so I don't have to state I only want one row of data when I use a Select statement.

If I was going to capture the information mentioned in the original question I would want to save it to an outfile, and email it.

My outfile is really a DDL table, created by the following SQL statements.

01 CREATE TABLE MYLIB.MYSYSSTS AS ( 02 SELECT CAST(HOST_NAME AS VARCHAR(10)) AS SYSTEM, 03 ELAPSED_CPU_USED, 04 SYSTEM_ASP_USED, 05 TOTAL_AUXILIARY_STORAGE 06 FROM QSYS2.SYSTEM_STATUS_INFO) 07 DEFINITION ONLY 08 INCLUDING COLUMN DEFAULTS ; 09 ALTER TABLE MYSYSSTS 10 ADD COLUMN RUN_TIMESTAMP FOR "RUN_TIME" TIMESTAMP ; 11 LABEL ON TABLE MYSYSSTS IS 'Snapshot of System Status' ;

Line 1: The created table will be created in my library with the name: MYSYSSTS

Line 2: The host name in the view is a variable 255 long character column. I know that all of the host names I work with are less than 10 characters long, therefore, I am casting the host name to be this smaller size, and still be variable length.

Lines 3 – 5: The other columns I want in this table. Notice that I have not defined them.

Line 6: This is the really cool thing to me. Having using a Select statement to create the table is like using reference columns, which are based upon the columns in the SYSTEM_STATUS_INFO view.

Line 7: I only need to define the table. I do not need to fill it with data.

Line 8: This line ensures that all the rules that apply to the columns in the "reference" view are applied to these columns too.

Lines 9 and 10: I want a timestamp column in the table too. If I had defined it within the create table statement I would have had to list and define all the columns. Why both to do that when I can use the "reference" table and then add one more column afterwards.

Line 11: This gives the table a description.

I am going to use a RPG program, with embedded SQL, to retrieve the data from the SQL view, insert it into the table, and send an email with the information in the email's message area.

01 **free 02 ctl-opt main(Main) 03 option(*nodebugio:*srcstmt) 04 dftactgrp(*no) ; 05 dcl-ds Data extname('MYSYSSTS') qualified ; 06 end-ds ; 06 dcl-proc Main ; 07 GetData() ; 08 InsertData() ; 09 SendData() ; 10 end-proc ;

Line 1: I only code in totally free RPG.

Lines 2 – 4: The control options. This program contains a Main procedure and as I am not passing any parameters to this program I just need this, and not any procedure definition (DCL-PR). Line 3 has my favorite options that make debugging this program so much easier. And as I am using subprocedures I need line 4 too.

Lines 5 and 6: Define a data structure that I will be using to contain the results I get from the SQL view. I will be using the short names for the subfields in this program. If I wanted to use the long names I would have to use the ALIAS keyword.

Lines 6: The start of the Main procedure.

Lines 7 – 9: All the Main procedure does is to call three subprocedures.

1. Get the data from the view
2. Insert the data into the table
3. Create and send the email

The subprocedure to get the data from the view just contains a SQL select statement.

11 dcl-proc GetData ; 12 exec sql SELECT HOST_NAME,ELAP_USED, 13 SYS_RATE,AUX_STG,CURRENT_TIMESTAMP 14 INTO :Data 15 FROM QSYS2.SYSTEM_STATUS_INFO ; 16 end-proc ;

As there is only one row of data I can just select the columns into the data structure, line 14, without fear of an error caused by a second row.

The subprocedure used to insert the data into the table contains just one line as I can insert from from the data structure without having to list the individual columns.

17 dcl-proc InsertData ; 18 exec sql INSERT INTO MYSYSSTS VALUES(:Data) ; 19 end-proc ;

The subprocedure to create and send the email is a bit more complicated as I need to build the email message, create the send email command, and execute the send command.

20 dcl-proc SendData ; 21 dcl-s EmailAddress char(30) inz('simon@rpgpgm.com') ; 22 dcl-s EmailMessage char(200) ; 23 dcl-s String char(300) ; 24 EmailMessage = 'System . . . . . . . . : ' + 25 %trimr(Data.SYSTEM) + '<br>' + 26 'Date and time . . . . . : ' + 27 %char(Data.RUN_TIME) + 28 '<br>' + 29 'Elapsed CPU . . . . . . : ' + 30 %trim(%char(Data.ELAP_USED)) + 31 '<br>' + 32 'System ASP used . . . . : ' + 33 %trim(%char(Data.SYS_RATE)) + 34 '<br>' + 35 'Total auxillary storage : ' + 36 %trim(%char(Data.AUX_STG)) ; 37 String = 'SNDSMTPEMM RCP((''' + %trimr(EmailAddress) + ''')) + 38 SUBJECT(''System statistics'') + 39 NOTE(''' + %trimr(EmailMessage) + ''') + 40 CONTENT(*HTML)' ; 41 exec sql CALL QSYS2.QCMDEXC(:String) ; 42 end-proc ;

Line 21: I have created a variable to contain the email address as a variable. This way it is easier to change it here, rather than in the statement to build the send email command.

Lines 24 – 36: I am building a variable that will be the message area of the email. As the email message will be HTML I am inserting line breaks using the HTML <BR> tag at the end of each line.

Lines 37 – 40: I am making the command, SNDSMTPEMM, that I will use to send the email message. When this is finished it looks like:

....5...10...15...20...25...30...35...40...45...50...55...60 1 'SNDSMTPEMM RCP(('simon@rpgpgm.com')) SUBJECT('System statist' 61 'ics') NOTE('System . . . . . . . . : DEV730<br>Date and tim' 121 'e . . . . . : 2019-02-19-21.54.12.464521<br>Elapsed CPU . . ' 181 '. . . . : 16.50<br>System ASP used . . . . : 49.78<br>Total ' 241 'auxillary storage : 205069') CONTENT(*HTML) '

Line 41: I am executing the command I built using SQL's QCMDEXC as I don't have to bother with the length of the parameter, as I would have to do if I used the QCMDEXC API.

When I look in the table MYSYSSTS I can see that a record was added:

SYSTEM ELAPSED_CPU SYSTEM_ASP TOTAL_AUXILIARY RUN_TIMESTAMP _USED _USED _STORAGE ------ ----------- ---------- --------------- -------------------------- DEV730 16.50 49.78 205,069 2019-02-19-21.54.12.464521

Why did I include the host name in the table? As I work with multiple partitions I could modify this program to retrieve the same information from multiple partitions and insert it into the same table. I need to host name to know which partition the data came from.

Now all I need to do is to add this to the job scheduler to run on the days and time I desire.

 

You can learn more about the SYSTEM_STATUS_INFO view from the IBM website here.

 

This article was written for IBM i 7.3, and will also work for IBM i 7.2 too.