SYSPARTITIONMQTS
The SYSPARTITIONMQTS view contains one row for every materialized table built over a table partition or table member. If the table is a distributed table, the materialized tables over partitions that reside on other database nodes are not contained in this catalog view. They are contained in the catalog views of the other database nodes.
Use this view when you want to see materialized query table information for materialized tables built on a specified table or set of 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 SYSPARTITIONMQTS view:
Column name | System Column Name | Data Type | Description |
---|---|---|---|
TABLE_SCHEMA | TABSCHEMA | VARCHAR(128) | Name of the SQL schema that contains the table. |
TABLE_NAME | TABNAME | VARCHAR(128) | Name of the table. |
TABLE_PARTITION | TABPART | VARCHAR(128) | Name of the table partition or member. |
MQT_NAME | MQTNAME | VARCHAR(128) | Name of the materialized query table. |
MQT_SCHEMA | MQTSCHEMA | VARCHAR(128) | Name of the SQL schema that contains 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. |
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. |
ENABLED | ENABLED | VARCHAR(3) | An indication or 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
|
Schema name of the sort sequence to use. Contains null if there is no schema name. |
SORT_SEQUENCE_NAME | SRTSEQNAM | CHAR(10) Nullable
|
Name of the sort sequence to use. Contains null if there is no sort sequence name. |
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. |
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. |
SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | System schema name. |
SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | System table name. |
SYSTEM_TABLE_MEMBER | SYS_MNAME | CHAR(10) | System member name. |