Subsystem status using SQL

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.