Pages

Tuesday, July 13, 2021

New columns added to Job Info table function

3 new coulns added to job_info

The latest round of Technology Refreshes sees the addition of the same three columns to the JOB_INFO table function as were added to the ACTIVE_JOB_INFO table function.

These new columns break apart the full job name placing its parts into three new columns:

  • JOB_NAME_SHORT
  • JOB_USER
  • JOB_NUMBER

For example I can get the information about my job by leaving out the parameters for the table function:

SELECT JOB_NAME,
       JOB_NAME_SHORT,JOB_USER,JOB_NUMBER 
FROM TABLE(QSYS2.JOB_INFO()) ;

The results are:

                         JOB_NAME_   JOB_   JOB_
JOB_NAME                 _SHORT      USER   NUMBER
-----------------------  ----------  -----  -------
714431/SIMON/QPRTJOB     QPRTJOB     SIMON  714431
892849/SIMON/QPADEV0002  QPADEV0002  SIMON  892849

I can also get the short job name for any jobs on the partition. For example if I wanted to list jobs for any user that was running interactively I can use the JOB_INFO parameters:

SELECT JOB_NAME,JOB_NAME_SHORT,JOB_NUMBER 
FROM TABLE(QSYS2.JOB_INFO(
       JOB_USER_FILTER => '*ALL',
       JOB_TYPE_FILTER => '*INTERACT')) ;

The JOB_USER_FILTER is used to specify the users to return, in this case I want all. The JOB_TYPE_FILTER limits the results to those running interactive.

I am not going to list all of the jobs that were running interactive on this partition, just a few.

                              JOB_NAME_   JOB_
JOB_NAME                      _SHORT      NUMBER
----------------------------  ----------  ------
597933/P*********/QPADEV0004  QPADEV0004  685211
173486/A*********/QPADEV0002  QPADEV0002  611767
716274/O*********/QPADEV0005  QPADEV0005  609607
216231/B*********/QPADEV0004  QPADEV0004  611407

Why would these interest me? If I am looking for a job that is on the partition I can now search for it using the JOB_NAME_SHORT rather than using a wildcard with JOB_NAME:

For example I can check if a job is still in the job queue or whether it is active.

SELECT JOB_NAME_SHORT,JOB_NAME,JOB_TYPE,JOB_STATUS
  FROM TABLE(QSYS2.JOB_INFO(JOB_TYPE_FILTER => '*BATCH'))
 WHERE JOB_NAME_SHORT = 'BJOB34' ;

The results from this statement show me that the job is still waiting in the job queue.

JOB_NAME                       JOB_  JOB_
_SHORT    JOB_NAME             TYPE  STATUS
--------  -------------------  ----  ------
BJOB34    893965/SIMON/BJOB34  BCH   JOBQ

Before if I needed to extract the short job name from the full job name like this:

01  SELECT JOB_NAME,
02         SUBSTR(JOB_NAME,LOCATE_IN_STRING(QSYS2.JOB_NAME,'/',-1)+2),
03         JOB_NAME_SHORT
04    FROM TABLE(QSYS2.JOB_INFO()) ;

Results:

JOB_NAME                 00001       JOB_NAME_SHORT
-----------------------  ----------  --------------
126045/SIMON/QPADEV0002  QPADEV0002  QPADEV0002
126054/SIMON/JOBQ_1      JOBQ_1      JOBQ_1

Using the JOB_NAME_SHORT, or the other two other new columns, is a lot easier than having to use the LOCATE_IN_STRING and substring functions to extract the short job name from the full job name, see line 2 of the SQL previous statement.

A small change, but something that will make my statements quicker to execute in the future.

 

You can learn more about the changes to the JOB_INFO SQL table function from the IBM website here.

 

This article was written for IBM i 7.4 TR4 and 7.3 TR10.

No comments:

Post a Comment

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.