New View for listing jobs within job queues

Included as part of the recent Technology Refreshes, IBM i TR3 and 7.4 TR9, comes a new Db2 for i, or SQL, View that will return all the jobs that are submitted to job queues.

I have written in the past about how to find a job that has been submitted to batch. This new View makes it much easier to find this information.

I have a couple of examples of the kind of information I want to find about submitted jobs. But before I talk about those I recommend that you run the following statement to see what information is returned. As there is, IMHO, a lot of useful information in the columns of this View.

01 SELECT * 02 FROM SYSTOOLS.JOB_QUEUE_ENTRIES 03 LIMIT 20

The IBM i partition is not a production partition, and it appears it is used by only a very few people. Let me start by looking to see which job queues have jobs within them. Here I would use the JOB_QUEUE_INFO to get the information I want:

01 SELECT JOB_QUEUE_LIBRARY AS "Library", 02 JOB_QUEUE_NAME AS "Jobq", 03 JOB_QUEUE_STATUS AS "Status", 04 NUMBER_OF_JOBS AS "Jobs" 05 FROM QSYS2.JOB_QUEUE_INFO 06 WHERE NUMBER_OF_JOBS > 0

Line 1: I want the library the job queue is in.

Line 2: Job queue's name.

Line 3: Its status.

Line 4: How many jobs are within it?

Line 6: I only want to list the job queues that have at least one job in them.

My results are:

Library Jobq Status Jobs ------- ------- ------ ----- QGPL QBATCH HELD 10 QGPL QBATCH2 HELD 5

There are only two jobs queues with jobs, and I am just interested in the job queue QBATCH2.

The first that comes to mind are what are those five jobs?

01 SELECT JOB_QUEUE_STATUS AS "Status", 02 JOB_NAME AS "Full job name", 03 JOB_TYPE_ENHANCED AS "Job type", 04 JOB_ENTERED_SYSTEM_TIME AS "When submitted", 05 SUBMITTER_JOB_NAME AS "Submitted by" 06 FROM SYSTOOLS.JOB_QUEUE_ENTRIES 07 WHERE JOB_QUEUE_LIBRARY = 'QGPL' 08 AND JOB_QUEUE_NAME = 'QBATCH2'

Below are the results:

Job sts Full job name Job type When submitted Submitted by -------- ------------------- -------- ------------------- ------------------ RELEASED 669610/SIMON/WAIT_1 BATCH 2023-12-31 07:10:38 669600/SIMON/DSP01 RELEASED 669611/SIMON/WAIT_2 BATCH 2023-12-31 07:13:15 669600/SIMON/DSP01 RELEASED 669612/SIMON/WAIT_3 BATCH 2023-12-31 07:17:30 669600/SIMON/DSP01 RELEASED 669613/SIMON/WAIT_4 BATCH 2023-12-31 07:22:45 669600/SIMON/DSP01 RELEASED 669614/SIMON/WAIT_5 BATCH 2023-12-31 07:24:14 669600/SIMON/DSP01

The job status refers to the job in the job queue, not the status of the job queue.

There are many different job types I am not going to list them all here. Go to the IBM documentation, that I will link to at the bottom of this post, to discover them all.

Next scenario I often have is whether a particular job is sitting on a job queue waiting to run. For this example I want to find everywhere the job 'WAIT_2' is found. My statement would be:

01 SELECT A.JOB_QUEUE_LIBRARY AS "Library", 02 A.JOB_QUEUE_NAME AS "Jobq", 03 B.JOB_QUEUE_STATUS AS "Jobq sts", 04 A.JOB_QUEUE_STATUS AS "Job sts", 05 A.JOB_NAME AS "Full job name", 06 A.JOB_ENTERED_SYSTEM_TIME AS "When submitted", 07 A.SUBMITTER_JOB_NAME AS "Submitted by" 08 FROM SYSTOOLS.JOB_QUEUE_ENTRIES A 09 JOIN QSYS2.JOB_QUEUE_INFO B 10 ON (A.JOB_QUEUE_LIBRARY,A.JOB_QUEUE_NAME) = (B.JOB_QUEUE_LIBRARY,B.JOB_QUEUE_NAME) 11 AND A.JOB_NAME_SHORT = 'WAIT_2'

Line 1: I want the library the job queue is in.

Line 2: Job queue's name.

Line 3: Job queue's status, that I need to get from the JOB_QUEUE_INFO View, which is why this column is prefixed with a 'B'.

Line 4: Job on the job queue's status.

Line 5: The full job name of the job on the job queue.

Line 6: When the job was submitted to the job queue.

Line 7: Which job submitted the job.

Lines 8 and 9: I need columns from the JOB_QUEUE_ENTRIES, all prefixed with 'A', and one column from the JOB_QUEUE_INFO, prefixed with 'B'.

Line 10: The two Views are joined using the job queue library and name columns.

Line 11: And I am only wanting rows where the short job name, the job name itself, is 'WAIT_2'.

There is only one result:

Library Jobq Jobq sts Job sts Full job name ------- ------- -------- -------- ------------------- QGPL QBATCH2 HELD RELEASED 669611/SIMON/WAIT_2 When submitted Submitted by ------------------- ------------------ 2023-12-31 07:13:15 669600/SIMON/DSP01

This shows me that the job is on the QBATCH job queue, and the job queue is held.

I am going to use the JOB_QUEUE_ENTRIES to be able to monitor the number of jobs in job queues, which jobs in those job queues are held and released, and whether a particular job is sitting in a job queue waiting its turn to run. All good and useful stuff.

 

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

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.