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.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi,

    we are on 7.0.
    is there any api that receive this information too?
    regards

    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.