DB2 Views and Materialized Query Tables: Complete Guide

Views and Materialized Query Tables (MQTs) are two complementary mechanisms for simplifying complex queries and improving query performance in IBM DB2. Understanding both — and knowing when to use each — is an essential skill for any DB2 developer or DBA.

A view acts as a named, reusable query: it simplifies access to complex joins and business logic without duplicating data. An MQT goes further by physically storing pre-computed results, making it particularly powerful for analytical queries over large datasets.

DB2 Views

What Is a View?

A view is a virtual table defined by a SELECT statement. It has no physical storage — when you query a view, DB2 substitutes the view definition into your query and executes it against the underlying base tables. From the user's perspective, a view looks exactly like a regular table.

Creating a Basic View

CREATE VIEW EMPLOYEE_SUMMARY AS
SELECT E.EMPNO,
       E.FIRSTNME || ' ' || E.LASTNAME AS FULL_NAME,
       E.WORKDEPT,
       D.DEPTNAME,
       E.JOB,
       E.SALARY
FROM   EMPLOYEE E
JOIN   DEPARTMENT D ON E.WORKDEPT = D.DEPTNO;

Once created, query it like any table:

SELECT * FROM EMPLOYEE_SUMMARY WHERE WORKDEPT = 'A00';
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE_SUMMARY GROUP BY WORKDEPT;

Views with Filtering

Views can encapsulate WHERE clause logic to expose a subset of data:

-- View of only active, senior staff
CREATE VIEW SENIOR_EMPLOYEES AS
SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, HIREDATE
FROM   EMPLOYEE
WHERE  EDLEVEL >= 18
AND    SALARY  >= 60000
WITH   CHECK OPTION;

WITH CHECK OPTION ensures that any INSERT or UPDATE through this view must satisfy the WHERE conditions. Attempting to insert an employee with EDLEVEL < 18 through this view will fail.

Updatable Views

A DB2 view is updatable (supports INSERT/UPDATE/DELETE) if it:

• References exactly one base table (no JOINs)
• Does not use DISTINCT
• Does not use GROUP BY, HAVING, or aggregate functions
• Does not use set operations (UNION, INTERSECT, EXCEPT)
• Includes all NOT NULL columns that have no default

-- This view IS updatable
CREATE VIEW EMP_CONTACT AS
SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO
FROM   EMPLOYEE;

-- Update through the view
UPDATE EMP_CONTACT SET PHONENO = '4567' WHERE EMPNO = '000010';

-- Insert through the view
INSERT INTO EMP_CONTACT (EMPNO, FIRSTNME, LASTNAME, PHONENO)
VALUES ('999001', 'JANE', 'SMITH', '1234');

For views that are not updatable (e.g., JOINs, aggregates), use INSTEAD OF triggers to implement DML support.

Schema Binding with BIND

By default, views in DB2 are not bound to the current schema of their tables — if you drop and recreate a base table with the same name, the view continues to work. If you want the view to fail if the underlying schema changes, use schema binding (DB2 LUW):

CREATE VIEW DEPT_HEADCOUNT
WITH SCHEMABINDING AS
SELECT WORKDEPT, COUNT(*) AS HEADCOUNT
FROM   dbo.EMPLOYEE
GROUP BY WORKDEPT;

Dropping Views

DROP VIEW EMPLOYEE_SUMMARY;

Dropping a view does not affect the underlying base tables. However, any views, triggers, or stored procedures that reference this view will become invalid and need to be recreated.

Checking View Definitions

-- List views in a schema
SELECT VIEWNAME, DEFINER, CREATE_TIME
FROM   SYSCAT.VIEWS
WHERE  VIEWSCHEMA = 'DB2ADMIN'
ORDER BY VIEWNAME;

-- See the full view definition
SELECT TEXT
FROM   SYSCAT.VIEWS
WHERE  VIEWNAME = 'EMPLOYEE_SUMMARY';

Materialized Query Tables (MQTs)

What Is an MQT?

A Materialized Query Table (MQT) — also called a summary table in some documentation — is a table whose contents are defined by a query and physically stored on disk. Unlike a view, an MQT has real storage. The stored data is a pre-computed snapshot of what the query would return.

MQTs are most valuable for:

• Expensive aggregation queries (SUM, COUNT, AVG across millions of rows)
• Complex joins that are executed repeatedly
• Reporting and analytical workloads where a slightly stale result is acceptable

Creating an MQT

