New columns added to Job Info table function

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.