Wednesday, March 10, 2021

Check if submitted job is still on job queue

Find job on jobq with SQL

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.

10 comments:

  1. That's interesting. I wonder what the job status is if the job is transitioning from one state to another like *JOB to *ACTIVE (CPF3C54 - Job &3/&2/&1 currently not available). Thanks.

    Ringer

    ReplyDelete
  2. Vraiment pratique

    ReplyDelete
  3. Found an issue with the script finding all jobs in a given job queue
    SELECT SUBSTR(JOB_NAME,LOCATE_IN_STRING(QSYS2.JOB_NAME,'/',-1)+2)
    AS "Job"
    You are sub stringing Job_Name using the / found in QSYS2.JOB_NAME. It was using my job information to find the second / which is always 13 and substring the Job_name in the job queue. Producing the below substrings.
    F/OFFSHPLD
    SBMLSBUPC
    SBMLSBUPC
    RE/OFFSHPLD.
    I changed it to
    SUBSTR(JOB_NAME,LOCATE_IN_STRING(JOB_NAME,'/',-1)+1) that fixed the issue.
    OFFSHPLD
    SBMLSBUPC

    ReplyDelete
  4. Thanks Simon, valuable info although would be nice to trap the parameters passing to those jobs in the job queue.

    ReplyDelete
  5. thanks for this tutorial.

    ReplyDelete
  6. Muy buenos comentarios, manos a la obra !

    ReplyDelete
  7. Thank you for this. This is very informative. I especially like how you show the SQL code as well as the code to embed it into RPG. Btw, since the job number is a fixed length, I think it would be easier to read using: SUBSTR(JOB_NAME,LOCATE_IN_STRING(QSYS2.JOB_NAME,'/',8)+1)

    ReplyDelete
  8. Perhaps JOB_INFO has been enhanced since this article was first written but I see separate columns for JOB_NAME_SHORT, JOB_USER, and JOB_NUMBER so there is no need to use substring of JOB_NAME to get an individual part of it. I'm on 7.3 TR12.

    ReplyDelete
    Replies
    1. JOB_INFO was enhanced and the columns you mention were added.

      Delete

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.