Db2 views

What a view is

IBM describes a view as an alternative representation of data that exists in one or more tables. A view can include all or some columns from one or more base tables. Formally, a view is a named specification of a result table. At any moment, the view consists of the rows that its SELECT would return against current data.

You retrieve from a view much like a table: SELECT with WHERE, GROUP BY, and HAVING as allowed. Views can be based on tables, other views, or combinations. That composability is powerful—and a reason to keep view layers understandable.

• Combine data from different base tables
• Omit columns or rows to shield sensitive data
• Simplify authorization by granting on the view
• Show summary or filtered data tailored to a process

Views vs base tables

When you CREATE VIEW, Db2 stores the definition in the catalog. It does not store a separate full copy of the view’s data—the rows live in the underlying base tables. Restriction: you cannot create an index for a view, and you cannot create keys or constraints on a view; build those on the base tables.

Columns added to base tables after the view is defined do not appear in the view automatically. If callers need new columns, recreate or adjust the view definition.

Simple CREATE VIEW

Use CREATE VIEW, give a name (up to 128 characters), optionally list view column names, and supply AS SELECT ….

1
2
3
4
5
CREATE VIEW EMPINFO
  (EMPLOYEE, FIRSTNAME, LASTNAME, TEAM, JOBTITLE)
AS
SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, JOB
  FROM EMP;

EMPINFO renames columns for callers and omits sensitive columns such as salary. Querying the view is like running that SELECT:

1
2
3
SELECT EMPLOYEE, LASTNAME, TEAM
  FROM EMPINFO
 WHERE TEAM = 'A00';

Narrow the view with a WHERE clause in the definition so only certain departments appear:

1
2
3
4
5
6
7
CREATE VIEW EMPINFO_AC
  (EMPLOYEE, FIRSTNAME, LASTNAME, TEAM, JOBTITLE)
AS
SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, JOB
  FROM EMP
 WHERE WORKDEPT = 'A00'
    OR WORKDEPT = 'C01';

Mental model

CREATE VIEW = save a SELECT under a name. DROP VIEW = remove that saved definition. Privileges on the view control who can use it. The base table remains the source of truth for stored rows.

Updatable vs read-only views (intro)

Whether you can INSERT, UPDATE, or DELETE through a view depends on its definition. If a view includes a foreign key of its base table, insert/update through the view still obeys that referential constraint. If the base table is a parent, deletes through the view follow the same rules as deletes on the base table—when the view allows deletes at all.

Read-only views cannot be used for insert, update, and delete. Complex SELECTs— joins, unions, aggregations, DISTINCT, and similar constructs—often produce read-only views. Simple views on a single base table with a straightforward column list are the usual candidates for updatability (exact rules are version- and definition-specific—verify in the SQL reference before relying on them in production).

1
2
3
4
5
6
7
8
9
10
11
-- Likely closer to updatable: single table, simple columns
CREATE VIEW ACTIVE_CUSTOMER AS
SELECT CUST_ID, CUST_NAME, STATUS
  FROM CUSTOMER
 WHERE STATUS = 'A';
 
-- Typically read-only: aggregation
CREATE VIEW DEPT_HEADCOUNT AS
SELECT WORKDEPT, COUNT(*) AS EMP_COUNT
  FROM EMP
 GROUP BY WORKDEPT;

For beginners: use views freely for read simplification and security. Treat updatable views as an advanced design choice—many shops prefer updating base tables explicitly and keeping views read-focused.

Authorization pattern

A classic pattern is: revoke broad SELECT on the base table from an app id, create a view with only allowed columns/rows, and GRANT SELECT on the view. Callers never see the hidden columns even with SELECT *.

Explain It Like I'm Five

The real toy box is the base table. A view is a special window you cut in a piece of cardboard: maybe you only see the red toys, or you put two boxes side by side in the window. Looking through the window does not make a second full toy box. Sometimes you are allowed to move toys through the window; sometimes the window is look-only (read-only).

Exercises

1. Write CREATE VIEW that shows CUST_ID and CUST_NAME only from CUSTOMER.
2. Explain why a manager might get SELECT on that view but not on CUSTOMER.
3. Why doesn’t adding EMAIL to CUSTOMER automatically add EMAIL to an old view?
4. Classify EMPINFO (simple column subset) vs DEPT_HEADCOUNT (GROUP BY) as more likely updatable or read-only, and say why.
5. Can you CREATE INDEX on EMPINFO? What should you index instead?