SQL View for information about Job Queues

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.