Chapter 4. Using the cube model for summary tables optimization 185
Is the table really an MQT?
You can ALTER an MQT and make it a real table with the following command:
ALTER TABLE mqt SET MATERIALIZED QUERY AS DEFINITION ONLY;
You can check the status of a table with:
SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABNAME = ‘mqt_name’;
Is the MQT accessible?
An MQT may be in a CHECK PENDING NO ACCESS state. This occurs:
1. After initial creation prior to population
2. When a staging table is created
3. On a staging table after a
SET INTEGRITY IMMEDIATE CHECKED is run on a base
table following a LOAD INSERT
4. On a REFRESH IMMEDIATE MQT after a
LOAD INSERT and SET INTEGRITY on
the base table
You can determine its status with:
SELECT TABNAME, STATUS ...