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: Start of changeThe caller must have *IOSYSCFG special authority or be authorized to the QIBM_IOSYSCFG_VIEW function usage identifier. Rows are returned for any device description where the caller has *USE authority.End of change No additional authority is 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 be authorized to the QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage identifiers.

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