DB2 materialized query tables (MQTs)

What an MQT is (and is not)

A view stores only the SELECT text. An MQT stores the rows. When a user writes SUM(AMOUNT) GROUP BY YEAR against a billion-row fact table, Db2 can notice you already materialized that grain and read the small MQT instead. That process is automatic query rewrite (AQR). The user does not name the MQT.

You can also SELECT from the MQT by name, like any table. That is useful while you test refresh quality. Production reporting can use either path: explicit MQT names, or AQR against the base tables.

CREATE TABLE AS

The fullselect plus DATA INITIALLY DEFERRED and REFRESH DEFERRED is what makes the table an MQT on z/OS. Column names can be listed before AS or taken from the select list.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE FIN.TRAN_BY_YEAR
    (YEAR, TXN_COUNT, AMOUNT_SUM)
  AS (
    SELECT YEAR, COUNT(*), SUM(AMOUNT)
      FROM FIN.TRAN
     GROUP BY YEAR
  )
  DATA INITIALLY DEFERRED
  REFRESH DEFERRED
  MAINTAINED BY SYSTEM
  DISABLE QUERY OPTIMIZATION;

IBM’s sample uses ENABLE QUERY OPTIMIZATION on CREATE. Many shops still create DISABLE, refresh, collect statistics, then ALTER TABLE … ENABLE QUERY OPTIMIZATION so the optimizer never sees an empty MQT.

You can also ALTER an existing table into an MQT (or convert an MQT back to a base table). That is how you attach a fullselect to a table you already loaded.

REFRESH DEFERRED versus REFRESH IMMEDIATE

REFRESH DEFERRED means “stale until I say otherwise.” On Db2 for z/OS this is the supported refresh mode. You run REFRESH TABLE FIN.TRAN_BY_YEAR in batch. Until then, AQR may still use the MQT if special registers allow deferred tables—knowing the numbers can be hours old.

REFRESH IMMEDIATE on Db2 LUW maintains the MQT in the same unit of work as base-table INSERT/UPDATE/DELETE. That option is not available on Db2 for z/OS the way LUW implements it. If you need near-real-time summaries on z/OS, use triggers or a user-maintained MQT you update yourself, or offload to the Analytics Accelerator—not REFRESH IMMEDIATE in z/OS DDL.

1
REFRESH TABLE FIN.TRAN_BY_YEAR;

ENABLE and DISABLE QUERY OPTIMIZATION

ENABLE QUERY OPTIMIZATION allows AQR. DISABLE QUERY OPTIMIZATION forbids AQR but still allows direct SQL against the MQT. The fullselect you write for ENABLE is more restricted (IBM documents which constructs AQR can match). A DISABLE MQT can use a more exotic SELECT because only humans will query it.

Special registers that gate AQR:

• CURRENT REFRESH AGE — 0 means do not use deferred MQTs; ANY (or the large timestamp IBM documents) allows them. ZPARM DFT_REFRESH_AGE sets the default
• CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION — SYSTEM, USER, ALL, NONE — which maintenance types AQR may consider

1
2
3
4
5
6
SET CURRENT REFRESH AGE ANY;
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = ALL;
 
SELECT YEAR, SUM(AMOUNT)
  FROM FIN.TRAN
 GROUP BY YEAR;

MQT maintenance, refresh, dependencies, statistics

MQT maintenance is either system (REFRESH TABLE only, plus utilities that IBM allows) or user (you LOAD/INSERT). System-maintained MQTs generally need at least one REFRESH TABLE before AQR will trust them. User-maintained MQTs do not get a Db2-maintained REFRESH_TIME the same way—you are responsible for freshness.

MQT refresh cost is a full recompute: scan sources, aggregate, replace MQT rows. Lock and log like a warehouse LOAD. Do it when sources are quiet, then RUNSTATS.

MQT dependencies: the fullselect references base tables, views, or functions. DROP or incompatible ALTER of a source can invalidate the MQT. Check catalog dependency tables before you drop a fact table that an MQT sits on.

MQT statistics are ordinary RUNSTATS on the MQT table space. Without them AQR cannot compare cost. After every refresh, RUNSTATS TABLESPACE … TABLE(the MQT) INDEX(ALL). Real-time statistics help too, but a full RUNSTATS after a complete replace is the safe habit.

Query rewrite, performance, restrictions

Query rewrite matches SELECT lists, GROUP BY columns, and predicates. A query that groups by YEAR, MONTH will not use an MQT that only groups by YEAR unless Db2 can derive the answer (usually it cannot). Equality predicates on extra dimensions also block a match. EXPLAIN is how you prove AQR fired.

MQT performance wins when many queries share one grain and refresh is rare compared with reads. It loses when every refresh scans the same fact table you were trying to avoid, or when DASD for the MQT rivals the base table.

MQT restrictions (z/OS):

• No unique index on an MQT
• DATA INITIALLY DEFERRED / REFRESH DEFERRED pattern; no LUW REFRESH IMMEDIATE
• AQR matching rules limit the fullselect if ENABLE QUERY OPTIMIZATION
• Isolation level of the query versus the MQT can block matching
• Empty, never-refreshed system MQTs are poor AQR candidates

Explain It Like I'm Five

Imagine counting every gold star in the school every time a teacher asks “how many stars this year?” An MQT is a poster on the wall with the totals already written. REFRESH TABLE is the night janitor who recounts and reprints the poster. ENABLE QUERY OPTIMIZATION means teachers who ask the long question get sent to the poster automatically. DISABLE means they must walk up and read the poster by name. REFRESH IMMEDIATE would be reprinting the poster after every single new star—that printer exists on LUW, not on z/OS, so the janitor works on a schedule instead.

Exercises

1. Write CREATE TABLE AS for SUM and COUNT grouped by WORKDEPT, DISABLE QUERY OPTIMIZATION, MAINTAINED BY SYSTEM.
2. List the steps: REFRESH TABLE, RUNSTATS, ALTER ENABLE, SET CURRENT REFRESH AGE ANY.
3. Explain in two sentences why z/OS does not give you LUW REFRESH IMMEDIATE.
4. EXPLAIN a GROUP BY query before and after ENABLE and compare PLAN_TABLE / query rewrite indicators your shop uses.
5. Find the catalog column that records last refresh time for a system-maintained MQT.