DB2 views: CREATE VIEW, check option, and updatability

CREATE VIEW

Syntax, stripped to what you type every day:

1
2
3
4
CREATE VIEW view-name (column-names)
     AS
     fullselect
     WITH CASCADED CHECK OPTION;

The name must not identify an existing table, view, alias, or synonym at the current server. A three-part name’s location must be this subsystem. You need SELECT (or ownership / DBADM / DATAACCESS / SYSADM, and so on) on every table or view in the fullselect.

The fullselect must not use host variables, parameter markers, declared global temporary tables, UNPACK, a period specification, a data-change-table-reference, a view that already has an INSTEAD OF trigger, or an array result column. It may use global variables and common table expressions.

A view definition cannot reference remote objects and cannot map to more than 15 base-table instances. CREATE VIEW is not allowed if a referenced column has pending definition changes.

View columns and view definitions

If you list column names, the list must match the select-list count; names must be unique and unqualified. If you omit the list, the view inherits result-column names. You must supply names when the fullselect has duplicate names or an unnamed column (a constant, function, or expression without AS).

1
2
3
4
5
CREATE VIEW HR.V_EMP_BASIC
     (EMPNO, NAME, DEPT)
     AS SELECT EMPNO, LASTNAME, WORKDEPT
          FROM HR.EMP
         WHERE WORKDEPT <> ' ';

IBM stores two forms of the definition. The source is the text you wrote. The operational form is what Db2 actually runs, frozen at CREATE time. If you write SELECT * FROM S and S is an alias for A.T with columns C1, C2, C3, the operational form is SELECT C1, C2, C3 FROM A.T. Adding C4 to A.T later does not add C4 to the view. Dropping the synonym S does not change the operational form either. Test with SELECT * FROM view-name.

Implicitly hidden base columns appear in the view only if you list them explicitly; view columns are never hidden. Inline LOB attributes may be inherited if Db2 can pass them through. XML type modifiers on a base column are inherited by the view column (INSTEAD OF triggers skip that validation on insert/update through the view).

ALTER VIEW

Db2 for z/OS does not ALTER the SELECT list. The statement you use is:

1
ALTER VIEW HR.V_EMP_BASIC REGENERATE;

REGENERATE rebuilds internal control blocks from the existing definition. Use it when implicit regeneration failed, after CATMAINT updated catalog columns the view references, or when maintenance ++HOLD data says to regenerate views. It does not flip read-only to updatable and does not pick up new base columns.

To change logic: DROP VIEW then CREATE VIEW with the new fullselect. Recreate dependent views afterward (DROP removes them).

DROP VIEW

1
DROP VIEW HR.V_EMP_BASIC;

The named view is dropped. Views that depend on it, directly or indirectly, are dropped as well. Packages that referenced the view are invalidated. The owner can drop the view; SYSADM and related authorities can drop others’ views. There is no separate “keep dependents” option — plan GRANT and nested-view trees before you drop a shared view.

Updatable views and read-only views

A view is read-only if any of IBM’s tests is true. Then it cannot be the target of INSERT, UPDATE, DELETE, or TRUNCATE. A view with GROUP BY or HAVING also cannot appear in a subquery of a basic predicate.

A view is insertable if an INSTEAD OF INSERT trigger exists, or if at least one column is updatable. The owner of a non-read-only view receives INSERT, UPDATE, and DELETE on the view for each of those privileges already held on the underlying table or view (with GRANT option if those were held with GRANT).

Simple updatable view: one base table, subset of columns, optional WHERE, no DISTINCT or GROUP BY. Updates change the base row. Inserts add a base row (omitted view columns follow defaults/nullability on the table).

WITH CHECK OPTION, CASCADED, and LOCAL

Without a check option, you can UPDATE a row through the view so that it no longer satisfies the view’s WHERE — the row “disappears” from the view. WITH CHECK OPTION forbids that: every inserted or updated row must still be retrievable through the view. If the view is updatable but not insertable, the option applies to updates only.

You cannot specify CHECK OPTION if the view is read-only, contains a subquery, uses a non-deterministic or external-action function, or references a created temporary table. Row/column access control on a referenced table also blocks CHECK OPTION when those search conditions would be checked on insert/update.

CASCADED vs LOCAL only differs when views sit on other views. IBM’s example:

1
2
3
4
5
6
7
8
9
CREATE VIEW V1 AS
  SELECT COL1 FROM T1 WHERE COL1 > 10;
 
CREATE VIEW V2 AS
  SELECT COL1 FROM V1
  WITH CASCADED CHECK OPTION;
 
CREATE VIEW V3 AS
  SELECT COL1 FROM V2 WHERE COL1 < 100;

• INSERT INTO V1 VALUES(5) — succeeds (V1 has no check option).
• INSERT INTO V2 VALUES(5) — fails (must satisfy V1’s COL1 > 10).
• INSERT INTO V3 VALUES(5) — fails (still must satisfy V1).
• INSERT INTO V3 VALUES(200) — succeeds: V3 has no check option of its own, and 200 passes V2/V1. The row will not appear in V3 because of V3’s WHERE.

WITH CASCADED CHECK OPTION is illegal if an underlying view has an INSTEAD OF trigger. LOCAL lets you insert a row that violates an underlying view that was defined without a check option. When in doubt, use CASCADED on the view that applications write through.

Views over joins

A join in the first FROM makes the view read-only. That is still one of the most useful view types: a saved join with nice column names.

1
2
3
4
5
6
7
8
CREATE VIEW DSN8C10.VPROJRE1
     (PROJNO, PROJNAME, PROJDEP, RESPEMP,
      FIRSTNME, MIDINIT, LASTNAME)
     AS SELECT ALL
            PROJNO, PROJNAME, DEPTNO, EMPNO,
            FIRSTNME, MIDINIT, LASTNAME
          FROM DSN8C10.PROJ, DSN8C10.EMP
         WHERE RESPEMP = EMPNO;

