Wednesday, November 22, 2017

SQL View for information about Job Queues

using sql db2 for i view to get job queue information

Another of the new Views added to the Db2 for i as part of the new Technology Refreshes, 7.3 TR3 and 7.2 TR7, is one that lists all of the job queues on your IBM i.

The JOB_QUEUE_INFO View will return a row for each job queue. It is essentially the equivalent of the Work with Job Queue command, WRKJOBQ, and the Retrieve Job Queue Information API, QSPRJOBQ. There are over 60 columns of information I can get in my results, don't worry I am not going to list them all here. IBM does a great job of describing them all here, I will only describe the columns I use in my examples.

I will be using the short names for the columns, rather than the long names, to save space in the example code.

My first example returns a list of job queues on the IBM i I am using.

01  SELECT JOBQ,JOBQ_LIB,STATUS,SUB_NAME,MAX_JOBS
02    FROM QSYS2.JOB_QUEUE_INFO
03    ORDER BY SUB_NAME

Line 1: The columns I am selecting are:

  1. JOBQ:  Job queue name
  2. JOBQ_LIB:  Job que library
  3. STATUS:  Job queue status. This will either be HELD or RELEASED
  4. SUB_NAME:  Subsystem that receives jobs from this job queue
  5. MAX_JOBS:  Maximum number of jobs that can be active from this job queue

Line 2: Results from the JOB_USER_INFO View.

Line 3: Return sorted in subsystem name order.

My results are:

JOB_QUEUE_NAME  JOBQ_LIB    STATUS    SUB_NAME   MAX_JOBS
  QBATCH        QGPL        RELEASED  QBATCH            1
  QS36EVOKE     QGPL        RELEASED  QBATCH            1-
  QTXTSRCH      QGPL        RELEASED  QBATCH            1-
  QCTL          QSYS        RELEASED  QCTL              1-
  QZHBHTTP      QHTTPSVR    RELEASED  QHTTPSVR          1-
  QINTER        QGPL        RELEASED  QINTER            1-
  QS36MRT       QGPL        RELEASED  QINTER            1-
  QSPL          QGPL        RELEASED  QSPL              1-
  QIJSSCD       QIJS        RELEASED  QSYSWRK         256
  QSYSNOMAX     QSYS        RELEASED  QSYSWRK           1-
  QUSRNOMAX     QSYS        RELEASED  QUSRWRK           1-
  QBASE         QGPL        RELEASED  -                  -
  QFNC          QGPL        RELEASED  -                  -
  QPGMR         QGPL        RELEASED  -                  -
  QSNADS        QGPL        RELEASED  -                  -
  QSYSSBSD      QSYS        RELEASED  -                  -
  QTCP          QTCP        RELEASED  -                  -

When MAX_JOBS show 1- (-1) means that the maximum number of active jobs was defined as *NOMAX.

The last six job queues have a null value, indicated as -, in the subsystem name as it is not associated with an active subsystem.

The rest of the examples I thought I would show how I could use RPG programs to present users with the information about the job queues. These programs will use subfiles to present the information. I do not want to make this post about subfile programming, these subfiles all use the same technique I did in the post I wrote about Creating a program to show jobs in message wait. I will be using a multiple row fetch to get the data from the SQL View into a data structure, then load the subfile from the data structure. All I am going to show is my "open" procedure that I use to get the data from the View.

In this first example I want to display all of the job queues that are either held or belong to an inactive subsystem.

01  dcl-proc GetData ;
02    clear Data ;
              
03    exec sql DECLARE C0 CURSOR FOR
04              SELECT JOBQ,JOBQ_LIB,STATUS,
05                     IFNULL(SUB_NAME,'Not active')
06                FROM QSYS2.JOB_QUEUE_INFO
07               WHERE STATUS = 'HELD'
08                  OR SUB_NAME IS NULL
09               ORDER BY JOBQ,SUB_NAME ;

10    exec sql OPEN C0 ;

11    exec sql FETCH C0 FOR :NbrOfRows ROWS INTO :Data ;

12    exec sql GET DIAGNOSTICS :RowsFetched = ROW_COUNT ;

13    exec sql CLOSE C0 ;
14  end-proc ;

Line 2: I clear the data structure array that I am fetching the data into.

Lines 3 – 9: The definition of my cursor that defines my fetch.

Lines 4 and 5: I used these columns in my previous example. I have used the IFNULL to convert the null value in the Subsystem name column to something that is more understandable by an user.

Lines 7 and 8: I only want to return the rows where the job queue is either held or in an inactive subsystem.

Line 9: And I want to have resulted sorted by job queue and subsystem name.

