Using SQL to determine if subsystem is started

The question came in two parts:

1. Is it possible to know whether a subsystem has been started?
2. If it is started how can I determine the number of jobs running in it?

To answer these questions I decided to use a Db2 for i table function I have written about before ACTIVE_JOB_INFO.

One of the parameters that can be used with ACTIVE_JOB_INFO is one that narrows the results to just all active subsystems. If I wanted to know if QINTER subsystem was active I could use the following SQL statement in my favorite SQL client:

SELECT SUBSYSTEM,JOB_NAME,JOB_STATUS FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*SBS')) A WHERE SUBSYSTEM = 'QINTER'

The names of the columns I have selected for the results explain themselves. When I run this statement the result returned will look like:

SUBSYSTEM JOBNAME JOB_STATUS QINTER 999999/QSYS/QINTER DEQW

If this statement is performed for a subsystem that is not started no results are returned.

To answer the second question I can use the same table function and return a count of the number of rows returned, as each row is an active job.

SELECT COUNT(*) "NBR OF JOBS" FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) A WHERE SUBSYSTEM = 'QINTER'

When I ran this statement there were 11 jobs active in QINTER, therefore, my result was:

NBR OF JOBS 11

By all means the examples above answer the questions. What about making this better, and put these statements into a program to return the results to me whenever I call it. I came up with two programs the first to be used for just one, or maybe a very small number of subsystems, and the second to be used for all subsystems.

This is the start of the first program:

01 **free 02 ctl-opt option(*srcstmt) dftactgrp(*no) ; 03 dcl-s Subsystem char(10) ; 04 dcl-s SbsActive ind ; 05 dcl-s SbsJobs uns(10) ; 06 Subsystem = 'QINTER' ; 07 ChkSbsSts() ; 08 DspMsg() ; 09 Subsystem = 'INACTIVE' ; //Not a real subsystem name 10 ChkSbsSts() ; 11 DspMsg() ;

Line 1: I only use totally free RPG, in 2019 why would I use any other form of the language?

Line 2: In this program I only need two control options. *SRCSTMT means that the compiled program will use the source sequence numbers for its internal sequence numbers, this makes it a lot easier to find the line that errored in a program. I need the DFTACTGRP as I will be calling subprocedures within this program,.

Line 3 – 5: These are the variables that will be used in the program.

Lines 6 - 8: First I want to know whether QINTER is started, which is subprocedure ChkSbsSts, and then format the results returned from that subprocedure to make a meaningful message for the user, subprocedure DspMsg.

Lines 9 – 11: The same is performed for a subsystem that is not started.

The ChkSbsSts is where the "magic" is performed:

12 dcl-proc ChkSbsSts ; 13 SbsActive = *off ; 14 SbsJobs = 0 ; 15 exec sql SELECT '1',COUNT(B.SUBSYSTEM) 16 INTO :SbsActive,:Sbsjobs 17 FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*SBS')) A 18 LEFT OUTER JOIN TABLE(QSYS2.ACTIVE_JOB_INFO()) B 19 ON A.SUBSYSTEM = B.SUBSYSTEM 20 WHERE A.SUBSYSTEM = :Subsystem ; 21 end-proc ;

Lines 13 and 14: I am moving default values to these variables. If the following SQL statement was to fail then these values would be the ones returned.

Rather than performing two separate SQL Select statements I have combined the two I showed earlier into one.

Lines 15 and 16: If the SQL statement is successful, i.e. the subsystem is started, the '1' will be placed in the host variable SbsActive and the count of active jobs in the subsystem into SbsJobs.

Line 17: Just gets the result for the subsystem, as I showed in the first example.

Line 18: I have used a Left Outer Join to join that to the table function for all active jobs. I am using the Left Outer Join because if the subsystem is started but there are no jobs in the subsystem I will still return a result for the count of active jobs, zero. If I had used a regular join then I would have got no result returned as there is nothing return from the second part of the join.

Line 19: The two table functions are joined using the subsystem name column in both.

Line 20: I only want the results for the subsystem name in the host variable Subsystem.

