Wednesday, February 20, 2019

Capturing system statistics using SQL

system_status_info sql view rather than wrksyssts command

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.

10 comments:

  1. Thanks as always, Simon, for another useful tip.
    Could use the "audit" timestamp to get the timestamp automatically. Just another way to do it.
    alter table mylib.mysyssts
    add column run_time
    FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL

    insert into mylib.mysyssts
    (SYSTEM, ELAP_USED, SYS_RATE,AUX_STG)
    SELECT CAST(HOST_NAME AS CHAR(10)) AS SYSTEM,
    ELAPSED_CPU_USED,
    SYSTEM_ASP_USED,
    TOTAL_AUXILIARY_STORAGE
    FROM QSYS2.SYSTEM_STATUS_INFO

    ReplyDelete
  2. Thank you again Simon for usefull information.
    When i do things like this i prefer to do "Insert into select from" as one sql statement, then i dont need to define variables in my program.

    Whenever i do cmd or send email and so on, from a program i also prefer to declare the "cmd" as a constant like
    Dcl-c cSndEmail 'SNDSMTPEMM RCP((''&EMAIL&'')) SUBJECT(''&SUBJECT&'')' and so on... then do %scanrpl in program ala:
    xCmd = %ScanRpl('&EMAIL&':'emailadress':cSndEmail);
    xCmd = %ScanRpl('&SUBJECT&':'my subject':xCmd);

    I think it makes it more easy to read the program, because the command beeing used i so easy to read instead of a lot of concattening.
    Best Regards Jan

    ReplyDelete
  3. Rusé, clair... comme d'habitude. Merci

    ReplyDelete
  4. Antonio Charles BonaldeJanuary 2, 2020 at 9:52 AM

    very good

    ReplyDelete
  5. How do you created the QCMDEXC command, can you tell me what you did ? As for me I can't use it.

    ReplyDelete
    Replies
    1. QCMDEXC is not a command that comes with IBM i. It is either a program or a SQL procedure. In this example I am using the SQL procedure.

      If you cannot use the SQL procedure in the partition you are using check the release number and whether it is up to date with PTFs.

      Delete
  6. i get error while compilation like ELAP_RATE, QSYS2 : Name or indicator not defined. Please help

    ReplyDelete
    Replies
    1. There are several columns in the View that start with ELAP, but there is no ELAP_RATE.

      All of those fields are integer and numeric, and some are nullable. Make sure that whatever your code moves them to is the correct data type, and you have code to cope with null values. Examples of both of these can be found in this blog. Use the "Search this site" page to look for them.

      Delete
  7. Harold Adolfo Mendoza AvendañoAugust 20, 2021 at 8:09 AM

    Excelente Simón gracias

    ReplyDelete
  8. Simon, thanks for sharing. Great read and information.. Another teaching moment for you. Thank you..

    ReplyDelete

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.