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.

Start of changeFor DETAILED_INFO => ALL:
  • All callers 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 caller with QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage authority can see detailed column information that relates to SQL activity starting with the SQL_STATEMENT_TEXT column through the PSEUDO_CLOSED_CURSOR_COUNT column.
  • A caller with *JOBCTL user special authority can see all detailed column information.
End of change
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.

Start of changedetailed-infoEnd of change
Start of changeA 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.
End of change

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.
Start of changeJOB_NAME_SHORTEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the job.End of change
Start of changeJOB_USEREnd of change Start of changeVARCHAR(10)End of change Start of changeThe user profile that started the job.End of change
Start of changeJOB_NUMBEREnd of change Start of changeVARCHAR(6)End of change Start of changeThe job number of the job.End of change
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. This is the pool that the threads in the job start in.
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 Start of changeDECIMAL(10,2)End of change 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 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) 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.
Start of changeValues for the following columns are returned when the DETAILED_INFO parameter is ALL. Otherwise, the columns will contain the null value.End of change
Start of changeJOB_DESCRIPTION_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the library containing the job description.

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

End of change
Start of changeJOB_DESCRIPTIONEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the job description used for this job.

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

End of change
Start of changeJOB_QUEUE_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeJOB_QUEUEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeOUTPUT_QUEUE_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the library that contains the default output queue.

Contains the null value if the job has no default output queue.

End of change
Start of changeOUTPUT_QUEUEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeCCSIDEnd of change Start of changeINTEGEREnd of change Start of changeThe coded character set identifier (CCSID) used for this job.End of change
Start of changeDEFAULT_CCSIDEnd of change Start of changeINTEGEREnd of change Start of changeThe default coded character set identifier used for this job. End of change
Start of changeSORT_SEQUENCE_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeSORT_SEQUENCEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the sort sequence table associated with this job.

Contains the null value if no sort sequence table is defined for this job.

End of change
Start of changeLANGUAGE_IDEnd of change Start of changeCHAR(3)End of change Start of changeThe language identifier associated with this job.End of change
Start of changeDATE_FORMATEnd of change Start of changeCHAR(4)End of change Start of changeThe 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.
End of change
Start of changeDATE_SEPARATOREnd of change Start of changeCHAR(1)End of change Start of changeThe date separator used for this job. End of change
Start of changeTIME_SEPARATOREnd of change Start of changeCHAR(1)End of change Start of changeThe time separator used for this job. End of change
Start of changeDECIMAL_FORMATEnd of change Start of changeVARCHAR(6)End of change Start of changeThe 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.
End of change
Start of changeTIMEZONE_DESCRIPTIONEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the time zone description that is used to calculate local job time. End of change
Start of changeTIMEZONE_CURRENT_OFFSETEnd of change Start of changeINTEGEREnd of change Start of change

The offset, in minutes, used to calculate local job time. This value has been adjusted for Daylight Saving Time, if necessary.

End of change
Start of changeTIMEZONE_FULL_NAMEEnd of change Start of changeVARCHAR(50)End of change Start of changeThe 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.

End of change
Start of changeTIMEZONE_ABBREVIATED_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeJOB_TYPE_ENHANCEDEnd of change Start of changeVARCHAR(28)End of change Start of changeThe 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)
End of change
Start of changeJOB_ENTERED_SYSTEM_TIMEEnd of change Start of changeTIMESTAMP(0)End of change Start of changeThe timestamp for when the job was placed on the system.End of change
Start of changeJOB_ACTIVE_TIMEEnd of change Start of changeTIMESTAMP(0)End of change Start of changeThe timestamp for when the job began to run on the system.End of change
Start of changeCLIENT_IP_ADDRESSEnd of change Start of changeVARCHAR(45)End of change Start of changeClient 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.

