ASP_JOB_INFO view

The ASP_JOB_INFO view returns information about active jobs that are using an independent auxiliary storage pool (IASP).

The information is similar to what is returned by the Work with ASP Jobs (WRKASPJOB) CL command.

Authorization: None required 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. Otherwise, the caller must have *JOBCTL special authority, or QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage authority.

The following table describes the columns in the view. The system name is ASPJ_INFO. The schema is QSYS2.

Table 1. ASP_JOB_INFO view
Column Name	System Column Name	Data Type	Description
IASP_NAME	IASP_NAME	VARCHAR(10)	The name of the independent ASP (IASP) device description.
IASP_NUMBER	IASPNUM	INTEGER	The number associated with the ASP device.
JOB_NAME	JOB_NAME	VARCHAR(28)	The qualified job name.
JOB_STATUS	JOB_STATUS	VARCHAR(4) Nullable	The 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".
JOB_TYPE	JOB_TYPE	VARCHAR(3) Nullable	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
AUTHORIZATION_NAME	USER_NAME	VARCHAR(10) Nullable	The user profile under which the initial thread is running at this time.
SUBSYSTEM_NAME	SUB_NAME	VARCHAR(10) Nullable	Name of subsystem where job is running.
SQL_STATEMENT_STATUS	SQL_STATUS	VARCHAR(8) Nullable	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_TEXT	SQL_STMT	VARCHAR(10000) Nullable	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_START_ TIMESTAMP	SQL_TIME	TIMESTAMP Nullable	The timestamp of the execution start for an active SQL statement. Contains the null value if there is no active SQL statement.
ASP_TYPE	ASP_TYPE	VARCHAR(9) Nullable	The use that is assigned to the ASP. PRIMARY The ASP is a primary ASP. SECONDARY The ASP is a secondary ASP. UDFS The ASP is a user-defined file system ASP.
RDB_NAME	RDB_NAME	VARCHAR(18) Nullable	The name that is assigned to the database that this ASP defines. Contains the null value if ASP_TYPE is not PRIMARY or SECONDARY.

Example

List all the jobs that are active for IASP33.

SELECT JOB_NAME, JOB_STATUS, JOB_TYPE, AUTHORIZATION_NAME
  FROM QSYS2.ASP_JOB_INFO
  WHERE IASP_NAME = 'IASP33';