QSYS2.ACTIVE_JOB_INFO Service Enhancements: QTEMP_SIZE and Much More
By Dawn May / September 25, 2018
Several enhancements to IBM i services were announced with IBM i 7.3 TR5 and IBM i 7.2 TR9.
The following IBM i Services are new: And the following services have been enhanced: 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
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
Tagged as:
IBM i / Article / Community / Systems management / i Can blog
About the author
Dawn May is an IBM i consultant. She owns Dawn May Consulting, LLC in the Greater Boston area. Dawn is a former IBM senior technical staff member.
See more by Dawn May