Views

Difficulty: Intermediate · ~9 min read

Overview

A view is a stored query that looks and behaves like a table. It has no rows of its own — when you SELECT from a view, DB2 runs the underlying query under the hood.

Views are excellent for:

• Encapsulating complex joins — a 5-table join becomes a one-line SELECT * FROM v_orders_summary
• Hiding columns — expose only the columns a user role should see
• Renaming columns — present friendlier names
• Backward compatibility — keep old query interfaces working after table refactors

DB2 also supports Materialized Query Tables (MQTs) — physical copies of a view's result, refreshed on demand or automatically. MQTs trade storage for query speed, and the DB2 optimizer can use them transparently.

Syntax

-- Regular view
CREATE [OR REPLACE] VIEW v_name AS
  SELECT ... FROM ... WHERE ...;

-- View with check constraint enforcement
CREATE VIEW v_name AS
  SELECT ... FROM ... WHERE condition
WITH CHECK OPTION;

-- Materialized query table
CREATE TABLE mqt_name AS (SELECT ...)
  DATA INITIALLY DEFERRED REFRESH DEFERRED
  MAINTAINED BY USER;

-- Refresh MQT manually
REFRESH TABLE mqt_name;

-- Drop
DROP VIEW v_name;

Examples

We'll use these base tables:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        VARCHAR(80),
  region      VARCHAR(20),
  signup_date DATE
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers,
  amount      DECIMAL(10,2),
  ordered_at  TIMESTAMP
);

Example 1: A simple view

CREATE VIEW v_active_customers AS
  SELECT customer_id, name, region
  FROM   customers
  WHERE  signup_date >= CURRENT_DATE - 365 DAYS;

-- Read from it just like a table
SELECT * FROM v_active_customers WHERE region = 'EU';

Example 2: View hiding a join

CREATE VIEW v_customer_orders AS
  SELECT  c.customer_id,
          c.name,
          c.region,
          o.order_id,
          o.amount,
          o.ordered_at
  FROM    customers c
  JOIN    orders    o ON c.customer_id = o.customer_id;

-- Users don't need to know the schema
SELECT name, SUM(amount) AS total
FROM   v_customer_orders
GROUP BY name
ORDER BY total DESC;

Example 3: Aggregate view

CREATE VIEW v_region_sales AS
  SELECT  c.region,
          COUNT(*)           AS n_orders,
          SUM(o.amount)      AS total_sales,
          AVG(o.amount)      AS avg_order
  FROM    customers c
  JOIN    orders    o ON c.customer_id = o.customer_id
  GROUP BY c.region;

SELECT * FROM v_region_sales ORDER BY total_sales DESC;

Output:

REGION  N_ORDERS  TOTAL_SALES  AVG_ORDER
------  --------  -----------  ---------
US            42      8459.50     201.41
EU            38      6120.00     161.05
APAC          15      2350.00     156.67

Example 4: Updatable view

Views over a single table with no aggregation or DISTINCT are typically updatable — you can INSERT/UPDATE/DELETE through them.

CREATE VIEW v_eu_customers AS
  SELECT customer_id, name, region, signup_date
  FROM   customers
  WHERE  region = 'EU';

-- This works
UPDATE v_eu_customers
SET    name = 'New Name'
WHERE  customer_id = 1;

-- This also works but creates a non-EU row → invisible through the view
INSERT INTO v_eu_customers
       (customer_id, name, region, signup_date)
VALUES (999, 'Test', 'US', CURRENT_DATE);

Example 5: WITH CHECK OPTION

To prevent the surprise in Example 4, add WITH CHECK OPTION:

CREATE OR REPLACE VIEW v_eu_customers AS
  SELECT customer_id, name, region, signup_date
  FROM   customers
  WHERE  region = 'EU'
WITH CHECK OPTION;

-- Now this fails — would create a row that doesn't match the view's WHERE
INSERT INTO v_eu_customers
       (customer_id, name, region, signup_date)
VALUES (999, 'Test', 'US', CURRENT_DATE);
-- SQL0161N  CHECK OPTION violation

Example 6: Materialized Query Table

CREATE TABLE mqt_region_sales AS (
  SELECT  c.region,
          COUNT(*)      AS n_orders,
          SUM(o.amount) AS total_sales
  FROM    customers c
  JOIN    orders    o ON c.customer_id = o.customer_id
  GROUP BY c.region
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER;

-- Populate it
REFRESH TABLE mqt_region_sales;

-- The optimizer can now use this MQT to satisfy similar queries automatically
-- if CURRENT REFRESH AGE = ANY and the MQT is up to date.

Example 7: Dropping and recreating

DROP VIEW v_eu_customers;
CREATE VIEW v_eu_customers AS SELECT ...;  -- new definition

Or use CREATE OR REPLACE VIEW to do it in one shot — but note that the column list must be compatible with any objects that depend on the view.

Notes & Tips

• A view is just a saved query — there's no data stored. Queries against a view are exactly as fast (or slow) as the underlying SELECT.
• DB2's optimizer can sometimes merge a view into the outer query (view merging) so there's no performance penalty. Complex aggregating views may not merge — profile before assuming.
• Use WITH CHECK OPTION if your view has a WHERE filter and you allow writes through it. Otherwise users can insert rows that don't satisfy the filter.
• For very expensive aggregations, prefer an MQT with REFRESH DEFERRED over a regular view. The optimizer can route matching queries to the MQT automatically (a feature called query rewrite).
• A view depends on its base tables. If you DROP or ALTER a column the view uses, DB2 marks the view inoperative and you have to recreate it.
• Catalog: SELECT viewname, text FROM syscat.views WHERE viewschema = CURRENT_USER shows your views and their definitions.

Practice Exercises

1. Create v_top_customers that returns each customer's name + total amount of all orders, filtered to those over $1000. Verify by selecting from it.
2. Create v_recent_orders for orders in the last 30 days — make it read-only by using WITH CHECK OPTION on a base-table view that filters by ordered_at.
3. Build an MQT for v_region_sales and run REFRESH TABLE on it. Verify the row count matches the live view.
4. Drop a column the view uses and observe the view's valid flag turn to 'N' in SYSCAT.VIEWS.
5. Use CREATE OR REPLACE VIEW to change a view's column list without dropping it.

Quick Quiz

Q1. Does a view store rows?

Show answer

No. A regular view stores only its query definition. Selecting from the view re-executes the underlying query each time. A Materialized Query Table (MQT) does store rows — it's the persistent counterpart, refreshed on demand or automatically.

Q2. What does WITH CHECK OPTION do?

Show answer

When you INSERT or UPDATE through the view, DB2 verifies that the new row still satisfies the view's WHERE clause. Without WITH CHECK OPTION, you can write rows that "fall outside" the view — they get stored but are invisible through the view.

Q3. When should you choose an MQT over a regular view?

Show answer

When the view's query is expensive (heavy aggregation, multi-table join) and the result is read far more often than it changes. The MQT pre-computes the rows; the optimizer can transparently rewrite matching ad-hoc queries to read from the MQT. Pay with storage + refresh time; gain with fast reads.

Next Up

Views give you read shortcuts. Next we look at indexes — how DB2 finds rows fast in the first place.