End of change
Start of changeJOB_USER_IDENTITY_SETTINGEnd of change Start of changeVARCHAR(11)End of change Start of changeThe 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.
End of change
Start of changeJOB_USER_IDENTITYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeDBCS_CAPABLEEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether the job is DBCS-capable.
NO
The job is not DBCS-capable.
YES
The job is DBCS-capable.
End of change
Start of changeSIGNAL_STATUSEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether 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.
End of change
Start of changeMESSAGE_REPLYEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether 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 of change
Start of changeEND_STATUSEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether 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.
End of change
Start of changeCANCEL_KEYEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether the user pressed the cancel key.
NO
The user did not press the cancel key.
YES
The user pressed the cancel key.
End of change
Start of changeEXIT_KEYEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether the user pressed the exit key.
NO
The user did not press the exit key.
YES
The user pressed the exit key.
End of change
Start of changeMAXIMUM_ACTIVE_THREADSEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeSYSTEM_POOL_IDEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changePOOL_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeQTEMP_SIZEEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changePEAK_TEMPORARY_STORAGEEnd of change Start of changeINTEGEREnd of change Start of changeThe maximum amount of auxiliary storage, in megabytes, that the job has used.End of change
Start of changeDEFAULT_WAITEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of change
MAXIMUM_PROCESSING_TIME_
ALLOWED
End of change
Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of change
MAXIMUM_TEMPORARY_STORAGE_
ALLOWED
End of change
Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeTIME_SLICEEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changePAGE_FAULTSEnd of change Start of changeBIGINTEnd of change Start of changeThe number of times an active program referenced an address that was not in main storage during the current routing step of the specified job.End of change
Start of changeTOTAL_RESPONSE_TIMEEnd of change Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of change
INTERACTIVE_TRANSACTIONS
End of change
Start of changeINTEGEREnd of change Start of changeThe count of operator interactions, such as pressing the Enter key or a function key. Returns zero for jobs that have no interactions.End of change
Start of changeDATABASE_LOCK_WAITSEnd of change Start of changeINTEGEREnd of change Start of changeThe number of times that the initial thread had to wait to obtain a database lock.End of change
Start of changeNON_DATABASE_LOCK_WAITSEnd of change Start of changeINTEGEREnd of change Start of changeThe number of times that the initial thread had to wait to obtain a nondatabase lock.End of change
Start of change
INTERNAL_MACHINE_
  LOCK_WAITS
End of change
Start of changeINTEGEREnd of change Start of changeThe number of times that the initial thread had to wait to obtain an internal machine lock.End of change
Start of changeDATABASE_LOCK_WAIT_TIMEEnd of change Start of changeINTEGEREnd of change Start of changeThe cumulative amount of time, in milliseconds, that the initial thread has had to wait to obtain database locks. End of change
Start of changeNON_DATABASE_LOCK_WAIT_TIMEEnd of change Start of changeINTEGEREnd of change Start of changeThe cumulative amount of time, in milliseconds, that the initial thread has had to wait to obtain nondatabase locks.End of change
Start of change
INTERNAL_MACHINE_LOCK_
WAIT_TIME
End of change
Start of changeINTEGEREnd of change Start of changeThe cumulative amount of time, in milliseconds, that the initial thread has had to wait to obtain internal machine locks.End of change
Start of changeSQL_STATEMENT_TEXTEnd of change Start of changeVARCHAR(10000)End of change Start of changeStatement 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.

End of change
Start of changeSQL_STATEMENT_STATUSEnd of change Start of changeVARCHAR(8)End of change Start of changeThe 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.

End of change
Start of changeSQL_STATEMENT_START_TIMESTAMPEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe timestamp of the execution start for an active SQL statement.

Contains the null value if there is no active SQL statement.

End of change
Start of changeSQL_STATEMENT_NAMEEnd of change Start of changeVARCHAR(128)End of change Start of changeThe name of the SQL statement.

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

End of change
Start of changeSQL_STATEMENT_LIBRARY_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeSQL_STATEMENT_OBJECT_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeSQL_STATEMENT_OBJECT_TYPEEnd of change Start of changeVARCHAR(7)End of change Start of changeThe 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.

End of change
Start of changeQUERY_OPTIONS_LIBRARY_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the QAQQINI options library in use for this job. End of change
Start of changeSQL_ACTIVATION_GROUP_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeSQL_DESCRIPTOR_COUNTEnd of change Start of changeBIGINTEnd of change Start of changeThe number of SQL descriptors that are active for the job.

Contains the null value if no SQL descriptors are active for the job.

End of change
Start of changeSQL_LOB_LOCATOR_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe number of LOB locators that are active for the job.

Contains the null value if no LOB locators are active for the job.

End of change
Start of changeCLI_HANDLE_COUNTEnd of change Start of changeBIGINTEnd of change Start of changeThe 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.