The WHERE may reference columns that are not in the view (EMPNO). GRANT SELECT on VPROJRE1 without granting EMP or PROJ. To make a join “updatable,” define INSTEAD OF triggers that split the change onto the base tables — that is advanced application design, not WITH CHECK OPTION.

Views over unions

An outer fullselect that is not a single subselect (UNION, UNION ALL, EXCEPT, INTERSECT) is read-only. IBM’s sample stacks three month tables:

1
2
3
4
5
6
7
8
9
CREATE VIEW DSN8C10.FIRSTQTR (SNO, CHARGES, DATE) AS
  SELECT SNO, CHARGES, DATE FROM MONTH1
   WHERE DATE BETWEEN '01/01/2000' AND '01/31/2000'
  UNION ALL
  SELECT SNO, CHARGES, DATE FROM MONTH2
   WHERE DATE BETWEEN '02/01/2000' AND '02/29/2000'
  UNION ALL
  SELECT SNO, CHARGES, DATE FROM MONTH3
   WHERE DATE BETWEEN '03/01/2000' AND '03/31/2000';

UNION ALL keeps duplicates and is cheaper than UNION. Column names come from your view column list (required here because each SELECT uses the same names). Applications SELECT from FIRSTQTR as if it were one table.

Views over expressions

Select-list expressions (SALARY * 1.1, COALESCE, CAST, scalar functions) need an AS name or a view column list. Those columns are not updatable: Db2 cannot invert an expression into a base column. A view can mix updatable base columns and read-only expressions; INSERT must omit the expression columns (or an INSTEAD OF trigger must handle them).

1
2
3
4
5
CREATE VIEW HR.V_EMP_PAY
     (EMPNO, LASTNAME, SALARY, TAX_EST)
     AS SELECT EMPNO, LASTNAME, SALARY,
               DECIMAL(SALARY * 0.22, 9, 2)
          FROM HR.EMP;

TAX_EST is derived. Updating SALARY through this view (if otherwise updatable) does not accept an UPDATE of TAX_EST.

Nested views

A view may SELECT FROM another view. That is how you layer security (inner view hides SSN; outer view filters WORKDEPT) and how CASCADED/LOCAL check options chain. Nested views still count toward the 15 base-table-instance limit when expanded. Keep the tree shallow: each layer is another catalog lookup and another privilege check.

View dependencies, invalidation, and regeneration

SYSIBM.SYSVIEWS holds the definition. SYSIBM.SYSVIEWDEP (and related dependency tables) list tables, views, aliases, and functions the view needs. DROP TABLE or DROP VIEW of a base object drops dependent views. ALTER TABLE that is incompatible with the operational form can invalidate packages and leave the view needing DROP/CREATE or REGENERATE.

Invalidation of packages happens when the view is dropped or its operational form can no longer be used. Implicit regeneration runs when Db2 opens a view after migration or fallback. If that fails, issue ALTER VIEW REGENERATE. APAR ++HOLD text sometimes requires regenerating views that reference catalog columns CATMAINT changed.

View security and authorization

The owner always gets SELECT on the view and DROP. If every privilege needed to create the view was held WITH GRANT OPTION, SELECT on the view is WITH GRANT OPTION; otherwise without. A process with SYSADM/SYSCTRL can create a view for another owner who has no privilege on the base tables; that owner still gets SELECT on the view (without GRANT) and can drop it — this is how shops publish a safe window on a sensitive table.

Then GRANT SELECT ON view TO role-or-id, and do not GRANT on the base table. Column hiding (omit SALARY) and row hiding (WHERE WORKDEPT = 'D11') are the two classic patterns. Updatable views need WITH CHECK OPTION so users cannot UPDATE WORKDEPT to escape the filter. Row permissions and column masks on the base table are a newer complement; they interact with CHECK OPTION as noted above.

1
2
3
4
5
6
7
CREATE VIEW HR.V_D11_EMP
     AS SELECT EMPNO, LASTNAME, WORKDEPT
          FROM HR.EMP
         WHERE WORKDEPT = 'D11'
     WITH CASCADED CHECK OPTION;
 
GRANT SELECT, UPDATE ON HR.V_D11_EMP TO ROLE_D11_CLERK;

Explain It Like I'm Five

A view is a window with blinds. CREATE VIEW cuts the window and writes down which room (table) you are looking into. The furniture still sits in the room; the window does not copy the sofa. If the window looks into two rooms at once (a join) or stacks two photos (UNION), you can look but you cannot rearrange furniture through the glass (read-only). WITH CHECK OPTION is a lock on the blinds: you may not shove a chair somewhere the window would not show. GRANT on the view is handing someone a key to the window, not to the whole house. DROP VIEW takes the window out — and any window that was looking through it. REGENERATE is polishing the same glass after the house was painted, not cutting a new window.

Exercises

1. Create an updatable view on EMP showing EMPNO, LASTNAME, WORKDEPT for WORKDEPT = 'A00' with WITH CHECK OPTION. Attempt an UPDATE that sets WORKDEPT to 'B01' and predict the SQLCODE class of error.
2. Using V1/V2/V3 from IBM’s CASCADED example, explain why INSERT INTO V3 VALUES(200) succeeds but the row is invisible in V3.
3. Write a read-only view that joins EMP and DEPT. Which IBM rule made it read-only?
4. CREATE VIEW V AS SELECT * FROM T; then ALTER TABLE T ADD COLUMN C4. Does SELECT * FROM V return C4? How do you pick up C4?
5. You need clerks to see EMP without SALARY. Write the view, GRANT, and say whether the clerks need SELECT on EMP.