ACTIVE_QUERY_INFO table function

The ACTIVE_QUERY_INFO table function returns information about active SQL Query Engine (SQE) queries. An active query is either open or pseudo-closed.

Authorization: None required to see information for the current job.

Otherwise, the caller must have *JOBCTL special authority or be authorized to the QIBM_DB_SQLADM function usage ID.

The schema is QSYS2.

The values specified on the input parameters are ANDed together.

job-name

A character or graphic string expression that contains an unqualified job name that determines the job information to be returned.

The job name can end with a wildcard character. For example, 'QPADEV*' indicates that any job name starting with the characters 'QPADEV' is a match.

The string can be the following special values:

*: Information for the current job is returned. The job-user and job-number parameters cannot be specified.
*ALL: Information for all job names is returned.

If this parameter is not specified, is an empty string, or is the null value, information for all jobs is returned.

job-user

A character or graphic string expression that contains a user name that determines the job information to be returned.

The job user name can end with a wildcard character. For example, 'Q*' indicates that any job user name starting with the character 'Q' is a match.

The string can be the following special value:

*ALL: Information for all job user names is returned.

If this parameter is not specified, is an empty string, or is the null value, information for all users is returned.

job-number

A character or graphic string expression that contains a job number that determines the job information to be returned.

The string can be the following special value:

*ALL: Information for all job numbers is returned.

If this parameter is not specified, is an empty string, or is the null value, information for all job numbers is returned.

user-name

A character or graphic string expression that contains a user name. This is the user profile under which the initial thread is running at this time.

The user name can end with a wildcard character. For example, 'BA*' indicates that any user name starting with the characters 'BA' is a match.

The string can be the following special value:

*ALL: Information for all users is returned.

If this parameter is not specified, is an empty string, or is the null value, the results are not filtered by the user name used by the initial thread .

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_QUERY_INFO table function
Column Name	Data Type	Description
QUALIFIED_JOB_NAME	VARCHAR(28)	The qualified job name.
JOB_NAME	VARCHAR(10)	The name of the job.
JOB_USER	VARCHAR(10)	The user profile that started the job.
JOB_NUMBER	VARCHAR(6)	The job number of the job.
USER_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 started the job can be different.
QUERY_TYPE	VARCHAR(6)	The type of query. HLL High Level Language (HLL) open done using SQE. HLL opens include opens done via RPG, C, and COBOL NATIVE Native query SQL SQL query
PSEUDO_CLOSED	VARCHAR(3)	The current state of the SQL cursor associated with the query. NO Query is open YES Query is pseudo-closed Contains the null value if the query is not an SQL query.
QRO_HASH	VARCHAR(8)	The QRO_HASH which uniquely identifies an SQE query.
PLAN_IDENTIFIER	DECIMAL(20,0)	The Plan Identifier of the SQE plan.
FULL_OPEN_TIMESTAMP	TIMESTAMP	The full open timestamp.
LAST_PSEUDO_OPEN_TIMESTAMP	TIMESTAMP	The timestamp of the last pseudo-open. Contains the null value if the query has not been pseudo opened.
LIBRARY_NAME	VARCHAR(10)	The name of the library that contains FILE_NAME.
FILE_NAME	VARCHAR(10)	The database file name of the first table referenced by the query.
NUMBER_OF_PSEUDO_CLOSES	BIGINT	The number of complete runs for this full open of the query. Returns 0 if this query has not been pseudo-closed.
CURRENT_ROW_COUNT	BIGINT	If this cursor is not pseudo-closed, the current number of rows fetched for this run of the query. Contains the null value if this cursor is currently pseudo-closed.
CURRENT_RUNTIME	BIGINT	If this cursor is not pseudo-closed, the current runtime in microseconds for this run of the query. Contains the null value if this cursor is currently pseudo-closed.
CURRENT_TEMPORARY_STORAGE	BIGINT	The current amount of temporary storage, in MB, used by the query. This size does not include storage used by MTIs.
CURRENT_DATABASE_READS	BIGINT	If this cursor is not pseudo-closed, the current number of asynchronous and synchronous database reads for this run of the query. Contains the null value if this cursor is currently pseudo-closed.
CURRENT_PAGE_FAULTS	BIGINT	If this cursor is not pseudo-closed, the current number of page faults for this run of the query. Contains the null value if this cursor is currently pseudo-closed.
MTI_COUNT	BIGINT	The number of Maintained Temporary Indexes (MTIs). Returns 0 if no MTIs are used by the query.
MTI_SIZE	BIGINT	The current size of MTIs, in MB. This size includes shared MTIs. Contains the null value if no MTIs are used by the query.
AVERAGE_ROW_COUNT	BIGINT	The average number of rows fetched for pseudo-closed runs of this query. Contains the null value if there are no prior runs.
AVERAGE_RUNTIME	BIGINT	The average runtime in microseconds for pseudo-closed runs of this query. Contains the null value if there are no prior runs.
AVERAGE_TEMPORARY_STORAGE	BIGINT	The average amount of temporary storage in MB for pseudo-closed runs of this query. This size does not include storage used by MTIs. Contains the null value if there are no prior runs.
AVERAGE_DATABASE_READS	BIGINT	The average number of asynchronous and synchronous database reads for pseudo-closed runs of this query. Contains the null value if there are no prior runs.
AVERAGE_PAGE_FAULTS	BIGINT	The average number of page faults for pseudo-closed runs of this query. Contains the null value if there are no prior runs.

Examples

Retrieve information about all active SQE queries on the system.
```
SELECT *
FROM TABLE(QSYS2.ACTIVE_QUERY_INFO( ));
```

Find which QZDASOINIT jobs are using MTIs.

SELECT JOB_NAME, MTI_COUNT, MTI_SIZE
FROM TABLE(QSYS2.ACTIVE_QUERY_INFO(
            JOB_NAME => 'QZDASOINIT'))
WHERE MTI_COUNT > 0;