ACTIVE_JOB_INFO table function
The ACTIVE_JOB_INFO table function returns one row for every active job.
- To see details for all, or a subset of, active jobs. A subset of active jobs can be requested by using the optional filter parameters.
- To measure elapsed statistics for active jobs. You can use an optional parameter to reset statistics, similar to the WRKACTJOB command F10 Restart Statistics function. Measurements will be calculated based on this new starting point.
Authorization: None required to see general information or to see information for jobs where the caller's user profile is the same as the job user identity of the job for which the information is being returned.
![Start of change](./delta.gif)
- None required.
![End of change](./deltaend.gif)
![Start of change](./delta.gif)
- The caller must have *JOBCTL special authority.
![End of change](./deltaend.gif)
![Start of change](./delta.gif)
- All callers can see detailed column information for the columns that are included with DETAILED_INFO => WORK. In addition, the CLIENT_IP_ADDRESS, PAGE_FAULTS, PRESTART_JOB_REUSE_COUNT, and PRESTART_JOB_MAX_USE_COUNT columns are returned.
- A caller with QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage authority can see detailed column information that relates to SQL activity starting with the SQL_STATEMENT_TEXT column through the PSEUDO_CLOSED_CURSOR_COUNT column.
- A caller with *JOBCTL user special authority can see all detailed column information.
![End of change](./deltaend.gif)
- reset-statistics
- A character or graphic string expression that contains a value
of YES or NO.
If this parameter has a value of YES, statistics are reset such that the time of this query execution is used as the new baseline. Future invocations of ACTIVE_JOB_INFO within this connection will return statistical detail relative to the new baseline. If this parameter has a value of NO, statistics are not reset for the invocation unless the subsystem-list-filter or job-name-filter parameter values are different than the previous invocation. Changing the filter values will always cause statistics to be reset. If this parameter is not specified, the default is NO.
The first invocation of ACTIVE_JOB_INFO within a connection will always perform an implicit reset, regardless of whether a reset was explicitly requested.
- subsystem-list-filter
- A character or graphic string expression that contains a list
of up to 25 subsystem names separated by exactly one comma. The filter
determines which subsystems to use to return job information.
If this parameter is not specified, is an empty string, or is the null value, information for all subsystems is returned.
- job-name-filter
- A character or graphic string expression that contains an unqualified
job name that determines the job information to be returned. The name
can be a generic name.The string can be one of the following special values:
- *
- Only information for the current job is returned.
- *ALL
- Information for all jobs is returned.
- *CURRENT
- Information for all jobs with a job name that is the same as the current job is returned.
- *SBS
- Information for all active subsystem monitors is returned.
- *SYS
- Information for all active system jobs is returned. When using this value, the subsystem-list-filter must not be specified or must be the null value.
If this parameter is not specified, is an empty string, or is the null value, information for all jobs is returned.
- current-user-list-filter
- A character or graphic string expression that contains a list
of up to 10 user profile names separated by exactly one comma. The
filter determines which current user values to use to return job information.
If this parameter is not specified, is an empty string, or is the null value, information for all users is returned.
detailed-info
A character or graphic string expression that indicates the type of information to be returned.
- NONE
- Only the general information is returned for active jobs. This is the information in the columns prior to the JOB_DESCRIPTION_LIBRARY column. This is the default.
- WORK
- In addition to the general information for active jobs, additional work management information is returned.
- QTEMP
- In addition to the general information for active jobs, the QTEMP_SIZE column is returned.
- ALL
- Information for all the columns is returned.
The result of the function is a table containing multiple rows with the format shown in the following table. All the columns are nullable.
The DETAILED_INFO option column indicates which of the DETAILED_INFO parameter values can return a non-null value for the corresponding result column.
Column Name | Data Type | DETAILED_INFO option | Description |
---|---|---|---|
ORDINAL_POSITION | INTEGER |
NONE
WORK QTEMP ALL |
A unique number for each row. |
JOB_NAME | VARCHAR(28) |
NONE
WORK QTEMP ALL |
The qualified job name. |
![]() ![]() |
![]() ![]() |
![]() NONE
WORK QTEMP ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() NONE
WORK QTEMP ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() NONE
WORK QTEMP ALL ![]() |
![]() ![]() |
INTERNAL_JOB_ID | BINARY(16) |
NONE
WORK QTEMP ALL |
The internal job identifier. |
SUBSYSTEM | VARCHAR(10) |
NONE
WORK QTEMP ALL |
The name of the subsystem where the job is running. Contains the null value if the job is a system job. |
SUBSYSTEM_LIBRARY_NAME | VARCHAR(10) |
NONE
WORK QTEMP ALL |
Library containing the subsystem description. Contains the null value if the job is a system job. |
AUTHORIZATION_NAME | VARCHAR(10) |
NONE
WORK QTEMP ALL |
The user profile under which the initial thread is running at this time. For jobs that swap user profiles, this user profile name and the user profile that initiated the job can be different. |
JOB_TYPE | VARCHAR(3) |
NONE
WORK QTEMP ALL |
Type of active job.
|
FUNCTION_TYPE | VARCHAR(3) |
NONE
WORK QTEMP ALL |
The type of function described in the FUNCTION column.
Contains the null value if none of these values apply. |
FUNCTION | VARCHAR(10) |
NONE
WORK QTEMP ALL |
The last high-level function initiated by the initial
thread. If FUNCTION_TYPE is not null, contains a value as defined by the FUNCTION_TYPE column. Otherwise, can contain one of the following values:
Contains the null value if a logged function has not been performed. |
JOB_STATUS | VARCHAR(4) |
NONE
WORK QTEMP ALL |
The status of the initial thread of the job. The following list
contains some of the most common values. For a complete list of values, see Work Management API Attribute Descriptions in Application
Programming Interfaces
|
MEMORY_POOL | VARCHAR(9) |
NONE
WORK QTEMP ALL |
The identifier of the system-related pool from which the job's main storage is allocated. This is the pool that the threads in the job start in. |
RUN_PRIORITY | INTEGER |
NONE
WORK QTEMP ALL |
The run priority of the job. |
THREAD_COUNT | INTEGER |
NONE
WORK QTEMP ALL |
The number of active threads in the job. |
TEMPORARY_STORAGE | INTEGER |
NONE
WORK QTEMP ALL |
The amount of temporary storage, in megabytes, that is currently allocated to this job. |
CPU_TIME | DECIMAL(20,0) |
NONE
WORK QTEMP ALL |
The total processing unit time used by the job, in milliseconds. |
TOTAL_DISK_IO_COUNT | DECIMAL(20,0) |
NONE
WORK QTEMP ALL |
The total number of disk I/O operations performed by the job across all routing steps. This is the sum of the asynchronous and synchronous disk I/O. |
ELAPSED_INTERACTION_COUNT | INTEGER |
NONE
WORK QTEMP ALL |
The number of interactions. This is the number of operator
interactions during the measurement time interval. Contains the null value if the job is not interactive. |
ELAPSED_TOTAL_RESPONSE_TIME | INTEGER |
NONE
WORK QTEMP ALL |
The total response time over the measurement time interval, in
seconds. Contains the null value if the job is not interactive. |
ELAPSED_TOTAL_DISK_IO_COUNT | DECIMAL(20,0) |
NONE
WORK QTEMP ALL |
The number of disk I/O operations performed by the job during the measurement time interval. This is the sum of the asynchronous and synchronous disk I/O. |
ELAPSED_ASYNC_DISK_IO_COUNT | DECIMAL(20,0) |
NONE
WORK QTEMP ALL |
The number of asynchronous (physical) disk I/O operations performed by the job during the measurement time interval. This value is the sum of the asynchronous database and nondatabase reads and writes. |
ELAPSED_SYNC_DISK_IO_COUNT | DECIMAL(20,0) |
NONE
WORK QTEMP ALL |
The number of synchronous (physical) disk I/O operations performed by the job during the measurement time interval. This value is the sum of the synchronous database and nondatabase reads and writes. |
ELAPSED_CPU_PERCENTAGE | ![]() ![]() |
NONE
WORK QTEMP ALL |
The percent of processing unit time attributed to this job during the measurement time interval. |
ELAPSED_CPU_TIME | DECIMAL(20,0) |
NONE
WORK QTEMP ALL |
The total CPU time spent during the measurement time interval, in milliseconds. |
ELAPSED_PAGE_FAULT_COUNT | DECIMAL(20,0) |
NONE
WORK QTEMP ALL |
The number of times an active program referenced an address that is not in main storage for the specified job during the measurement time interval. |
JOB_END_REASON | VARCHAR(60) |
NONE
WORK QTEMP ALL |
Reason the job is ending. Contains one of the following values:
|
SERVER_TYPE | VARCHAR(30) |
NONE
WORK QTEMP ALL |
The type of server represented by the job. See Server table for a list of
server type values. Contains the null value if the job is not part of a server. |
ELAPSED_TIME | DECIMAL(20,3) |
NONE
WORK QTEMP ALL |
The time that has elapsed, in seconds, between the measurement start time and the current system time. |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if the job has no job description. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if the job has no job description. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if the job is not a batch job that was started from a job queue. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if the job is not a batch job that was started from a job queue. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if the job has no default output queue. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if the job has no default output queue. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if the job is not part of a workload group. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if no sort sequence table is defined for this job or if SORT_SEQUENCE is a special value. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() Contains the null value if no sort sequence table is defined for this job. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() The offset, in minutes, used to calculate local job time. This value has been adjusted for Daylight Saving Time, if necessary. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the time zone description uses a message to specify the current full name and the message cannot be retrieved. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the time zone description uses a message to specify the current abbreviated name and the message cannot be retrieved. ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() WORK
ALL ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value when no client IP address exists or the job is using IPv6. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the user profile no longer exists. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
Contains the null value if the job is not in message wait status. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no maximum. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the value is not available. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
Contains the null value if the value is not available. ![]() |
![]() ![]() |
![]() ![]() |
![]() QTEMP ALL ![]() |
![]() Contains the null value if the size cannot be returned. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no maximum or if the value is not available. ![]() |
![]() MAXIMUM_PROCESSING_TIME_
ALLOWED ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no maximum amount of processing unit time has been defined. ![]() |
![]() MAXIMUM_TEMPORARY_STORAGE_
ALLOWED ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no maximum amount of temporary storage has been defined. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the value is not available. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() INTERACTIVE_TRANSACTIONS
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() INTERNAL_MACHINE_
LOCK_WAITS ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() INTERNAL_MACHINE_LOCK_
WAIT_TIME ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no SQL statement has been run. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
Contains the null value if no SQL statement has been run. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no active SQL statement. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value when the SQL statement has no name. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value when the SQL statement name is null or when the SQL statement does not exist within a permanent object. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value when the SQL statement name is null or when the SQL statement does not exist within a permanent object. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
Contains the null value when the SQL statement object name is null. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no SQL statement has been run. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no SQL descriptors are active for the job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no LOB locators are active for the job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no CLI handles are active for the job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
Contains the null value if there is no SQL routine currently executing. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no SQL routine currently executing. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no SQL routine currently executing. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the target job does not correspond to a connection formed using the TCP/IP protocol. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the target job does not correspond to a connection formed using the TCP/IP protocol. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no client database interface name. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no client database interface type. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if there is no client database interface level. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the job name is not QSQSRVR or JOB_STATUS is PSRW. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the job name is not QSQSRVR or JOB_STATUS is PSRW. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the job is not a prestart job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the job is not a prestart job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the job has no unconsumed SQL result sets. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if the job has no unconsumed SQL result sets that have been discarded. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no SQL cursors are currently open for the job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no SQL cursors have been full opened during the life of the job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no SQL cursors have been pseudo opened during the life of the job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no SQL cursors are pseudo closed. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no cursors have used CQE for this job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no cursors have used CQE for this job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no cursors have used SQE for this job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no cursors have used SQE for this job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no cursors have used SQE for this job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() Contains the null value if no cursors have used SQE for this job. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
Examples
- Example 1: Looking at only QZDASOINIT jobs, find the top 10 consumers of Elapsed I/O.
Note: The data in the ELAPSED_xxx columns is updated upon each re-execution of the query. Elapsed data will not get returned the first time a query is run for ACTIVE_JOB_INFO for a connection. See the reset-statistics parameter for details.SELECT JOB_NAME, AUTHORIZATION_NAME, ELAPSED_TOTAL_DISK_IO_COUNT, ELAPSED_CPU_PERCENTAGE FROM TABLE(QSYS2.ACTIVE_JOB_INFO( JOB_NAME_FILTER => 'QZDASOINIT', SUBSYSTEM_LIST_FILTER => 'QUSRWRK')) X ORDER BY ELAPSED_TOTAL_DISK_IO_COUNT DESC FETCH FIRST 10 ROWS ONLY;
- Example 2: Find the active jobs using the most temporary storage. Include the most
recently executed SQL statement for each target job.
SELECT JOB_NAME, AUTHORIZATION_NAME, TEMPORARY_STORAGE, SQL_STATEMENT_TEXT FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) X WHERE JOB_TYPE <> 'SYS' ORDER BY TEMPORARY_STORAGE DESC;