Start of change

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.

Read syntax diagramSkip visual syntax diagramACTIVE_QUERY_INFO(JOB_NAME => job-name,JOB_USER => job-user,JOB_NUMBER => job-number)
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.

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.
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;
End of change