Check if submitted job is still on job queue

The question was: How can I check if job is still on the job queue and if it is held?

Fortunately the solution is simple using SQL. I can check a specific job queue or all for the job. The gotcha is that the job name does need to be unique. If the job was submitted to batch with the default job name, which is the user profile of the job that submitted it, it will be hard to determine which job is the one I want. But as I always submit jobs with a distinct name it is easy. In this example I want to check for a job called TRIAL_BAL that is submitted to the QPGMR job queue, which is in the QGPL library.

I have given examples of using the JOB_INFO SQL Table function in a previous post, but I did not write about how I can use it to get details of jobs waiting in job queues.

JOB_INFO Table function has a parameter called JOB_STATUS-FILTER that can be used to limit the type of jobs returned in the results. When *JOBQ is used it will return all of the jobs waiting on job queues.

If I use this SQL statement I get every job that is waiting on a job queue returned in the results:

SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ')) ;

I think it is a good idea just to execute this statement for yourself to see what information is returned. In the example I writing about I am only interested in a few of those columns:

• JOB_ENTERED_SYSTEM_TIME:  Timestamp when the job was submitted to the job queue
• JOB_NAME:  Full IBM i job name
• JOB_TYPE_ENHANCED:  Type if job as a full description, rather than the three character abbreviation
• JOB_QUEUE_LIBRARY:  Library the job queue is in
• JOB_QUEUE_NAME:  Name of the job queue
• JOB_QUEUE_STATUS:  Status of the job on the job queue

If I want to list every job waiting on a job queue I can just use this:

01 SELECT SUBSTR(JOB_NAME,LOCATE_IN_STRING(QSYS2.JOB_NAME,'/',-1)+2) AS "Job", 02 JOB_TYPE_ENHANCED AS "Type", 03 JOB_NAME AS "Job name", 04 JOB_ENTERED_SYSTEM_TIME AS "Sbm time", 05 JOB_QUEUE_LIBRARY AS "Library", 06 JOB_QUEUE_NAME AS "Job Q", 07 JOB_QUEUE_STATUS AS "Status" 08 FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ')) ;

Line 1: I did not mention this column when I listed the ones I was interested in. This will extract just the job name part of the Job Name using the LOCATE_IN_STRING. I went into this in greater detail in an earlier post.

Line 8: As the JOB_STATUS_FILTER is equal to *JOBQ this Table function only returns the jobs waiting in job queues.

The results are:

Job Type Job name Sbm time --------- ----- ----------------------- ------------------- TEST_JOB BATCH 630306/SIMON/TEST_JOB 2021-03-09 17:30:24 TRIAL_BAL BATCH 630695/SIMON/TRIAL_BAL 2021-03-09 17:52:56 Library Job Q Status ------- ----- -------- QGPL QPGMR HELD QGPL QPGMR RELEASED

As the partition I am using is for testing, and the time of day I am writing the code for this post there are only two submitted jobs. I submitted both to the same job queue, and I held the first job, TEST_JOB.

If I wanted to list the jobs waiting in one job queue I would change the above statement to become:

01 SELECT SUBSTR(JOB_NAME,LOCATE_IN_STRING(QSYS2.JOB_NAME,'/',-1)+2) AS "Job", 02 JOB_TYPE_ENHANCED AS "Type", 03 JOB_NAME AS "Job name", 04 JOB_ENTERED_SYSTEM_TIME AS "Sbm time", 05 JOB_QUEUE_LIBRARY AS "Library", 06 JOB_QUEUE_NAME AS "Job Q", 07 JOB_QUEUE_STATUS AS "Status" 08 FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ')) 09 WHERE JOB_QUEUE_LIBRARY = 'QGPL' 10 AND JOB_QUEUE_NAME = 'QPGMR' ;

Or find a specific job in any job queue:

01 SELECT SUBSTR(JOB_NAME,LOCATE_IN_STRING(QSYS2.JOB_NAME,'/',-1)+2) AS "Job", 02 JOB_TYPE_ENHANCED AS "Type", 03 JOB_NAME AS "Job name", 04 JOB_ENTERED_SYSTEM_TIME AS "Sbm time", 05 JOB_QUEUE_LIBRARY AS "Library", 06 JOB_QUEUE_NAME AS "Job Q", 07 JOB_QUEUE_STATUS AS "Status" 08 FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ')) 09 WHERE JOB_NAME LIKE '%TRIAL_BAL' ;

Below is a RPG program that will show me if the job TRIAL_BAL is on the job queue I want, and the status of the job.

01 **free 02 dcl-s wkStatus char(10) ; 03 exec sql 04 SELECT LOWER(JOB_QUEUE_STATUS) INTO :wkStatus 05 FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ')) 06 WHERE JOB_QUEUE_LIBRARY = 'QGPL' 07 AND JOB_QUEUE_NAME = 'QPGMR' 08 AND JOB_NAME LIKE '%TRIAL_BAL' 09 FETCH FIRST ROW ONLY ; 10 if (wkStatus = ' ') ; 11 dsply ('Job is not in job queue') ; 12 else ; 13 dsply ('Job is in job queue & is ' + wkStatus) ; 14 endif ; 15 *inlr = *on ;

Line 1: All my code is always totally free format RPG.

Line 2: I am defining this variable to contain the status of the job on the job queue.

Lines 3 – 9: The SQL statement to tell me whether my job is on the job queue, and what status it has.

Line 3: If the job is found and returned in the results then the JOB_QUEUE_STATUS value is placed in the variable wkStatus. If the job is not found then wkStatus will remain blank. I am using the LOWER function to change the case of the result from upper case to lower case. You will see why later in the program.

Lines 6 – 8: I am only looking in the job queue QPGMR in the library QGPL for the job TRIAL_BAL.

Line 9: I am using the FETCH FIRST ROW ONLY to only return the first result from my Select statement. In this example I know that the job will only be on the job queue once. If the job could be on the job queue multiple times then this will only return the first result and ignore the others. Why have I done this rather than, let's say, a COUNT(*)? This is faster as when if finds the first result the statement ends without checking the rest of the results.

Lines 10 and 11: As I said above if wkStatus is blank then there is no job called TRIAL_BAL in the job queue.

DSPLY Job is not in job queue

Lines 12 and 13: If there is a job of that name then I append the returned job status to the string to display. When you see the results you will understand why I translated the status to lower case.

DSPLY Job is in job queue & is released DSPLY Job is in job queue & is held

If there is more than one job with the job name I am interested in then I would need to do multi row fetch to get a list of all the jobs with that name.

A simple solution to the question I was asked. One that could easily be enhanced to send a message to the system operator or an email to interested people that there is a problem.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.