Additions to the Active Job Info table function

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.