
MTI_INFO table function
The MTI_INFO table function returns information about Maintained Temporary Indexes (MTIs).
Authorization: The caller must have *JOBCTL special authority or be authorized to the QIBM_DB_SQLADM function usage ID.
The schema is QSYS2.
- table-schema
- A character or graphic string expression that contains the schema name of the base table for the MTIs to be returned. The schema name may be either the SQL or the system schema name.
- table-name
- A character or graphic string expression that contains the table name of the base table for the MTIs to be returned. The table name may be either the SQL or the system table name.
The result of the function is a table containing multiple rows with the format shown in the following table. Each row contains information for one MTI on the system. All columns are nullable.
Column Name | Data Type | Description |
---|---|---|
TABLE_SCHEMA | VARCHAR(128) | Schema name of the base table that the MTI is created over. |
TABLE_NAME | VARCHAR(128) | Name of the base table that the MTI is created over. |
![]() ![]() |
![]() ![]() |
![]() ![]() |
REFERENCE_COUNT | BIGINT | The current number of references to this MTI. This includes plans in the plan cache and open queries that are using this MTI. When this count goes to 0, the MTI will be deleted. |
KEYS | INTEGER | Number of keys. |
KEY_DEFINITION | DBCLOB(10000) CCSID 1200 | Key definition. |
STATE | VARCHAR(20) | The current state of the MTI.
|
MTI_SIZE | BIGINT | The current size of the MTI in bytes. |
CREATE_TIME | TIMESTAMP | The timestamp of when the MTI was created. |
LAST_BUILD_START_TIME | TIMESTAMP | Start of the last index build. Contains the null value if the index portion of the MTI has not been populated. |
LAST_BUILD_END_TIME | TIMESTAMP | End of the last index build. Contains the null value if the index portion of the MTI has not been populated or is currently being populated. |
REUSABLE | VARCHAR(3) | MTI is reusable across queries.
|
SPARSE | VARCHAR(3) | MTI is sparse.
|
SPARSE_DEFINITION | DBCLOB(10000) CCSID 1200 | The predicate used to create the sparse MTI.
Contains the null value if SPARSE is NO. |
QRO_HASH | VARCHAR(8) | An internally generated identifier which identifies the SQE query which originally created the MTI. |
PLAN_IDENTIFIER | DECIMAL(20,0) | Identifies the plan within the plan cache which originally created the MTI. |
USER_NAME | VARCHAR(10) | The effective user of the thread that created the MTI. |
QUALIFIED_JOB_NAME | VARCHAR(28) | The fully qualified job name of the job that created the MTI. |
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. |
MTI_NAME | VARCHAR(128) | A generated name that identifies the MTI. |
LIBRARY_NAME | VARCHAR(10) | The name of the library that contains FILE_NAME. |
FILE_NAME | VARCHAR(10) | The database file name of the base table that the MTI is created over. |
![]() ![]() |
![]() ![]() |
![]() Note that the plan for a specific QRO hash may no longer be in the plan cache. ![]() |
![]() ![]() |
![]() ![]() |
![]() Note that the plan for a specific plan identifier may no longer be in the plan cache. ![]() |
Example
- Return information about all MTIs that exist over the APPLIB/EMPLOYEE
table.
SELECT * FROM TABLE(QSYS2.MTI_INFO('APPLIB', 'EMPLOYEE'));
- For the MTIs that exist over the APPLIB/EMPLOYEE table, return the list of associated
plans.
SELECT KEY_DEFINITION, PLAN_ID, PLAN_IDENTIFIER_JSON FROM TABLE (QSYS2.MTI_INFO('APPLIB', 'EMPLOYEE')), JSON_TABLE(PLAN_IDENTIFIER_JSON, 'lax $.PLAN_IDENTIFIER[*]' COLUMNS(PLAN_ID BIGINT PATH 'lax $' ));