When control is passed back to the main body part of the program the second subprocedure, DspMsg, is called.

22 dcl-proc DspMsg ; 23 dcl-s Msg char(50) ; 24 if (SbsActive) ; 25 Msg = %trimr(Subsystem) + ' is active with ' + %trim(%char(SbsJobs)) + ' jobs' ; 26 else ; 27 Msg = %trimr(Subsystem) + ' is inactive' ; 28 endif ; 29 dsply (Msg) ; 30 end-proc ;

This procedure is just to make a message that is then displayed using RPG's DSPLY operation code. I am only including this subprocedure as this is a test program. In a production scenario I would not have this routine.

Line 23: By defining the variable Msg within the subprocedure means that is "local", can only be used within this subprocedure only.

When I run this program the following is displayed.

DSPLY QINTER is active with 11 jobs DSPLY INACTIVE is inactive

In the second program I want to have a list of all the started subsystems, and the number of jobs within each one. This example just loads a data structure array with the information. I could load this into a subfile, format the data into a HTML table and send it in an email, insert the data into a table or file, etc.

I am going to show the program in two parts, first the definitions.

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-ds Data qualified dim(999) ; 04 Subsystem char(10) ; 05 Jobs int(5) ; 06 end-ds ; 07 dcl-s Rows packed(4) ;

Lines 3 – 6: This is the definition of my data structure array. The data structure has two subfields for the subsystem name and the number of jobs running in it.

The next part of the program is where the data is returned from the table functions and loaded into the data structure array.

08 Rows = %elem(Data) ; 09 exec sql DECLARE C0 CURSOR FOR 10 SELECT A.SUBSYSTEM,COUNT(B.SUBSYSTEM) 11 FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*SBS')) A 12 LEFT OUTER JOIN TABLE(QSYS2.ACTIVE_JOB_INFO()) B 14 ON A.SUBSYSTEM = B.SUBSYSTEM 15 GROUP BY A.SUBSYSTEM 16 ORDER BY A.SUBSYSTEM 17 FOR READ ONLY ; 18 exec sql OPEN C0 ; 19 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ; 20 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 21 exec sql CLOSE C0 ;

Line 8: I am initializing the variable Rows with the number of elements in the data structure array. By using the %ELEM built in function I can change the number of elements in the array, and not need to change this line of code. This will be used in the SQL Select statement to tell Db2 how many rows to return.

Lines 9 – 17: Is the definition of the SQL cursor that will be used to retrieve the results from the table functions.

Line 10: I only want to retrieve the subsystem name and the count of the number of jobs running in it.

Lines 11 – 14: Is the same as the previous example. Left out joining the table function statements on the subsystem name.

Line 15: GROUP BY is like subtotaling or a level break. By using it I will get a count of just the jobs for each subsystem.

Line 16: I want my data structure array to list the subsystems in ascending order.

Line 17: I always add this so Db2 knows that the cursor will not be used for insert or update.

Line 18: The cursor is opened.

Line 19: This is where I use the Rows variable to inform Db2 how many rows of results to retrieve and place in the data structure array, Data.

Line 20: I am using GET DIAGNOSTICS to retrieve the number of rows fetched. This allows the next part of program to know how many elements of the data structure array need to be "read".

Line 21: Close the cursor.

If I use debug to look at the contents of the data structure array after the cursor has been closed I see the following:

DATA.SUBSYSTEM(1) = 'QBATCH ' DATA.JOBS(1) = 3 DATA.SUBSYSTEM(2) = 'QCMN ' DATA.JOBS(2) = 1 DATA.SUBSYSTEM(3) = 'QCTL ' DATA.JOBS(3) = 2 DATA.SUBSYSTEM(4) = 'QHTTPSVR ' DATA.JOBS(4) = 18 DATA.SUBSYSTEM(5) = 'QINTER ' DATA.JOBS(5) = 11 DATA.SUBSYSTEM(6) = 'QPGMR ' DATA.JOBS(6) = 1

This is only a subset of the contents of the data structure array.

As I said before I can take these results and format them in any way I desire.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.