Tuesday, June 30, 2020

Subsystem status using SQL

check status of subsystem using new sql view

Last year I wrote about a way to check if a subsystem was active or not. The statement combined two table functions and could only check if a subsystem was active, if it was not returned in the results it was assumed to be inactive. Included in the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, is a new SQL View all about subsystems, now I can get the same information just from one View, including the status of the subsystem.

The information I am interested from the View SUBSYSTEM_INFO is:

  • Subsystem name and the library it resides in
  • Status: active, inactive, etc
  • Number of jobs currently running in the subsystem
  • Maximum number of jobs that could run in the subsystem
  • Job that is monitoring the subsystem

There are other columns in the View, and if you are interested to learn what they are click on the link at the bottom of this post to IBM's documentation.

If I wanted to have a list of all the active subsystems on the partition I am working up, I would use the following statement:

01  SELECT SUBSYSTEM_DESCRIPTION_LIBRARY AS "LIBRARY",
02         SUBSYSTEM_DESCRIPTION AS "SUBSYSTEM",
03         STATUS,
04         MAXIMUM_ACTIVE_JOBS AS "MAX ACT",
05         CURRENT_ACTIVE_JOBS AS "ACT JOBS",
06         SUBSYSTEM_MONITOR_JOB AS "MONITOR JOB",
07         TEXT_DESCRIPTION AS "TEXT"
08    FROM QSYS2.SUBSYSTEM_INFO 
09    WHERE STATUS = 'ACTIVE'

Lines 1 – 7: I think the View's column names fully describe what is in each column, therefore, I am not going to describe what each one is. I am giving each of these columns a short name just so that this will fit reasonably on this page.

Line 8: The View is in the library QSYS2.

Line 9: With this statement I am only interested in active subsystems.

The results look like:

LIBRARY   SUBSYSTEM  STATUS    MAX ACT  ACT JOBS
--------  ---------  --------  -------  --------
QHTTPSVR  QHTTPSVR   ACTIVE    -               8
QSYS      QBATCH     ACTIVE         10         1
QSYS      QCMN       ACTIVE    -               7
QSYS      QCTL       ACTIVE    -               1
QSYS      QINTER     ACTIVE    -               0
QSYS      QSERVER    ACTIVE    -              16
QSYS      QSPL       ACTIVE    -               1
QSYS      QSYSWRK    ACTIVE    -              59
QSYS      QUSRWRK    ACTIVE    -              24


MONITOR JOB             TEXT
--------------------    ----------------------
194704/QSYS/QHTTPSVR    HTTP SERVER SUBSYSTEM
194637/QSYS/QBATCH      Batch Subsystem
194642/QSYS/QCMN        Communications Subsystem
194603/QSYS/QCTL        Controlling Subsystem
194632/QSYS/QINTER      Interactive Subsystem
194612/QSYS/QSERVER     File Server Subsystem
194646/QSYS/QSPL        Spooling Subsystem
194604/QSYS/QSYSWRK     System subsystem
194614/QSYS/QUSRWRK     User subsystem

What about the subsystems that are not started?

01  SELECT SUBSYSTEM_DESCRIPTION_LIBRARY AS "LIBRARY",
02         SUBSYSTEM_DESCRIPTION AS "SUBSYSTEM",
03         STATUS,
04         TEXT_DESCRIPTION AS "TEXT"
05    FROM QSYS2.SUBSYSTEM_INFO 
06    WHERE STATUS <> 'ACTIVE'

Lines 1 – 4: I don't need all the columns I did in the "active" statement.

Line 6: Here I am saying where the status is not equal to active.

The first few results are:

LIBRARY  SUBSYSTEM   STATUS    TEXT
--------  ---------  --------  -----------------
QBRM      Q1ABRMENT  INACTIVE  -
QBRM      Q1ABRMNET  INACTIVE  -
QFAX      QFAXSBS    INACTIVE  FAX SUPPORT
QFAX      QFQSBS     INACTIVE  FAX SUPPORT
QGPL      QFNC       INACTIVE  Finance Subsystem

