ACTIVE_JOB_INFO table function

The ACTIVE_JOB_INFO table function returns one row for every active job.

The information returned is similar to the detail seen from the Work with Active Jobs (WRKACTJOB) command and the List Job (QUSLJOB) API. The ACTIVE_JOB_INFO table function has two uses:

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.

For DETAILED_INFO => NONE or DETAILED_INFO => WORK:

None required.

For DETAILED_INFO => QTEMP:

The caller must have *JOBCTL special authority.

For DETAILED_INFO => ALL:

None required to see detailed column information for the columns that are included with DETAILED_INFO => WORK or the CLIENT_IP_ADDRESS, PAGE_FAULTS, PRESTART_JOB_REUSE_COUNT, PRESTART_JOB_MAX_USE_COUNT, and WORKLOAD_GROUP columns.
A caller who is authorized to the QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage identifiers can see detailed column information related to SQL activity starting with the SQL_STATEMENT_TEXT column through the PSEUDO_CLOSED_CURSOR_COUNT column.
A caller with *JOBCTL special authority can see all detailed column information.

The schema is QSYS2.

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.

Start of change The DETAILED_INFO option column indicates which of the DETAILED_INFO parameter values can return a non-null value for the corresponding result column. End of change

Table 1. ACTIVE_JOB_INFO table function
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.
JOB_NAME_SHORT	VARCHAR(10)	NONE WORK QTEMP ALL	The name of the job.
JOB_USER	VARCHAR(10)	NONE WORK QTEMP ALL	The user profile that started the job.
JOB_NUMBER	VARCHAR(6)	NONE WORK QTEMP ALL	The job number of the job.
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. ASJ Autostart BCH Batch BCI Batch Immediate EVK Started by a procedure start request INT Interactive M36 Advanced 36 server job MRT Multiple requester terminal PDJ Print driver job PJ Prestart job RDR Spool reader SBS Subsystem monitor SYS System WTR Spool writer
FUNCTION_TYPE	VARCHAR(3)	NONE WORK QTEMP ALL	The type of function described in the FUNCTION column. CMD The FUNCTION column contains the name of the command being run. DLY The initial thread of the job is processing a DLYJOB (Delay Job) command. The FUNCTION column contains a time that is the number of seconds the job is delayed (up to 999999 seconds), or the time when job is to resume processing (hh:mm:ss). GRP The FUNCTION column contains the group name of a suspended group job. I/O The job is a subsystem monitor that is performing input/output operations (I/O) to a work station for the sign-on display file. The FUNCTION column contains the name of the work station device. IDX The FUNCTION column contains the name of the file associated with an index rebuild operation. JVM The initial thread of the job is running a Java™ Virtual Machine. The FUNCTION column contains the name of the java class. LOG The FUNCTION column contains QHST to indicate history information is being logged to a database file. MNU The FUNCTION column contains the name of the menu. MRT The job is either a multiple requester terminal (MRT) job if JOB_TYPE is `BCH`, or it is an interactive job attached to an MRT job if JOB_TYPE is `INT`. For an MRT job, the FUNCTION column contains information in the following format: CHAR(2): The number of requesters currently attached to the MRT job. CHAR(1): Contains a / (slash). CHAR(2): The maximum number of requesters. CHAR(1): Contains a blank. CHAR(3): The never-ending program (NEP) indicator. A value of `NEP` indicates a never-ending program. A value of blanks indicates that it is not a never-ending program. CHAR(1): Contains a blank. For an interactive job attached to an MRT, the FUNCTION column contains the name of the MRT procedure. PGM The FUNCTION column contains the name of a program. PRC The FUNCTION column contains the name of a procedure. USR The FUNCTION column contains the user-specified function set with the Change Current Job (QWCCCJOB) API. 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: ADLACTJOB Auxiliary storage is being allocated for the number of active jobs specified in the QADLACTJ system value. ADLTOTJOB Auxiliary storage is being allocated for the number of jobs specified in the QADLTOTJ system value. CMDENT The command entry display is being used. COMMIT The initial thread of the job is performing a commit operation. DIRSHD This job is running under the directory shadowing function. DLTSPF A spooled file is being deleted. DUMP A dump is in process. JOBIDXRCY A damaged job index is being recovered. JOBLOG A job log is being produced. JOBLOGQRCY The job log server queue is being recovered or rebuilt. PASSTHRU The job is a pass-through job. RCLSPLSTG Empty spooled database members are being deleted. ROLLBACK The initial thread of the job is performing a rollback operation. SPLCLNUP A cleanup of jobs on job queues and spooled files is being performed. 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 CMNW Waiting for the completion of an I/O operation to a communications device. CNDW Waiting on handle-based condition. DEQW Waiting for completion of a dequeue operation. DLYW Due to the Delay Job (DLYJOB) command, the initial thread of the job is delayed while it waits for a time interval to end, or for a specific delay end time. DSPW Waiting for input from a work station display. END The job has been ended with the IMMED option, or its delay time has ended with the CNTRLD option. EOJ Ending for a reason other than running the End Job (ENDJOB) or End Subsystem (ENDSBS) command. EVTW Waiting for an event. HLD The job is being held. JVAW Waiting for completion of a Java program operation. LCKW Waiting for a lock. LSPW Waiting for a lock space to be attached. MSGW Waiting for a message from a message queue. MTXW Waiting for a mutex. PSRW A prestart job waiting for a program start request. RUN Job is currently running. SEMW Waiting for a semaphore. THDW Waiting for another thread to complete an operation.
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 priority at which the job competes for the processing unit relative to other jobs that are active at the same time. The run priority ranges from 1 (highest priority) to 99 (lowest priority).
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	DECIMAL(10,2)	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: JOB ENDED DUE TO A DEVICE ERROR JOB ENDED DUE TO A SIGNAL JOB ENDED DUE TO AN UNHANDLED ERROR JOB ENDED DUE TO THE CPU LIMIT BEING EXCEEDED JOB ENDED DUE TO THE DISCONNECT TIME INTERVAL BEING EXCEEDED JOB ENDED DUE TO THE INACTIVITY TIME INTERVAL BEING EXCEEDED JOB ENDED DUE TO THE MESSAGE SEVERITY LEVEL BEING EXCEEDED JOB ENDED DUE TO THE STORAGE LIMIT BEING EXCEEDED JOB ENDED WHILE IT WAS STILL ON A JOB QUEUE JOB ENDING ABNORMALLY JOB ENDING IMMEDIATELY JOB ENDING IN NORMAL MANNER JOB ENDING NORMALLY AFTER A CONTROLLED END WAS REQUESTED SYSTEM ENDED ABNORMALLY Contains the null value if job is not currently ending.
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.
JOB_DESCRIPTION_LIBRARY	VARCHAR(10)	WORK ALL	The name of the library containing the job description. Contains the null value if the job has no job description.
JOB_DESCRIPTION	VARCHAR(10)	WORK ALL	The name of the job description used for this job. Contains the null value if the job has no job description.
JOB_QUEUE_LIBRARY	VARCHAR(10)	WORK ALL	The name of the library containing the job queue. Contains the null value if the job is not a batch job that was started from a job queue.
JOB_QUEUE	VARCHAR(10)	WORK ALL	The name of the job queue that the job was on. Contains the null value if the job is not a batch job that was started from a job queue.
OUTPUT_QUEUE_LIBRARY	VARCHAR(10)	WORK ALL	The name of the library that contains the default output queue. Contains the null value if the job has no default output queue.
OUTPUT_QUEUE	VARCHAR(10)	WORK ALL	The name of the default output queue that is used for spooled output produced by this job. The default output queue is only used by spooled printer files that specify *JOB for the output queue. Contains the null value if the job has no default output queue.
WORKLOAD_GROUP	VARCHAR(10)	WORK ALL	The name of the workload group to which the job belongs. Contains the null value if the job is not part of a workload group.
CCSID	INTEGER	WORK ALL	The coded character set identifier (CCSID) used for this job.
DEFAULT_CCSID	INTEGER	WORK ALL	The default coded character set identifier used for this job.
SORT_SEQUENCE_LIBRARY	VARCHAR(10)	WORK ALL	The name of the library that contains the sort sequence table. Contains the null value if no sort sequence table is defined for this job or if `SORT_SEQUENCE` is a special value.
SORT_SEQUENCE	VARCHAR(10)	WORK ALL	The name of the sort sequence table associated with this job. Contains the null value if no sort sequence table is defined for this job.
LANGUAGE_ID	CHAR(3)	WORK ALL	The language identifier associated with this job.
DATE_FORMAT	CHAR(4)	WORK ALL	The date format used for this job. DMY Day, month, year format. JUL Julian format (year and day). MDY Month, day, year format. YMD Year, month, day format.
DATE_SEPARATOR	CHAR(1)	WORK ALL	The date separator used for this job.
TIME_SEPARATOR	CHAR(1)	WORK ALL	The time separator used for this job.
DECIMAL_FORMAT	VARCHAR(6)	WORK ALL	The decimal format used for this job. *BLANK Uses a period for a decimal point, a comma for a 3-digit grouping character, and zero-suppress to the left of the decimal point. J Uses a comma for a decimal point and a period for a 3-digit grouping character. The zero-suppression character is in the second position (rather than the first) to the left of the decimal notation. Balances with zero values to the left of the comma are written with one leading zero (0,04). The J entry also overrides any edit codes that might suppress the leading zero. I Uses a comma for a decimal point, a period for a 3-digit grouping character, and zero-suppress to the left of the decimal point.
TIMEZONE_DESCRIPTION	VARCHAR(10)	ALL	The name of the time zone description that is used to calculate local job time.
TIMEZONE_CURRENT_OFFSET	INTEGER	ALL	The offset, in minutes, used to calculate local job time. This value has been adjusted for Daylight Saving Time, if necessary.
TIMEZONE_FULL_NAME	VARCHAR(50)	ALL	The full, or long, name for the time zone. This column returns either the standard or Daylight Saving Time full name depending on whether or not Daylight Saving Time is in effect. Contains the null value if the time zone description uses a message to specify the current full name and the message cannot be retrieved.
TIMEZONE_ABBREVIATED_NAME	VARCHAR(10)	ALL	The abbreviated, or short, name for the time zone. This column returns either the standard or Daylight Saving Time abbreviated name depending on whether or not Daylight Saving Time is in effect. Contains the null value if the time zone description uses a message to specify the current abbreviated name and the message cannot be retrieved.
JOB_TYPE_ENHANCED	VARCHAR(28)	WORK ALL	The combined job type and job subtype values. ALTERNATE_SPOOL_USER Batch - alternate spool user AUTOSTART Autostart job BATCH Batch job BATCH_IMMEDIATE Batch immediate job BATCH_MRT Batch - System/36 multiple requester terminal (MRT) job COMM_PROCEDURE_START_REQUEST Communications job - procedure start request job INTERACTIVE Interactive job INTERACTIVE_GROUP Interactive job - Part of group INTERACTIVE_SYSREQ Interactive job - Part of system request pair INTERACTIVE_SYSREQ_AND_GROUP Interactive job - Part of system request pair and part of a group PRESTART Prestart job PRESTART_BATCH Prestart batch job PRESTART_COMM Prestart communications job READER Reader job SUBSYSTEM Subsystem job SYSTEM System job (all system jobs including SCPF) WRITER Writer job (including both spool writers and print drivers)
JOB_ENTERED_SYSTEM_TIME	TIMESTAMP(0)	WORK ALL	The timestamp for when the job was placed on the system.
JOB_ACTIVE_TIME	TIMESTAMP(0)	WORK ALL	The timestamp for when the job began to run on the system.
CLIENT_IP_ADDRESS	VARCHAR(45)	ALL	Client IP address, in IPv4 format, being used by the job. Contains the null value when no client IP address exists or the job is using IPv6.
JOB_USER_IDENTITY_SETTING	VARCHAR(11)	ALL	The method by which the job user identity was set. APPLICATION The job user identity was explicitly set by an application using one of the Set Job User Identity APIs, QWTSJUID or QwtSetJuid(). The job may be running either single threaded or multithreaded. DEFAULT The job is currently running single threaded and the job user identity is the name of the user profile under which the job is currently running. SYSTEM The job is currently running multithreaded and the job user identity was implicitly set by the system when the job became multithreaded. It was set to the name of the user profile that the job was running under when it became multithreaded.
JOB_USER_IDENTITY	VARCHAR(10)	ALL	The user profile name by which the job is known to other jobs on the system. The job user identity is used for authorization checks when other jobs on the system attempt to operate against the job. Contains the null value if the user profile no longer exists.
DBCS_CAPABLE	VARCHAR(3)	ALL	Whether the job is DBCS-capable. NO The job is not DBCS-capable. YES The job is DBCS-capable.
SIGNAL_STATUS	VARCHAR(3)	ALL	Whether the job is enabled to receive signals from another job or the system. NO The job is not enabled for signals. This job cannot receive signals from another job or the system. YES The job is enabled for signals. This job can receive signals from another job or the system.
MESSAGE_REPLY	VARCHAR(3)	ALL	Whether the job is waiting for a reply to a specific message. NO The job is not waiting for a reply to a message. YES The job is waiting for a reply to a message. Contains the null value if the job is not in message wait status.
END_STATUS	VARCHAR(3)	ALL	Whether the system issued a controlled cancellation. NO The system, subsystem, or job is not canceled. YES The system, the subsystem in which the job is running, or the job itself is canceled.
CANCEL_KEY	VARCHAR(3)	ALL	Whether the user pressed the cancel key. NO The user did not press the cancel key. YES The user pressed the cancel key.
EXIT_KEY	VARCHAR(3)	ALL	Whether the user pressed the exit key. NO The user did not press the exit key. YES The user pressed the exit key.
MAXIMUM_ACTIVE_THREADS	INTEGER	ALL	The maximum number of threads that a job can run with at any time. If multiple threads are initiated simultaneously, this value may be exceeded. If this maximum value is exceeded, the excess threads will be allowed to run to their normal completion. Initiation of additional threads will be inhibited until the maximum number of threads in the job drops below this maximum value. Contains the null value if there is no maximum.
SYSTEM_POOL_ID	INTEGER	ALL	The identifier of the system-related pool from which main storage is currently being allocated for the job's initial thread. These identifiers are not the same as those specified in the subsystem description, but are the same as the system pool identifiers shown on the system status display. If a thread reaches its time-slice end, the pool the thread is running in can be switched based on the job's time-slice end pool value. The current system pool identifier returned will be the actual pool in which the initial thread of the job is running. Contains the null value if the value is not available.
POOL_NAME	VARCHAR(10)	ALL	The name of the memory pool in which the job started running. The name may be a number, in which case it is a private pool associated with a subsystem. Can contain one of the following special values: BASE This job is running in the base system pool, which can be shared with other subsystems. INTERACT This job is running in the shared pool used for interactive work. MACHINE This job is running in the machine pool. SHRPOOL1 - SHRPOOL60 This job is running in the identified shared pool. SPOOL This job is running in the shared pool for spooled writers. Contains the null value if the value is not available.
QTEMP_SIZE	INTEGER	QTEMP ALL	The amount of storage, in megabytes, used by objects in the job's temporary library (QTEMP). Objects that are locked, damaged, or not authorized are not included. Contains the null value if the size cannot be returned.
PEAK_TEMPORARY_STORAGE	INTEGER	ALL	The maximum amount of auxiliary storage, in megabytes, that the job has used.
DEFAULT_WAIT	INTEGER	ALL	The default maximum time, in seconds, that a thread in the job waits for a system instruction, such as a LOCK machine interface (MI) instruction, to acquire a resource. Contains the null value if there is no maximum or if the value is not available.
MAXIMUM_PROCESSING_TIME_ ALLOWED	INTEGER	ALL	The maximum processing unit time, in milliseconds, that the job can use. If the job consists of multiple routing steps, this is the maximum processing unit time that the current routing step can use. If the maximum time is exceeded, the job is held. Contains the null value if no maximum amount of processing unit time has been defined.
MAXIMUM_TEMPORARY_STORAGE_ ALLOWED	INTEGER	ALL	The maximum amount of auxiliary storage, in megabytes, that the job can use. If the job consists of multiple routing steps, this is the maximum temporary storage that the routing step can use. This temporary storage is used for storage required by the program itself and by implicitly created internal system objects used to support the routing step. (It does not include storage for objects in the QTEMP library.) If the maximum temporary storage is exceeded, the job is held. This does not apply to the use of permanent storage, which is controlled through the user profile. Contains the null value if no maximum amount of temporary storage has been defined.
TIME_SLICE	INTEGER	ALL	The maximum amount of processor time, in milliseconds, given to each thread in this job before other threads in this job and in other jobs are given the opportunity to run. The time slice establishes the amount of time needed by a thread in this job to accomplish a meaningful amount of processing. At the end of the time slice, the thread might be put in an inactive state so that other threads can become active in the storage pool. Values range from 8 through 9999999. Contains the null value if the value is not available.
PAGE_FAULTS	BIGINT	ALL	The number of times an active program referenced an address that was not in main storage during the current routing step of the specified job.
TOTAL_RESPONSE_TIME	BIGINT	ALL	The total amount of response time for the initial thread, in milliseconds. This value does not include the time used by the machine, by the attached input/output (I/O) hardware, and by the transmission lines for sending and receiving data. Returns zero for jobs that have no interactions. A value of -1 is returned if the field is not large enough to hold the actual result.
INTERACTIVE_TRANSACTIONS	INTEGER	ALL	The count of operator interactions, such as pressing the Enter key or a function key. Returns zero for jobs that have no interactions.
DATABASE_LOCK_WAITS	INTEGER	ALL	The number of times that the initial thread had to wait to obtain a database lock.
NON_DATABASE_LOCK_WAITS	INTEGER	ALL	The number of times that the initial thread had to wait to obtain a nondatabase lock.
INTERNAL_MACHINE_ LOCK_WAITS	INTEGER	ALL	The number of times that the initial thread had to wait to obtain an internal machine lock.
DATABASE_LOCK_WAIT_TIME	INTEGER	ALL	The cumulative amount of time, in milliseconds, that the initial thread has had to wait to obtain database locks.
NON_DATABASE_LOCK_WAIT_TIME	INTEGER	ALL	The cumulative amount of time, in milliseconds, that the initial thread has had to wait to obtain nondatabase locks.
INTERNAL_MACHINE_LOCK_ WAIT_TIME	INTEGER	ALL	The cumulative amount of time, in milliseconds, that the initial thread has had to wait to obtain internal machine locks.
SQL_STATEMENT_TEXT	VARCHAR(10000)	ALL	Statement text of the last SQL statement to run or the SQL statement that is currently running. The statement text will be truncated if it is longer than the column. Contains the null value if no SQL statement has been run.
SQL_STATEMENT_STATUS	VARCHAR(8)	ALL	The status of SQL within this job. ACTIVE An SQL statement is currently running COMPLETE At least one SQL statement has run and has completed Contains the null value if no SQL statement has been run.
SQL_STATEMENT_START_TIMESTAMP	TIMESTAMP	ALL	The timestamp of the execution start for an active SQL statement. Contains the null value if there is no active SQL statement.
SQL_STATEMENT_NAME	VARCHAR(128)	ALL	The name of the SQL statement. Contains the null value when the SQL statement has no name.
SQL_STATEMENT_LIBRARY_NAME	VARCHAR(10)	ALL	The library name for the SQL statement object. Contains the null value when the SQL statement name is null or when the SQL statement does not exist within a permanent object.
SQL_STATEMENT_OBJECT_NAME	VARCHAR(10)	ALL	The name of the object which contains the last SQL statement executed in the job. When the current SQL statement belongs to an SQL function or an SQL procedure, the object name will be the external program name. Contains the null value when the SQL statement name is null or when the SQL statement does not exist within a permanent object.
SQL_STATEMENT_OBJECT_TYPE	VARCHAR(7)	ALL	The type of object containing the current SQL statement. PGM The current SQL statement resides within a program. SQLPKG The current SQL statement resides within an SQL package. *SRVPGM The current SQL statement resides within a service program. Contains the null value when the SQL statement object name is null.
QUERY_OPTIONS_LIBRARY_NAME	VARCHAR(10)	ALL	The name of the QAQQINI options library in use for this job.
SQL_ACTIVATION_GROUP_COUNT	INTEGER	ALL	The number of activation groups, current and ended, that have executed SQL statements for the job. Contains the null value if no SQL statement has been run.
SQL_DESCRIPTOR_COUNT	BIGINT	ALL	The number of SQL descriptors that are active for the job. Contains the null value if no SQL descriptors are active for the job.
SQL_LOB_LOCATOR_COUNT	INTEGER	ALL	The number of LOB locators that are active for the job. Contains the null value if no LOB locators are active for the job.
CLI_HANDLE_COUNT	BIGINT	ALL	The number of SQL Call Level Interface (CLI) handles that are active for the job. This count includes CLI statement handles, descriptor handles, environment handles, and connection handles. Contains the null value if no CLI handles are active for the job.
SQL_SERVER_MODE	VARCHAR(3)	ALL	Indicates whether the job is configured to use SQL Server Mode. NO The job is not configured to use SQL Server Mode. YES The job is configured to use SQL Server Mode.
CLIENT_ACCTNG	VARCHAR(255)	ALL	Value of the SQL CURRENT CLIENT_ACCTNG special register. The value can be null. For more information, see CURRENT CLIENT_ACCTNG.
CLIENT_APPLNAME	VARCHAR(255)	ALL	Value of the SQL CURRENT CLIENT_APPLNAME special register. The value can be null. For more information, see CURRENT CLIENT_APPLNAME.
CLIENT_PROGRAMID	VARCHAR(255)	ALL	Value of the SQL CURRENT CLIENT_PROGRAMID special register. The value can be null. For more information, see CURRENT CLIENT_PROGRAMID.
CLIENT_USERID	VARCHAR(255)	ALL	Value of the SQL CURRENT CLIENT_USERID special register. The value can be null. For more information, see CURRENT CLIENT_USERID.
CLIENT_WRKSTNNAME	VARCHAR(255)	ALL	Value of the SQL CURRENT CLIENT_WRKSTNNAME special register. The value can be null. For more information, see CURRENT CLIENT_WRKSTNNAME.
ROUTINE_TYPE	CHAR(1)	ALL	For a routine defined using SQL, the type of the currently executing routine. F Function P Procedure Contains the null value if there is no SQL routine currently executing.
ROUTINE_SCHEMA	VARCHAR(128)	ALL	For a routine defined using SQL, the schema name of the currently executing routine. Contains the null value if there is no SQL routine currently executing.
ROUTINE_SPECIFIC_NAME	VARCHAR(128)	ALL	For a routine defined using SQL, the name of the currently executing routine. Contains the null value if there is no SQL routine currently executing.
CLIENT_PORT	INTEGER	ALL	The port number used by the current client to communicate with the server. Contains the null value if the target job does not correspond to a connection formed using the TCP/IP protocol.
CLIENT_HOST	VARCHAR(255)	ALL	The host name used by the current client to communicate with the server. Contains the null value if the target job does not correspond to a connection formed using the TCP/IP protocol.
INTERFACE_NAME	VARCHAR(127)	ALL	The client database interface name. Contains the null value if there is no client database interface name.
INTERFACE_TYPE	VARCHAR(63)	ALL	The client database interface type. Contains the null value if there is no client database interface type.
INTERFACE_LEVEL	VARCHAR(63)	ALL	The client database interface level in the following form: "VVRRMMFP". VV - Version RR - Release MM - Modification level FP - Fix pack level (only applicable for certain interfaces). Contains the null value if there is no client database interface level.
SERVER_MODE_CONNECTING_JOB	VARCHAR(28)	ALL	The qualified job name of the job that established the SQL Server Mode connection. If the job name is QSQSRVR, then the qualified job name of the connecting job is returned. Contains the null value if the job name is not QSQSRVR or `JOB_STATUS` is `PSRW`.
SERVER_MODE_CONNECTING_THREAD	BIGINT	ALL	If the job name is QSQSRVR and the server mode job is in use, the thread identifier of the last thread to use this connection is returned. When `SQL_STATEMENT_STATUS` is `COMPLETE`, this application thread identifier might no longer exist. Contains the null value if the job name is not QSQSRVR or `JOB_STATUS` is `PSRW`.
PRESTART_JOB_REUSE_COUNT	INTEGER	ALL	The number of times the prestart job has been used. The prestart job reuse count is incremented when a disconnect is processed for a prestart job. When the prestart job reuse count exceeds the prestart job maximum number of uses, the job is ended. Contains the null value if the job is not a prestart job.
PRESTART_JOB_MAX_USE_COUNT	INTEGER	ALL	The maximum number of times the prestart job can be used before it is ended. A value of -1 is returned for *NOMAX. Contains the null value if the job is not a prestart job.
AVAILABLE_RESULT_SETS	INTEGER	ALL	The current count of unconsumed SQL result sets for the job. Contains the null value if the job has no unconsumed SQL result sets.
UNCONSUMED_RESULT_SETS	INTEGER	ALL	The cumulative count of unconsumed SQL result sets that were discarded for the job. Contains the null value if the job has no unconsumed SQL result sets that have been discarded.
OPEN_CURSOR_COUNT	INTEGER	ALL	The number of SQL cursors that are currently open for the job. Contains the null value if no SQL cursors are currently open for the job.
FULL_OPEN_CURSOR_COUNT	BIGINT	ALL	The total number of SQL cursors that have been full opened for the life of the job. Contains the null value if no SQL cursors have been full opened during the life of the job.
PSEUDO_OPEN_CURSOR_COUNT	BIGINT	ALL	The total number of SQL cursors that have been pseudo opened for the life of the job. Pseudo opens are also known as reused SQL cursors. Contains the null value if no SQL cursors have been pseudo opened during the life of the job.
PSEUDO_CLOSED_CURSOR_COUNT	INTEGER	ALL	The active number of pseudo closed SQL cursors within the job. Pseudo closed cursors are cursors that have been closed by the application, but remain open within the database. A pseudo closed cursor may be reused when the same query is executed many times, resulting in a performance improvement on the open. Conversely, accumulating too many pseudo closed cursors within the job can have a negative impact on the storage footprint of the job. Contains the null value if no SQL cursors are pseudo closed.
CQE_CURSOR_COUNT	INTEGER	ALL	The number of cursors using CQE for this job. This includes SQL cursors (both fully opened and pseudo closed) and cursors used to implement native database queries. Contains the null value if no cursors have used CQE for this job.
CQE_CURSOR_STORAGE	INTEGER	ALL	The amount of storage, in megabytes, used by cursors using CQE for this job. Contains the null value if no cursors have used CQE for this job.
SQE_CURSOR_COUNT	INTEGER	ALL	The number of cursors using SQE for this job. This includes SQL cursors (both fully opened and pseudo closed) and cursors used to implement native database queries. Contains the null value if no cursors have used SQE for this job.
SQE_CURSOR_STORAGE	INTEGER	ALL	The amount of storage, in megabytes, used by cursors using SQE for this job. Contains the null value if no cursors have used SQE for this job.
LARGEST_QUERY_SIZE	INTEGER	ALL	The amount of storage, in megabytes, used by the SQE cursor that used the most storage for this job. This could be for a different query than the one listed in the SQL_STATEMENT_TEXT column. Contains the null value if no cursors have used SQE for this job.
QRO_HASH	VARCHAR(8)	ALL	An internally generated identifier for the SQE query referred to in the `LARGEST_QUERY_SIZE` column. This could be for a different query than the one listed in the SQL_STATEMENT_TEXT column. The QRO hash surfaces within Visual Explain and from Show Statements exploration of the SQL Plan Cache and SQL Plan Cache Snapshots. Contains the null value if no cursors have used SQE for this job.
QRO_HASH_JSON	CLOB(1M) CCSID 1208	ALL	The list of QRO hashes associated with queries currently being optimized or running in this job. This list is returned as an array within a JSON object. The array is identified by QRO_HASH_LIST. Each entry in the JSON array identifies a QRO hash. Note that the plan for a specific QRO hash may no longer be in the plan cache.
PLAN_IDENTIFIER_JSON	CLOB(1M) CCSID 1208	ALL	The list of plan identifiers associated with queries currently running in this job. This list is returned as an array within a JSON object. The array is identified by PLAN_IDENTIFIER. Each entry in the JSON array identifies a plan identifier. Note that the plan for a specific plan identifier may no longer be in the plan cache.
OPEN_FILES	INTEGER	ALL	The number of open files (*FILE objects) for this job. For details about the types of files and their usage, use the QSYS2.OPEN_FILES table function.

Example

Example 1: Looking at only QZDASOINIT jobs, find the top 10 consumers of Elapsed I/O.
```
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;
```
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.

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;