Start of change

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.

Read syntax diagramSkip visual syntax diagramMTI_INFO( TABLE_SCHEMA => table-schema,TABLE_NAME => table-name )

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.
Can contain the following special value:
*ALL
Information for MTIs in all schemas with a base table specified by table-schema is returned. This is the default.
If this parameter contains an empty string or the null value, *ALL is used.
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.
Can contain the following special value:
*ALL
Information for all MTIs with a base table in table-schema is returned. This is the default.
If this parameter contains an empty string or the null value, *ALL is used.

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.

Table 1. MTI_INFO table function
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.
CREATED
MTI is in a valid state but has not been populated. This can occur if the query subtree that uses the MTI has not been run.
CREATING
MTI is currently being created.
DELETING
MTI is currently being deleted.
MAPPING ERROR
A selection or mapping error occurred during index build. A mapping or selection error can occur if the key definition or the sparse definition contains an expression and that expression resulted in an error when being run. An example of that is a divide by zero.
POPULATING
The MTI is currently populating.
REBUILD REQUIRED
The index portion of the MTI will need to be re-populated on the next use of the MTI.
VALID
MTI is in a valid state and populated.
nnnn
A 4 digit number indicating an error status to be used by IBM® support.
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.
NO
MTI can only be used for this query.
YES
MTI is reusable.
SPARSE VARCHAR(3) MTI is sparse.
NO
MTI is not sparse.
YES
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.
Start of changeQRO_HASH_JSONEnd of change Start of changeCLOB(1M) CCSID 1208End of change Start of changeThe list of QRO hashes that has ever used this MTI. This list is returned as an array within a JSON object. The array is identified by QRO_HASH_LIST. Each entry in the JSON array identifies a QRO hash.

Note that the plan for a specific QRO hash may no longer be in the plan cache.

End of change
Start of changePLAN_IDENTIFIER_JSONEnd of change Start of changeCLOB(1M) CCSID 1208End of change Start of changeThe list of plan identifiers that has ever used this MTI. This list is returned as an array within a JSON object. The array is identified by PLAN_IDENTIFIER_LIST. Each entry in the JSON array identifies a plan identifier.

Note that the plan for a specific plan identifier may no longer be in the plan cache.

End of change

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 $' ));
    
End of change