SYSMQTSTAT
The SYSMQTSTAT view contains one row for every materialized table partition.
Use this view when you want to see information about a specified materialized query table or set of materialized query tables. The information is similar to that returned via Show Materialized Query Tables in System i® Navigator.
The following table describes the columns in the SYSMQTSTAT view:
Column name | System Column Name | Data Type | Description |
---|---|---|---|
MQT_SCHEMA | MQTSCHEMA | VARCHAR(128) | Name of the SQL schema that contains the materialized query table. |
MQT_NAME | MQTNAME | VARCHAR(128) | Name of the materialized query table. |
MQT_PARTITION | MQTMEMBER | VARCHAR(128) | Partition or member name of the materialized query table. |
MQT_OWNER | MQTOWNER | VARCHAR(128) | Materialized query table owner. |
MQT_TEXT | LABEL | VARGRAPHIC(50) CCSID 1200 Nullable
|
Text of the materialized query table. Contains null if text does not exist for the materialized query table. |
ENABLED | ENABLED | VARCHAR(3) | An indication of whether the materialized
query table is enabled:
|
CREATE_TIMESTAMP | CREATED | TIMESTAMP | The timestamp when the materialized query table was created. |
REFRESH_TIME | REFRESHDTS | TIMESTAMP Nullable
|
The timestamp when the materialized query table was last refreshed. Contains null if the materialized query table has never been refreshed. |
LAST_QUERY_USE | LASTQRYUSE | TIMESTAMP Nullable
|
The timestamp of the last time the materialized query table was used in a query since the last time the usage statistics were reset. If the materialized query table has never been used in a query since the last time the usage statistics were reset, contains null. |
LAST_STATISTICS_USE | LASTSTUSE | TIMESTAMP Nullable
|
The timestamp of the last time the materialized query table was used by the optimizer for statistics since the last time the usage statistics were reset. If the materialized query table has never been used for statistics since the last time the usage statistics were reset, contains null. |
QUERY_USE_COUNT | QRYUSECNT | BIGINT | The number of times the materialized query table was used in a query since the last time the usage statistics were reset. If the materialized query table has never been used in a query since the last time the usage statistics were reset, contains 0. |
QUERY_STATISTICS_COUNT | QRYSTCNT | BIGINT | The number of times the materialized query table was used by the optimizer for statistics since the last time the usage statistics were reset. If the materialized query table has never been used for statistics since the last time the usage statistics were reset, contains 0. |
LAST_USED_TIMESTAMP | LASTUSED | TIMESTAMP Nullable
|
The timestamp of the last time the materialized query table was used directly by an application for native record I/O or SQL operations. If the materialized query table has never been used, contains null. |
DAYS_USED_COUNT | DAYSUSED | INTEGER | The number of days the materialized query table was used directly by an application for native record I/O or SQL operations since the last time the usage statistics were reset. If the materialized query table has never been used since the last time the usage statistics were reset, contains 0. |
LAST_RESET_TIMESTAMP | LASTRESET | TIMESTAMP Nullable
|
The timestamp of the last time the usage statistics were reset for the materialized query table. For more information see the Change Object Description (CHGOBJD) command. If the materialized query table's last used timestamp has never been reset, contains null. |
NUMBER_ROWS | CARD | BIGINT | Number of rows in the materialized query table. |
MQT_SIZE | SIZE | BIGINT | Size (in bytes) of the materialized query table. |
LAST_CHANGE_TIMESTAMP | LASTCHG | TIMESTAMP Nullable
|
The timestamp of the last time the materialized query table was changed. If the materialized query table has never been changed since the last time the usage statistics were reset, contains null. |
MAINTENANCE | MAINTAIN | VARCHAR(6) | Indicates the maintenance for the
materialized query table:
|
INITIAL_DATA | INITIAL | VARCHAR(19) | Indicates the initial data for the
materialized query table:
|
REFRESH | REFRESH | VARCHAR(9) | Indicates when the data in the materialized
query table can be refreshed:
|
ISOLATION | ISOLATION | VARCHAR(27) | Indicates the isolation level used
to refresh the materialized query table:
|
SORT_SEQUENCE | SRTSEQ | VARCHAR(12) | Indicates whether the materialize
query table uses a collating sequence:
|
LANGUAGE_IDENTIFIER | LANGID | CHAR(3) Nullable
|
The language ID of the materialize query table. Contains null if the sort sequence is hex. |
SORT_SEQUENCE_SCHEMA | SRTSEQSCH | CHAR(10) Nullable
|
The sort sequence table system schema. Contains null if the sort sequence is hex. |
SORT_SEQUENCE_NAME | SRTSEQNAME | CHAR(10) Nullable
|
The sort sequence table name. Contains null if the sort sequence is hex. |
MQT_RESTORE_DEFERRED | MQTRSTDFR | VARCHAR(3) | An indication of whether a restore
of the MQT is pending the restore of one of its dependents:
|
ROUNDING_MODE | DECFLTRND | VARCHAR(8) Nullable
|
Indicates the DECFLOAT rounding mode of the
materialized query table:
Contains the null value if the materialized query table does not have an expression that references a DECFLOAT column, function, or constant. |
DECFLOAT_WARNING | DECFLTWRN | VARCHAR(3) Nullable
|
Indicates whether DECFLOAT warnings
are returned:
Contains the null value if the materialized query table does not have an expression that references a DECFLOAT column, function, or constant. |
MQT_DEFINITION | MQTDEF | VARGRAPHIC(5000) CCSID 1200 | The query of the materialized query table. If the length of the query exceeds 5000, '...' is returned at the end of the column value. |
SYSTEM_MQT_SCHEMA | SYS_MQDNAM | CHAR(10) | System materialized query table schema name. |
SYSTEM_MQT_NAME | SYS_MQNAME | CHAR(10) | System materialized query table name. |