IBM i SQL

DB2 SQL MATERIALIZED QUERY TABLE


Materialized Query Table(MQT)

  1. A materialized query table is a table whose structure and definition is based on the result of a given query.
  2. Materialized query table get its data from same query. And MQT actually contains that data.
  3. That query may contain more than 1 table.


Syntax for MQT

CREATE TABLE mqt-name AS (select-statement) mqt-options.

for example

CREATE TABLE TRANS_REPORT              
AS (SELECT YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM FROM TRANS                             
GROUP BY YEAR, MONTH, DAY )
	DATA INITIALLY DEFERRED         
	REFRESH DEFERRED                    
        MAINTAINED BY USER      
mqt-options details
Option Details
DATA INITIALLY DEFERRED By default when MQT is created it will be populated with data from give (select-statement). If you don’t want to populate MQT table with data at time of creation use DATA INITIALLY DEFERRED option.
REFRESH DEFERRED || REFRESH IMMEDIATE Should MQT update automatically as soon as table(s) involved in (select-statement) gets updated? REFRESH DEFERRED answers NO and REFRESH IMMEDIATE answers YES
MAINTAINED BY SYSTEM || MAINTAINED BY USER MAINTAINED BY SYSTEM is default, SQL controls MQT and user can not run any DML (INSERT/UPDATE/DELETE) statement for this MQT.

MAINTAINED BY USER gives user option to run any DML (INSERT/UPDATE/DELETE) statement for this MQT.

REFRESH TABLE

How MQT will get data when it is created with “DATA INITIALLY DEFERRED” or How MQT will get updated when created with “REFRESH DEFERRED” option. Answer is “REFRESH TABLE” statement.

REFRESH TABLE TRANS_REPORT

This will update all the data of MQT using given (select-statement).

Convert a simple table into MQT.

Let say you created a table

CREATE TABLE TRANSCOUNT (ACCTID SMALLINT NOT NULL,  LOCID SMALLINT, YEAR DATE , CNT INTEGER)

And for some reason you want to convert this same table into MQT. Use ALTER TABLE Statement

ALTER TABLE TRANSCOUNT
		ADD MATERIALIZED QUERY
		(SELECT ACCTID, LOCID, YEAR, COUNT(*) AS CNT FROM TRANS GROUP BY ACCTID, LOCID, YEAR )
		DATA INITIALLY DEFERRED
		REFRESH DEFERRED
                MAINTAINED BY USER

And your table is now MQT.

If you want to convert your MQT back to your simple table, again use ALTER TABLE

ALTER TABLE TRANSCOUNT  DROP MATERIALIZED QUERY

NOTE: In this post most of queries are copied from somewhere else. I forgot the source as all this is saved in my notes. So as soon as I remember source detail I will update this post with details


IBM i developer.

View Comments
There are currently no comments.