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. 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.
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.

Authorization: None required to see general information or information about your own jobs.

For DETAILED_INFO => ALL:
  • All users can see detailed column information for CLIENT_IP_ADDRESS, PAGE_FAULTS, JOB_ACTIVE_TIME, PRESTART_JOB_REUSE_COUNT, and PRESTART_JOB_MAX_USE_COUNT.
  • A user 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.
  • For a user with *JOBCTL user special authority, all detailed column information is returned.
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.
ALL
Information for all the columns is 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.
QTEMP
In addition to the general information for active jobs, the QTEMP_SIZE column 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.

Table 1. ACTIVE_JOB_INFO table function
Column Name Data Type Description
ORDINAL_POSITION INTEGER A unique number for each row.
JOB_NAME VARCHAR(28) The qualified job name.
INTERNAL_JOB_ID BINARY(16) The internal job identifier.
SUBSYSTEM VARCHAR(10) 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) Library containing the subsystem description.

Contains the null value if the job is a system job.

AUTHORIZATION_NAME VARCHAR(10) 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) 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) 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) 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) 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) The identifier of the system-related pool from which the job's main storage is allocated.
RUN_PRIORITY INTEGER The run priority of the job.
THREAD_COUNT INTEGER The number of active threads in the job.
TEMPORARY_STORAGE INTEGER The amount of temporary storage, in megabytes, that is currently allocated to this job.
CPU_TIME DECIMAL(20,0) The total processing unit time used by the job, in milliseconds.
TOTAL_DISK_IO_COUNT DECIMAL(20,0) 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 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 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) 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) 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) 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,1) The percent of processing unit time attributed to this job during the measurement time interval.
ELAPSED_CPU_TIME DECIMAL(20,0) The total CPU time spent during the measurement time interval, in milliseconds.
ELAPSED_PAGE_FAULT_COUNT DECIMAL(20,0) 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) Reason the job is ending. Contains one of the following values:
  • JOB ENDING IN NORMAL MANNER
  • JOB ENDED WHILE IT WAS STILL ON A JOB QUEUE
  • SYSTEM ENDED ABNORMALLY
  • JOB ENDING NORMALLY AFTER A CONTROLLED END WAS REQUESTED
  • JOB ENDING IMMEDIATELY
  • JOB ENDING ABNORMALLY
  • JOB ENDED DUE TO THE CPU LIMIT BEING EXCEEDED
  • JOB ENDED DUE TO THE STORAGE LIMIT BEING EXCEEDED
  • JOB ENDED DUE TO THE MESSAGE SEVERITY LEVEL 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 A DEVICE ERROR
  • JOB ENDED DUE TO A SIGNAL
  • JOB ENDED DUE TO AN UNHANDLED ERROR
Contains the null value if job is not currently ending.
SERVER_TYPE VARCHAR(30) 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) The time that has elapsed, in seconds, between the measurement start time and the current system time.
Values for the following columns are returned when the DETAILED_INFO parameter is ALL. Otherwise, the columns will contain the null value.
JOB_DESCRIPTION_LIBRARY VARCHAR(10) The name of the library containing the job description.

Contains the null value if the job has no job description.

JOB_DESCRIPTION VARCHAR(10) 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) 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) 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) 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) 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.

CCSID INTEGER The coded character set identifier (CCSID) used for this job.
DEFAULT_CCSID INTEGER The default coded character set identifier used for this job.
SORT_SEQUENCE_LIBRARY VARCHAR(10) 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) 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) The language identifier associated with this job.
DATE_FORMAT CHAR(4) 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) The date separator used for this job.
TIME_SEPARATOR CHAR(1) The time separator used for this job.
DECIMAL_FORMAT VARCHAR(6) 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) The name of the time zone description that is used to calculate local job time.
TIMEZONE_CURRENT_OFFSET INTEGER

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) 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) 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_ENTERED_SYSTEM_TIME TIMESTAMP(0) The timestamp for when the job was placed on the system.
JOB_ACTIVE_TIME TIMESTAMP(0) The timestamp for when the job began to run on the system.
CLIENT_IP_ADDRESS VARCHAR(45) 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) 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) 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) Whether the job is DBCS-capable.
NO
The job is not DBCS-capable.
YES
The job is DBCS-capable.
SIGNAL_STATUS VARCHAR(3) 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) 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) 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) 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) 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 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

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.

POOL_NAME VARCHAR(10) 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.
QTEMP_SIZE INTEGER 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 The maximum amount of auxiliary storage, in megabytes, that the job has used.
DEFAULT_WAIT INTEGER 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.

MAXIMUM_PROCESSING_TIME_
ALLOWED
INTEGER 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 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 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.
PAGE_FAULTS BIGINT 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 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 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 The number of times that the initial thread had to wait to obtain a database lock.
NON_DATABASE_LOCK_WAITS INTEGER The number of times that the initial thread had to wait to obtain a nondatabase lock.
INTERNAL_MACHINE_
  LOCK_WAITS
INTEGER The number of times that the initial thread had to wait to obtain an internal machine lock.
DATABASE_LOCK_WAIT_TIME INTEGER 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 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 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) 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) 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 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) The name of the SQL statement.

Contains the null value when the SQL statement has no name.

SQL_STATEMENT_LIBRARY_NAME VARCHAR(10) 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) 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) 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) The name of the QAQQINI options library in use for this job.
SQL_ACTIVATION_GROUP_COUNT INTEGER 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 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 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 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) 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) 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) 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) 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) 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) 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) 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) 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) 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 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) 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) The client database interface name.

Contains the null value if there is no client database interface name.

INTERFACE_TYPE VARCHAR(63) The client database interface type.

Contains the null value if there is no client database interface type.

INTERFACE_LEVEL VARCHAR(63) 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) 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 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 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 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 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 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 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 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 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 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 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 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 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 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 The amount of storage, in megabytes, used by the SQE cursor that used the most storage for this job.

Contains the null value if no cursors have used SQE for this job.

QRO_HASH VARCHAR(8) An internally generated identifier for the SQE query referred to in the LARGEST_QUERY_SIZE 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.

Examples

  • 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;