GET_JOB_INFO table function

The GET_JOB_INFO table function returns one row containing the information about a specific job.

Authorization: None required to for a job where the caller's user profile is the same as the job user identity of the job for which the information is being returned.

Otherwise, the caller must have either *JOBCTL special authority, or QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage authority.

The schema is QSYS2.

job-name

A character or graphic string expression that identifies the name of a job. Two forms of a job name are supported.

A fully qualified job name in the form job-number/job-user/job-name.
The first 10 characters are the job name, the second 10 characters are the user name, and the last 6 characters are the job number.

The special value of '*' indicates the current job.

ignore-errors

A character or graphic string expression that identifies what to do when an error is encountered.

NO: An error is returned. This is the default.
YES: A warning is returned. No row is returned when an error is encountered.

The result of the function is a table containing a single row with the format shown in the following table. All the columns are nullable.

Table 1. GET_JOB_INFO table function
Column Name	Data Type	Description
V_JOB_STATUS	CHAR(10)	Status of the job. ACTIVE Job is active. It could be a group job, system request job, or disconnected job. JOBQ Job is currently on job queue. *OUTQ Job has completed running but has output on an output queue or the job log has not yet been written.
V_ACTIVE_JOB _STATUS	CHAR(4)	The active status of the initial thread of the job. For the list of values see Work Management API Attribute Descriptions in Application Programming Interfaces and search on "Active job status". Returns null if the job is in transition or is not active.
V_ACTIVE_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 Returns null if the job type is not available for jobs that are no longer active.
V_RUN_PRIORITY	INTEGER	The highest run priority allowed for any thread within this job.
V_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.
V_SBS_NAME	CHAR(10)	Name of subsystem where job is running. Returns null if the job is not active.
V_CPU_USED	BIGINT	The amount of CPU time (in milliseconds) that has been currently used by this job.
V_TEMP_STORAGE_USED_MB	INTEGER	The amount of auxiliary storage (in megabytes) that is currently allocated to this job.
V_AUX_IO_REQUESTED	BIGINT	The number of auxiliary I/O requests performed by the job across all routing steps. This includes both database and nondatabase paging.
V_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.
V_CLIENT_WRKSTNNAME	CHAR(255)	Value of the SQL CLIENT_WRKSTNNAME special register. Returns null if the job is not active.
V_CLIENT_APPLNAME	CHAR(255)	Value of the SQL CLIENT_APPLNAME special register. Returns null if the job is not active.
V_CLIENT_ACCTNG	CHAR(255)	Value of the SQL CLIENT_ACCTNG special register. Returns null if the job is not active.
V_CLIENT_PROGRAMID	CHAR(255)	Value of the SQL CLIENT_PROGRAMID special register. Returns null if the job is not active.
V_CLIENT_USERID	CHAR(255)	Value of the SQL CLIENT_USERID special register. Returns null if the job is not active.
V_SQL_STATEMENT_TEXT	VARCHAR(10000)	Statement text of the last SQL statement to run or the SQL statement that is currently running. Returns null if the job is not active.
V_SQL_STMT_STATUS	CHAR(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 UNKNOWN The SQL status is not known Returns null if no SQL statement has been run.
V_SQL_STMT_START_TIMESTAMP	TIMESTAMP	The timestamp of the execution start for an active SQL statement. If there is no active SQL statement, the null value is returned.
V_QUERY_OPTIONS_LIB_NAME	CHAR(10)	The name of the QAQQINI options library in use for this job.
V_CLIENT_IP_ADDRESS	VARCHAR(45)	Client IP address being used by the job. Returns null when no client IP address exists or the job is using IPv6.
V_PJ_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. Returns null if the job is not active or if the job is not a prestart job.
V_PJ_MAXUSE_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. Returns null if the job is not active or if the job is not a prestart job.

Example

Return information about job 347117/QUSER/QZDASOINIT.

SELECT * FROM TABLE(QSYS2.GET_JOB_INFO('347117/QUSER/QZDASOINIT'));