CREATE TABLE DEPT_SALARY_SUMMARY
AS (
    SELECT WORKDEPT,
           COUNT(*)    AS HEADCOUNT,
           SUM(SALARY) AS TOTAL_SALARY,
           AVG(SALARY) AS AVG_SALARY,
           MAX(SALARY) AS MAX_SALARY,
           MIN(SALARY) AS MIN_SALARY
    FROM   EMPLOYEE
    GROUP BY WORKDEPT
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION;

Breaking this down:

• DATA INITIALLY DEFERRED — the table is created empty; you must REFRESH it before querying
• REFRESH DEFERRED — DB2 will NOT automatically update the MQT when EMPLOYEE changes
• ENABLE QUERY OPTIMIZATION — allows DB2 to automatically route queries on EMPLOYEE to this MQT when appropriate

Refresh Strategies

REFRESH DEFERRED

The MQT data must be manually refreshed:

-- Populate or refresh the MQT
REFRESH TABLE DEPT_SALARY_SUMMARY;

Run this after significant changes to the base table. Deferred MQTs are ideal for nightly batch analytics where you refresh once and queries run against the snapshot all day.

REFRESH IMMEDIATE

The MQT is automatically maintained by DB2 as base table changes occur:

CREATE TABLE DEPT_SALARY_IMMEDIATE
AS (
    SELECT WORKDEPT,
           COUNT(*)    AS HEADCOUNT,
           SUM(SALARY) AS TOTAL_SALARY
    FROM   EMPLOYEE
    GROUP BY WORKDEPT
)
DATA INITIALLY DEFERRED
REFRESH IMMEDIATE
ENABLE QUERY OPTIMIZATION;

-- Initial population
SET INTEGRITY FOR DEPT_SALARY_IMMEDIATE IMMEDIATE CHECKED;

REFRESH IMMEDIATE MQTs have restrictions — they cannot include subqueries, OUTER JOINs, or certain aggregate functions. They add overhead to every INSERT/UPDATE/DELETE on the base table.

Automatic Query Routing

The real power of ENABLE QUERY OPTIMIZATION is that DB2 can transparently route queries to the MQT even when you query the base table:

-- User writes this query against the base table
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT;

-- DB2 automatically executes against DEPT_SALARY_SUMMARY instead
-- (much faster — reads pre-computed results)

This rewriting happens automatically when:

• The MQT was created with ENABLE QUERY OPTIMIZATION
• The MQT data is fresh (not deferred with expired data)
• The query is a semantic match for what the MQT covers

Use EXPLAIN to verify that DB2 is routing to your MQT:

EXPLAIN PLAN FOR
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT;

SELECT * FROM EXPLAIN_STATEMENT WHERE QUERYNO = (SELECT MAX(QUERYNO) FROM EXPLAIN_STATEMENT);

Look for the MQT table name in the EXPLAIN output rather than the base table.

User-Maintained MQTs

For total control over when and how data is refreshed, create a user-maintained MQT:

CREATE TABLE CUSTOM_SUMMARY
AS (
    SELECT WORKDEPT, JOB, YEAR(HIREDATE) AS HIRE_YEAR, COUNT(*) AS CNT
    FROM   EMPLOYEE
    GROUP BY WORKDEPT, JOB, YEAR(HIREDATE)
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER
ENABLE QUERY OPTIMIZATION;

With MAINTAINED BY USER, you populate and update the MQT yourself using regular INSERT/UPDATE/DELETE. DB2 never modifies it.

Staging Tables for Incremental Refresh

For REFRESH DEFERRED MQTs on large tables, a full refresh can be expensive. Use a staging table to capture changes incrementally:

-- Create a staging table linked to the MQT
CREATE TABLE DEPT_SALARY_STAGING
FOR DEPT_SALARY_SUMMARY
PROPAGATE IMMEDIATE;

Changes to the base table are captured in the staging table. When you run REFRESH TABLE, DB2 applies only the staged deltas rather than recomputing from scratch.

Comparing Views vs MQTs

Dropping an MQT

DROP TABLE DEPT_SALARY_SUMMARY;

MQTs are dropped like regular tables. All data in the MQT is lost, but the base tables are unaffected.

Practical Example: Reporting Layer

This pattern uses both views and an MQT to build a clean reporting layer:

-- Clean view joining the relevant tables
CREATE VIEW SALES_DETAIL AS
SELECT O.ORDER_ID, O.ORDER_DATE, C.CUSTOMER_NAME, P.PRODUCT_NAME,
       L.QUANTITY, L.UNIT_PRICE, L.QUANTITY * L.UNIT_PRICE AS LINE_TOTAL
FROM   ORDERS O
JOIN   CUSTOMERS C   ON O.CUSTOMER_ID = C.CUSTOMER_ID
JOIN   ORDER_LINES L ON O.ORDER_ID    = L.ORDER_ID
JOIN   PRODUCTS P    ON L.PRODUCT_ID  = P.PRODUCT_ID;

-- MQT for monthly summary reports (refreshed nightly)
CREATE TABLE MONTHLY_SALES_SUMMARY
AS (
    SELECT YEAR(ORDER_DATE)  AS YEAR,
           MONTH(ORDER_DATE) AS MONTH,
           PRODUCT_NAME,
           SUM(LINE_TOTAL)   AS TOTAL_REVENUE,
           COUNT(DISTINCT ORDER_ID) AS ORDER_COUNT
    FROM   SALES_DETAIL
    GROUP BY YEAR(ORDER_DATE), MONTH(ORDER_DATE), PRODUCT_NAME
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION;

-- Refresh after nightly batch
REFRESH TABLE MONTHLY_SALES_SUMMARY;

Report queries that aggregate by month and product now run in milliseconds instead of minutes.

Next Steps

Views and MQTs simplify query writing; CASE expressions make query logic more expressive. Read the DB2 CASE Expressions guide for conditional logic within SELECT statements. For the complete learning path, visit the DB2 Mastery course hub.