Getting Active Jobs data using SQL

With IBM i 7.2 released a whole lot of useful Views and Table functions that allowed us to gather, select, and view data that had otherwise only been available via a command or API. I have written about some of them, and today I am going to do so for another: ACTIVE_JOB_INFO, which basically gives me the save information as the Work With Active Jobs command, WRKACTJOB.

ACTIVE_JOB_INFO, found in library QSYS2, is a Table Function, therefore, it used in a slightly different way to a View. Perhaps the most obvious difference is that the Table Function has parameters, where a View does not.

ACTIVE_JOB_INFO has four optional parameters. I have listed them below with their equivalent in the Work Active Job command:

ACTIVE_JOB_INFO Table Function parameter	Parameter description	WRKACTJOB command parameter
RESET_STATISTICS	If YES restarts the statistics. If NO or not given continue from previous reset.	Reset status statistics (RESET)
SUBSYSTEM_LIST_FILTER	List of up to 25 subsystems can be given. If not used all subsystems is assumed.	Subsystem (SBS)
JOB_NAME_FILTER	What kinds of job(s) are to be displayed. If not given all jobs and types of jobs is assumed.	No equivalent
CURRENT_USER_LIST_FILTER	Up to 10 user profiles can be given. If not given all users is assumed.	No equivalent

One of the most common reasons I use WRKACTJOB is when the systems goes slow I need to find which jobs are using up all the CPU. So let me list all of the jobs with:

• JOB_NAME - Full job name
• JOB_TYPE - Type of job
• JOB_STATUS - Status of the job
• SUBSYSTEM - Subsystem the job is running in
• ELAPSED_CPU_PERCENTAGE - Percentage of CPU used

01 SELECT JOB_NAME,JOB_TYPE,JOB_STATUS,SUBSYSTEM, 02 ELAPSED_CPU_PERCENTAGE AS PERCENT 03 FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL')) A 04 ORDER BY ELAPSED_CPU_PERCENTAGE DESC

Lines 1 and 2: All of the columns I want are listed here. On line 2 for the column ELAPSED_CPU_PERCENTAGE I have used the AS to change the column heading to display PERCENT.

Line 3: This is where the FROM part of the statement where, normally, a Table's or View's name would be given. As this is a Table Function it is given in parentheses. In this statement I want all jobs I have used the JOB_NAME_FILTER parameter. Notice that I do not use just = (equal) before the comparison value, in my experience with Table Functions I have found I have to use => (equal or greater). The A has to be given at the end of the line, not because I am going to qualify the column names, without it I get a syntax error. I did not have to use A, I could have used any alphabetic character.

Line 4: This is where I give the sort order. In this case I want to sort by the CPU percentage in descending order. So that the biggest users of CPU will appear at the top of the results.

This gives me:

JOB_NAME JOB_TYPE JOB_STATUS SUBSYSTEM PERCENT 059455/USER1/BAD_JOB INT RUN QINTER 70.8 061849/QUSER/QZRCSRVS PJ RUN QUSRWRK 5.7 059455/SIMON/TEST BCH RUN QBATCH 3.1 059371/QSYS/QBATCH SBS DEQW QBATCH 1.3

I also use the WRKACTJOB to look for errors. I can also easily get this information using ACTIVE_JOB_INFO.

01 SELECT JOB_NAME,JOB_TYPE,JOB_STATUS,SUBSYSTEM 02 FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) B 03 WHERE JOB_STATUS = 'MSGW' 04 ORDER BY JOB_NAME

Line 2: As I have not given any parameters all jobs is assumed.

Line 3: I am only interested in those jobs in a Message Wait status, which is normally means it has errored.

Line 4: I want my results sorted in Job name order.

Which gives me all of those jobs in Message Wait status:

JOB_NAME JOB_TYPE JOB_STATUS SUBSYSTEM 061786/USER2/BAD_JOB2 BCH MSGW QINTER 061787/USER3/BAD_JOB3 BCH MSGW QINTER

In my last example I am going to list all of the active jobs I have:

