Pages

Thursday, July 8, 2021

Additions to the Active Job Info table function

changes to active_job_info

In the past few Technology Refreshes there have been additions to one of my favorite Db2 for i table functions. I use the ACTIVE_JOB_INFO a lot, I have even created a program to display jobs in message wait in a subfile using it.

The first change I am going to show makes it easier to search for all jobs that have the same job name. Three new columns have been added, "breaking apart" the full job name into its individual parts:

SELECT JOB_NAME,JOB_NAME_SHORT,JOB_USER,JOB_NUMBER
  FROM TABLE(ACTIVE_JOB_INFO()) ;

Which returns to me:

                         JOB_NAME            JOB_
JOB_NAME                 _SHORT    JOB_USER  NUMBER
-----------------------  --------  --------  ------
890015/QSYS/#SYSLOAD     #SYSLOAD  QSYS      890015
890016/#SYSLOAD/SYSLOAD  SYSLOAD   #SYSLOAD  890016
673007/QSYS/QBATCH       QBATCH    QSYS      673007

In the future if I need to determine if a certain job is active I no longer have to use a wildcard statement with the job name. I could just do, which I am sure will return the results faster to me:

SELECT *
  FROM TABLE(ACTIVE_JOB_INFO()) 
 WHERE JOB_NAME_SHORT = 'SYSLOAD' ;

The next addition is a column that tells me how many open files the job has.

SELECT OPEN_FILES
  FROM TABLE(ACTIVE_JOB_INFO()) ;

Alas when I run this statement I returned nulls.

OPEN_FILES
----------
    <NULL>
    <NULL>
    <NULL>

Why am I getting nulls returned? This is due to another enhancement to ACTIVE_JOB_INFO. A new parameter has been added to the table function which controls the results returned.

SELECT *
FROM TABLE(ACTIVE_JOB_INFO(DETAILED_INFO => value)) ;

The possible values are:

  • NONE:  Only returns the "general information", this makes getting the results faster than before. This is the default
  • ALL:  Returns all information
  • QTEMP:  Returns the "general information" and the QTEMP_SIZE column, more on that later

The OPEN_FILES column is not in the general information, therefore, I need to change my statement to be:

SELECT JOB_NAME,OPEN_FILES
  FROM TABLE(ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) 
 WHERE OPEN_FILES > 0 
 ORDER BY 2 DESC ;

In the ORDER BY clause I am being lazy. Rather than giving the column's name, OPEN_FILES, I am giving its position in the results. The results are:

JOB_NAME               OPEN_FILES
---------------------  ----------
881751/QSYS/CRTPFRDTA          47
672937/QSYS/QDBSRVXR           38
672941/QSYS/QDBSRVXR2          15

I have to admit I do not remember what all of the job types mean. I can remember the basic ones I encounter every day, but there are some that still having me looking up what it means. A new column, JOB_TYPE_ENHANCED, has been added that gives the description of the job type.

SELECT JOB_TYPE,JOB_TYPE_ENHANCED
FROM TABLE(ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) ;

I will not have to look up what the job types mean again.

JOB_TYPE  JOB_TYPE_ENHANCED
--------  -----------------
SBS       SUBSYSTEM
ASJ       AUTOSTART
BCH       BATCH
PJ        PRESTART_COMM
BCI       BATCH_IMMEDIATE
WTR       WRITER

The last addition is a column, QTEMP_SIZE, that contains the size in megabytes of any job's QTEMP library. Here is where I need to use the QTEMP value on the DETAILED_INFO parameter.

SELECT JOB_NAME,QTEMP_SIZE
  FROM TABLE(ACTIVE_JOB_INFO(DETAILED_INFO => 'QTEMP'))
 WHERE QTEMP_SIZE <> 0
 ORDER BY QTEMP_SIZE DESC ;

The results show:

JOB_NAME                 QTEMP_SIZE
-----------------------  ----------
874688/QUSER/QZDASOINIT          18
673035/QUSER/QNPSERVR             1
881752/QTMHHTTP/QHTTP             1
887944/QUSER/QNPSERVS             1
887945/QUSER/QNPSERVS             1

All of the above are great enhancements to this table function, and just add to its value.

 

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

 

This article was written for IBM i 7.4 TR4, and will work for 7.3 TR10 too.

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.