Wednesday, November 30, 2022

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.

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.