Line 11: I get the columns from the rows that match selection criteria, see lines 7 and 8, and receive them into my data structure array.

Line 12: I get the number of rows fetched, I use this to speed up the loading of my subfile.

When my subfile is displayed I see the following:

RPGPGM                    R P G P G M . C O M        TT:TT:TT DD/DD/DD
PGM01                    Job Queues Not Active                DEV730
                                                                      
Job queue  Library    Status   Subsystem 
QBASE      QGPL       RELEASED Not active
QFNC       QGPL       RELEASED Not active
QPGMR      QGPL       RELEASED Not active
QSNADS     QGPL       RELEASED Not active
QSPL       QGPL       HELD     QSPL
QSYSSBSD   QSYS       RELEASED Not active
QTCP       QTCP       RELEASED Not active

In the next example I want the number of jobs in job queues that are in active subsystems. The procedure to get the data from the View looks like.

01  dcl-proc GetData ;
02    clear Data ;

03    exec sql DECLARE C0 CURSOR FOR
04              SELECT JOBQ,SUB_NAME,ACT_JOBS,JOBS,
05                     RLS_JOBS,HELD_JOBS,SCHED_JOBS
06                FROM QSYS2.JOB_QUEUE_INFO
07               WHERE SUB_NAME IS NOT NULL
08               ORDER BY JOBS DESC,JOBQ,SUB_NAME ;

09    exec sql OPEN C0 ;

10    exec sql FETCH C0 FOR :NbrOfRows ROWS INTO :Data ;

11    exec sql GET DIAGNOSTICS :RowsFetched = ROW_COUNT ;

12    exec sql CLOSE C0 ;
13  end-proc ;

The only difference from the previous example is in the definition of the cursor, lines 3 – 8.

I only want to use the following columns:

  1. JOBQ:  Job queue name
  2. SUB_NAME:  Subsystem
  3. ACT_JOBS:  Number of jobs that are active in the subsystem that came from the job queue
  4. JOBS:  Total number of jobs in job queue
  5. RLS_JOBS:  Number of released jobs, i.e. waiting to come "off" the job queue and become active
  6. HELD_JOBS:  Number of held jobs
  7. SCHED_JOBS:  Number of scheduled jobs

Line 7: I am only selecting job queues that are active, which means subsystem column is not null.

Line 8: I want to rows to be retrieved by total number of jobs in the job queue in descending order, job queue name, then subsystem.

The subfile I made looks like:

RPGPGM                    R P G P G M . C O M        TT:TT:TT DD/DD/DD
PGM02                    Job Queues Not Active                DEV730
                                                                      
Job queue  Subsystem   Act  Qed  Rls  Hld  Sch
QBATCH     QBATCH         1   4    3    1    0
QCTL       QCTL           1   0    0    0    0
QIJSSCD    QSYSWRK        3   0    0    0    0
QINTER     QINTER         1   0    0    0    0
QSPL       QSPL           0   0    0    0    0
QSYSNOMAX  QSYSWRK       45   0    0    0    0
QS36EVOKE  QBATCH         0   0    0    0    0
QS36MRT    QINTER         0   0    0    0    0
QTXTSRCH   QBATCH         0   0    0    0    0
QUSRNOMAX  QUSRWRK        2   0    0    0    0
QZHBHTTP   QHTTPSVR       1   0    0    0    0

QBATCH appears first in the subfile as it has four jobs in it. I made the column heading "Qed" to indicate that this was the total of all the jobs queued in the job queue, the column JOBS.

When you submit a job to batch you can enter a job priority for your job on the job queue, JOBPTY keyword, and the JOB_QUEUE_INFO has columns for each type of job by priority. I am going to use the long names as they describe what the columns contain.

  • MAXIMUM_ACTIVE_JOBS_PRIORITY_1 – 9
  • ACTIVE_JOBS_PRIORITY_0 – 9
  • RELEASED_JOBS_PRIORITY_0 – 9
  • SCHEDULED_JOBS_PRIORITY_0 – 9
  • HELD_JOBS_PRIORITY_0 – 9

I am sure some of you might find a use for this information. At present I cannot think of why I would need to drill down to that level of detail.

 

You can learn more about the JOB_QUEUE_INFO View from the IBM website here.

 

This article was written for IBM i 7.3 TR3, but will work for IBM i 7.2 TR7 too.

5 comments:

  1. Sergio L Puentes-ValladaresNovember 22, 2017 at 5:35 PM

    BuenĂ­simo, DB2 es una herramienta muy poderosa y util

    ReplyDelete
  2. Can you help me to fetch user d wo has held the jobq?

    ReplyDelete
  3. Great dashboard data set

    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.