Wednesday, November 25, 2015

Getting Active Jobs data using SQL

active_job_info table function wrkactjob

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.

2 comments:

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.