QSYS2.ACTIVE_JOB_INFO Service Enhancements: QTEMP_SIZE and Much More

Several enhancements to IBM i services were announced with IBM i 7.3 TR5 and IBM i 7.2 TR9. The Db2 Group PTF, which includes these enhancements, was released on September 14th.

The following IBM i Services are new:

• QSYS2.JOB_DESCRIPTION_INFO
• QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC

And the following services have been enhanced:

• QSYS2.ACTIVE_JOB_INFO()
• QSYS2.NETSTAT_INFO
• QSYS2.NETSTAT_JOB_INFO

The most interesting updates are for the QSYS2.ACTIVE_JOB_INFO service.

The QSYS2_ACTIVE_JOB_INFO service has been extended with a new, optional parameter called DETAILED_INFO. The default does not return detailed information, but you can specify DEATILED_INFO => ‘ALL’ to return many more columns about your active jobs.

The DETAILED_INFO parameter has three options:

• Return NONE of the additional information (the default)
• Return ALL of the additional information
• Return QTEMP_SIZE in addition to the general information

I’ve written in the past about finding the size of QTEMP for a job in these blog posts: Determining the Size of QTEMP and QTEMP - Temporary or Permanent Storage?  If you review those blogs, you’ll find a number of ways to determine the size of QTEMP for a job, but none of them are very easy.  This has now changed.

Using the QSYS2.ACTIVE_JOB_INFO service, a very simple SQL statement will return the 20 jobs with the largest QTEMP libraries. Note that you need to include the AUTHORIZATION_NAME to see the current user for the prestart jobs.

-- see which jobs have the largest QTEMP library
SELECT JOB_NAME, AUTHORIZATION_NAME, QTEMP_SIZE
FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'QTEMP'))
ORDER BY QTEMP_SIZE DESC LIMIT 10 ;

This will return results that look something like the following (my test system is somewhat uninteresting):



I’m not going to review all of the additional information you can retrieve since the documentation lists the new columns.

Some of the more interesting fields that you can access with DETAILED_INFO =>’ALL’ include the following:

• CLIENT_IP_ADDRESS
• MESSAGE_REPLY
• QTEMP_SIZE
• DATABASE_LOCK_WAITS and DATABASE_LOCK_WAIT_TIME
• NON_DATABASE_LOCK_WAITS and NON_DATABASE_LOCK_WAIT_TIME
• SQL_STATEMENT_TEXT and additional SQL statement information
• QUERY_OPTIONS_LIBRARY_NAME
• SQL_SERVER_MODE
• Client special registers
• SERVER_MODE_CONNECTING_JOB
• PRESTART_JOB_REUSE_COUNT and PRESTART_JOB_MAX_USE_COUNT
• and much, much more

As I continue my series on prestart jobs, I will provide examples of using the QSYS2.ACTIVE_JOB_INFO service to gain insights into what your prestart jobs are doing. You can see from the above list that interesting data is now easily accessible.