01 SELECT JOB_NAME,JOB_TYPE,JOB_STATUS,SUBSYSTEM, 02 CPU_TIME 03 FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) C 04 WHERE JOB_NAME LIKE '%SIMON%' 05 ORDER BY CPU_TIME DESC

This time I want to know the amount CPU time each job has taken, I can get this from the column CPU_TIME.

Line 4: I have used the LIKE so that if SIMON appears anywhere in the JOB_NAME column the row will be selected.

The above statement gives me:

JOB_NAME JOB_TYPE JOB_STATUS SUBSYSTEM CPU_TIME 061890/SIMON/QPADEV0001 INT RUN QINTER 204 061858/SIMON/QPADEV0002 INT DSPW QINTER 46

What could I do with this information? I could create a program that would determine if there were any errors and which jobs were in message wait. Taking what I described in the post SQL blocking fetches, getting more than one row at a time I can write a SQL RPG program to use ACTIVE_JOB_INFO and retrieve any rows in MSGW, and display a count of those columns.

01 dcl-ds Errors qualified dim(100) ; 02 JobName char(28) ; 03 Subsystem char(10) ; 04 JobType char(3) ; 05 JobStatus char(4) ; 06 end-ds ; 07 dcl-s wkMaxRows packed(3) inz(%elem(Errors)) ; 08 dcl-s wkRtvRows like(wkMaxRows) ; 09 exec sql DECLARE C0 CURSOR FOR SELECT JOB_NAME,SUBSYSTEM,JOB_TYPE, JOB_STATUS FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) X WHERE JOB_STATUS = 'MSGW' FOR READ ONLY ; 10 exec sql OPEN C0 ; 11 exec sql FETCH NEXT FROM C0 FOR :wkMaxRows ROWS INTO :Errors ; 12 wkRtvRows = SQLER3 ; 13 exec sql CLOSE C0 ; 14 if (wkRtvRows > 0) ; 15 dsply (%char(wkRtvRows) + ' errors found') ; 16 endif ;

Lines 1 – 6: This is the data structure array, Errors, that will contain the columns I care about from the rows retrieved.

Line 7: wkMaxRows will contain the maximum number of rows that can be retrieved as one time, which happens to be the number of elements in the data structure array.

Line 8: wkRtvRows will contain the number or rows retrieved from ACTIVE_JOB_INFO.

Line 9: Here I am defining the cursor for the SELECT statement I want. Which is for all rows where JOB_STATUS is Message Wait.

Line 10: I open the cursor.

Line 11: The FETCH retrieves the first group of rows, the maximum number of rows to be retrieved is given in variable wkMaxRows. The columns I defined on line 9 are copied from the rows that satisfy the criteria in the cursor definition into the data structure array, Errors.

Line 12: The number of rows retrieved is moved to wkRtvRows.

Line 13: The cursor is closed.

Line 14 – 16: If any rows were retrieved then I use the DSPLY operation code to display the count.

When the program displays this on my screen:

DSPLY 3 errors found

And my data structure array contains the details for the jobs in message wait:

ERRORS.JOBNAME(1) = '061858/SIMON/QPADEV0002 ' ERRORS.SUBSYSTEM(1) = 'QINTER ' ERRORS.JOBTYPE(1) = 'INT' ERRORS.JOBSTATUS(1) = 'MSGW' ERRORS.JOBNAME(2) = '061915/USER1/QPADEV0005 ' ERRORS.SUBSYSTEM(2) = 'QINTER ' ERRORS.JOBTYPE(2) = 'INT' ERRORS.JOBSTATUS(2) = 'MSGW' ERRORS.JOBNAME(3) = '061844/USER1/TEST14 ' ERRORS.SUBSYSTEM(3) = 'QBATCH ' ERRORS.JOBTYPE(3) = 'BCH' ERRORS.JOBSTATUS(3) = 'MSGW'

I am sure you can elaborate on the code above and create yourself a better program for your use.

 

You can learn more about on the IBM website, including a list of all the available columns, here

 

This article was written for IBM i 7.2.