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:
  1. 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.
  2. 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 Start of changeDETAILED_INFO => WORKEnd of change:
  • 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.
Read syntax diagramSkip visual syntax diagramACTIVE_JOB_INFO(RESET_STATISTICS => reset-statistics,SUBSYSTEM_LIST_FILTER => subsystem-list-filter,JOB_NAME_FILTER => job-name-filter,CURRENT_USER_LIST_FILTER => current-user-list-filter,DETAILED_INFO => detailed-info)
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.
Start of changeWORKEnd of change
Start of changeIn addition to the general information for active jobs, additional work management information is returned.End of change
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 changeThe 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)
Start of changeWORKEnd of change
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.

Start of changeQRO_HASH_JSONEnd of change Start of changeCLOB(1M) CCSID 1208End of change Start of changeALLEnd of change Start of changeThe 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.

End of change
Start of changePLAN_IDENTIFIER_JSONEnd of change Start of changeCLOB(1M) CCSID 1208End of change Start of changeALLEnd of change Start of changeThe 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.

End of change
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;