DB2 MATERIALIZED QUERY TABLE

Materialized Query Table(MQT)

  1. A materialized query table is a table whose structure and definition are based on the result of a given query.
  2. Materialized query table gets its data from the 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

REFRESH TABLE

How MQT will get data when created with “DATA INITIALLY DEFERRED,” or How MQT will get updated when created with the “REFRESH DEFERRED” option. The answer is the “REFRESH TABLE” statement.

REFRESH TABLE TRANS_REPORT

This will update all the data of MQT using the 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 the queries are copied from somewhere else. I forgot the source. So as soon as I remember the source detail, I will update this post with the details.

--

--

--

A programmer.

Love podcasts or audiobooks? Learn on the go with our new app.

Part One: Deploy a Multi-Datacenter Apache Cassandra Cluster in Kubernetes

The Original Chatter Charts

CloudTrail to CloudWatch Metrics

Solar System — Exploration in Unity

What is a CDN(Content delivery network)?

What is a CDN(Content delivery network)?

Let’s consider you are a product manager at a company like Zomato.To

Testing Cashier

A Few Stokes of Genius: dexArb, A Free, Open Source Triangular Ether Dex Arbitrage Bot

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sumit goyal

Sumit goyal

A programmer.

More from Medium

Using the CASE Statement in SQL Server

Code Quality Analysis

Async programming in Shiny plus Spinners

MDF file size stays the same after DELETE records