Workload Group shortcut added to Active Job Info

One of the SQL table functions I use the most is the ACTIVE_JOB_INFO, which is used to list all of the active jobs in any partition.

In IBM i TR4 and 7.3 TR10 a new parameter, DETAILED_INFO, was added to the Table Function. This controlled the number of columns that were returned in the results. When it was introduced there were the following options

-- Default SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) ; -- All columns returned SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) ; -- QTEMP info returned only SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'QTEMP')) ; -- Minimum info SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'NONE')) ;

The fewer columns returned in the results by each option the faster statement runs. This makes the NONE option the fastest.

In IBM i 7.5 and 7.4 TR6 another DETAILED_INFO option was added, WORK. This returns the workload group information, previously this information was only available using the ALL option.

-- Workload group info SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'WORK')) ;

Columns returned by Detailed Info WORK, and not in the NONE results are:

• JOB_DESCRIPTION
• JOB_QUEUE_NAME
• JOB_QUEUE
• OUTPUT_QUEUE_LIBRARY
• OUTPUT_QUEUE
• CCSID
• DEFAULT_CCSID
• SORT_SEQUENCE
• LANGUAGE_ID
• DATE_FORMAT
• DATE_SEPERATOR
• TIME_SEPERATOR
• DECIMAL_FORMAT
• JOB_TYPE_ENHANCED
• JOB_ENTERED_SYSTEM
• JOB_ACTIVE_TIME

For a full explanation of these columns click on the link here.

I have written a program for the System Admins that displays all of the jobs in message wait from multiple partitions in one subfile. For that I will still be using the NONE option as it remains faster than the rest.

SELECT JOB_NAME,SUBSYSTEM,AUTHORIZATION_NAME, JOB_TYPE,JOB_STATUS,FUNCTION,CPU_TIME FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO=>'NONE') WHERE JOB_STATUS = 'MSGW' ORDER BY ORDINAL_POSITION

 

This article was written for IBM i 7.5 and 7.4 TR6.