End of change
Start of changeSQL_SERVER_MODE End of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeCLIENT_ACCTNGEnd of change Start of changeVARCHAR(255)End of change Start of changeValue of the SQL CURRENT CLIENT_ACCTNG special register. The value can be null. For more information, see CURRENT CLIENT_ACCTNG.End of change
Start of changeCLIENT_APPLNAMEEnd of change Start of changeVARCHAR(255)End of change Start of changeValue of the SQL CURRENT CLIENT_APPLNAME special register. The value can be null. For more information, see CURRENT CLIENT_APPLNAME.End of change
Start of changeCLIENT_PROGRAMIDEnd of change Start of changeVARCHAR(255)End of change Start of changeValue of the SQL CURRENT CLIENT_PROGRAMID special register. The value can be null. For more information, see CURRENT CLIENT_PROGRAMID.End of change
Start of changeCLIENT_USERIDEnd of change Start of changeVARCHAR(255)End of change Start of changeValue of the SQL CURRENT CLIENT_USERID special register. The value can be null. For more information, see CURRENT CLIENT_USERID.End of change
Start of changeCLIENT_WRKSTNNAMEEnd of change Start of changeVARCHAR(255)End of change Start of changeValue of the SQL CURRENT CLIENT_WRKSTNNAME special register. The value can be null. For more information, see CURRENT CLIENT_WRKSTNNAME.End of change
Start of changeROUTINE_TYPEEnd of change Start of changeCHAR(1)End of change Start of changeFor 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.

End of change
Start of changeROUTINE_SCHEMAEnd of change Start of changeVARCHAR(128)End of change Start of changeFor 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.

End of change
Start of changeROUTINE_SPECIFIC_NAMEEnd of change Start of changeVARCHAR(128)End of change Start of changeFor a routine defined using SQL, the name of the currently executing routine.

Contains the null value if there is no SQL routine currently executing.

End of change
Start of changeCLIENT_PORTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeCLIENT_HOSTEnd of change Start of changeVARCHAR(255)End of change Start of changeThe 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.

End of change
Start of changeINTERFACE_NAMEEnd of change Start of changeVARCHAR(127)End of change Start of changeThe client database interface name.

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

End of change
Start of changeINTERFACE_TYPEEnd of change Start of changeVARCHAR(63)End of change Start of changeThe client database interface type.

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

End of change
Start of changeINTERFACE_LEVELEnd of change Start of changeVARCHAR(63)End of change Start of changeThe 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.

End of change
Start of changeSERVER_MODE_CONNECTING_JOBEnd of change Start of changeVARCHAR(28)End of change Start of changeThe 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.

End of change
Start of changeSERVER_MODE_CONNECTING_THREADEnd of change Start of changeBIGINTEnd of change Start of changeIf 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.

End of change
Start of changePRESTART_JOB_REUSE_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changePRESTART_JOB_MAX_USE_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeAVAILABLE_RESULT_SETSEnd of change Start of changeINTEGEREnd of change Start of changeThe current count of unconsumed SQL result sets for the job.

Contains the null value if the job has no unconsumed SQL result sets.

End of change
Start of changeUNCONSUMED_RESULT_SETSEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeOPEN_CURSOR_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeFULL_OPEN_CURSOR_COUNTEnd of change Start of changeBIGINTEnd of change Start of changeThe 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.

End of change
Start of changePSEUDO_OPEN_CURSOR_COUNTEnd of change Start of changeBIGINTEnd of change Start of changeThe 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.

End of change
Start of changePSEUDO_CLOSED_CURSOR_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeCQE_CURSOR_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeCQE_CURSOR_STORAGEEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeSQE_CURSOR_COUNTEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeSQE_CURSOR_STORAGEEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeLARGEST_QUERY_SIZEEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.

End of change
Start of changeQRO_HASHEnd of change Start of changeVARCHAR(8)End of change Start of changeAn 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.

End of change
Start of changeOPEN_FILESEnd of change Start of changeINTEGEREnd of change Start of changeThe 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.End of change

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;
  • Example 3: Decompose the JOB_NAME field into separate columns for each portion of the qualified job name.
    SELECT SUBSTR(JOB_NAME,1,6) AS JOB_NUMBER,
           SUBSTR(JOB_NAME,8,POSSTR(SUBSTR(JOB_NAME,8),'/')-1) AS JOB_USER,
           SUBSTR(SUBSTR(JOB_NAME,8),POSSTR(SUBSTR(JOB_NAME,8),'/')+1)  AS JOB_NAME
    FROM TABLE (QSYS2.ACTIVE_JOB_INFO()) AS X;