DB2® for z/OS® supports two types of materialized query tables: a system-maintained materialized query table and a user-maintained materialized query table. You can insert, update, and delete records in a user-maintained materialized query table; you cannot insert, update, or delete records in a system-maintained materialized query table.
A materialized query table node is labeled with the name of the materialized query table , and the default shape for this type of node is an upside-down trapezoid. The table's correlation name, creator name, or cardinality can also be displayed on the label. If the RUNSTATS utility has not collected statistics for the table, the node is outlined in red. If the optimizer uses the default value for the cardinality, the cardinality is marked as the default.
DB2 accesses a materialized query table the same way as it accesses a base table with one exception: DB2 cannot use a direct fetch to access an materialized query table.
When underlying data changes, DB2 does not automatically refresh materialized query tables. To keep the materialized query table data current, you must issue a REFRESH TABLE statement if the table is system-maintained. If the materialized query table is user-maintained, you must issue INSERT, UPDATE, and DELETE statements as needed. To control the use of materialized query tables, use the CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special registers.