But what about using this in a RPG program to check if one or more subsystems I care about are active?

If I only care about one subsystem I can select the status from the one row using the following Select statement. In this example I want to know about the subsystem QPGMR in library QSYS.

01  exec sql SELECT STATUS INTO :wkStatus
               FROM QSYS2.SUBSYSTEM_INFO
              WHERE SUBSYSTEM_DESCRIPTION_LIBRARY = 'QSYS'
                AND SUBSYSTEM_DESCRIPTION = 'QPGMR' ;

02  dsply ('QPGMR is ' + wkStatus) ;

The INTO moves the value of the column STATUS into the program variable wkStatus, which I can then display using the DSPLY operation code.

DSPLY  QPGMR is INACTIVE

If I have more than one subsystem I want to retrieve the status of I can use a multiple row fetch to return all of the eligible results into a data structure array in one FETCH.

01  dcl-ds Data qualified dim(10) ;
02    Library char(10) ;
03    Subsystem char(10) ;
04    Active char(10) ;
05  end-ds ;

06  dcl-s Rows int(5) inz(%elem(Data)) ;

07  exec sql DECLARE C0 CURSOR FOR
08             SELECT SUBSYSTEM_DESCRIPTION_LIBRARY,
09                    SUBSYSTEM_DESCRIPTION,
10                    STATUS
11               FROM QSYS2.SUBSYSTEM_INFO
12              WHERE SUBSYSTEM_DESCRIPTION IN
13                      ('QINTER','QPGMR','QSPL')
14              ORDER BY SUBSYSTEM_DESCRIPTION,
15                       SUBSYSTEM_DESCRIPTION_LIBRARY
16              FOR READ ONLY ;

17  exec sql OPEN C0 ;

18  exec sql FETCH C0 FOR :Rows ROWS INTO :Data ;

19  exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;

20  exec sql CLOSE C0 ;

Lines 1 – 5: My data structure array contains subfields for library, subsystem name, and status.

Line 6: This is a dual purpose variable. When the program starts it is initialized with the number of elements of the data structure array, ten.

Lines 7 – 16: Definition of the cursor I will be using to retrieve the information I desire.

Lines 12 and 13: Here I am saying that the subsystem name must be in the list on line 13 to be included in the results.

Line 16: I always put this at the end of my cursors if I am not going to update them. It informs the compiler, and the reader, that I am using this cursor for input only.

Line 17: I have to open the cursor before I can get data from it.

Line 18: Here I fetch the number of rows in the variable Rows into my data structure array, Data.

Line 19: There may not have been ten rows of results to retrieve. I am using the GET DIAGNOSTICS to update the program variable Rows with the number of rows returned, three.

Line 20: As I am finished with my cursor I close it.

If I start debug and place a breakpoint after the close statement I can see what data was retrieved into the data structure array, and the number of rows retrieved:

> EVAL data
DATA.LIBRARY(1) = 'QSYS      '
DATA.SUBSYSTEM(1) = 'QINTER    '
DATA.ACTIVE(1) = 'ACTIVE    '
DATA.LIBRARY(2) = 'QSYS      '
DATA.SUBSYSTEM(2) = 'QPGMR     '
DATA.ACTIVE(2) = 'INACTIVE  '
DATA.LIBRARY(3) = 'QSYS      '
DATA.SUBSYSTEM(3) = 'QSPL      '
DATA.ACTIVE(3) = 'ACTIVE    '
DATA.LIBRARY(4) = '          '
DATA.SUBSYSTEM(4) = '          '
DATA.ACTIVE(4) = '          '

> EVAL rows
  ROWS = 3

With the results in the data structure array I could write them to a subfile, or some other user interface, to show the user the results.

 

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

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.

2 comments:

  1. Simon, thanks for sharing, another learning moment for us. Great read with great coding examples. Again, thanks for sharing.

    ReplyDelete
  2. Maricel Guanzon LabidiOctober 20, 2021 at 11:58 AM

    thank you for sharing

    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.