Db2 Views for z/OS: CREATE VIEW, Queries, and Dependencies

CREATE VIEW PAYROLL.EMP_PUBLIC AS SELECT EMPNO, LASTNAME FROM PAYROLL.EMPLOYEE gives applications a stable, named result without exposing every base-table column. Db2 stores the view definition, not a separate copy of the ordinary view's rows.

A Db2 view is a named SELECT over tables or other views; catalog rows record its definition and dependencies.

What is a Db2 view?

A Db2 view is a named specification of a result table. It can select particular columns, filter rows, calculate expressions, join tables, or refer to another view. When SQL references the view, Db2 evaluates its definition against the current underlying data.

Views are useful for giving an application a simpler interface and for granting access to a selected projection of data. They do not replace the table space, base table, indexes, or authorization design behind that interface. The Db2 relational database guide places views beside tables, rows, columns, and keys.

Create a single-table view

CREATE VIEW PAYROLL.EMP_PUBLIC
       (EMPLOYEE_NO, SURNAME, DEPARTMENT_NO)
AS
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM PAYROLL.EMPLOYEE
 WHERE STATUS = 'A';

The view exposes three columns and only active employees. The optional column list after the view name assigns names to the result columns. If it is supplied, the number of names must match the number of columns returned by the fullselect, and each name must be unique.

Without that list, eligible result columns inherit their names from the SELECT result. Explicit names are required in cases where the result does not provide usable unique names, such as some expressions or duplicate column names.

Db2 for z/OS catalog correction: use SYSIBM.SYSVIEWS and related SYSIBM catalog tables. SYSCAT.VIEWS is a Db2 LUW catalog view and is not the correct catalog name for this z/OS-focused article.

Query a view

SELECT EMPLOYEE_NO, SURNAME
  FROM PAYROLL.EMP_PUBLIC
 WHERE DEPARTMENT_NO = 'D01'
 ORDER BY SURNAME;

Applications reference the view much like a table. Predicates in the application query operate with the predicates in the view definition. Use explicit column names in production SQL so a later design review can see the required interface without relying on SELECT *.

An ordinary view does not guarantee row order. Code ORDER BY on the query that returns rows to the application. A view also does not automatically make a query faster; Db2's optimizer evaluates the expanded query and selects an access path using the underlying objects and statistics. See the Db2 optimizer guide for EXPLAIN and RUNSTATS checks.

Create a view that joins tables

CREATE VIEW PAYROLL.EMP_DEPT
       (EMPNO, LASTNAME, DEPTNO, DEPTNAME)
AS
SELECT E.EMPNO, E.LASTNAME, D.DEPTNO, D.DEPTNAME
  FROM PAYROLL.EMPLOYEE E
  JOIN PAYROLL.DEPARTMENT D
    ON D.DEPTNO = E.WORKDEPT;

This view hides the join from its consumers and gives the result a consistent column list. Keep the definition readable and qualify object names so deployment does not depend on an unexpected current SQL ID.

Join and aggregate views can be read-only, while some simple views allow INSERT, UPDATE, or DELETE. The exact rules belong to the Db2 view classification guide, which separates simple, join, aggregate, read-only, and updatable views.

Use views for controlled access

GRANT SELECT
   ON PAYROLL.EMP_PUBLIC
   TO ROLE REPORT_READER;

A view can omit salary, tax, or personal columns and expose only rows needed by a role. Granting SELECT on the view can let a user query that interface without granting direct SELECT on the base table, subject to Db2 ownership and authorization rules.

The view owner needs the required privileges or authority for the objects referenced by CREATE VIEW. Privilege changes later can affect privileges on the view and, in some cases, can cause the view to be dropped. For sensitive data, coordinate view grants with RACF-connected authorization IDs, roles, row permissions, and column masks.

Do not treat a filtered view as the only security control. Review who can access the base table, who can alter or replace application SQL, and whether row or column access control is required.

Use a view from COBOL static SQL

EXEC SQL
   SELECT SURNAME,
          DEPARTMENT_NO
     INTO :WS-SURNAME,
          :WS-DEPT-NO
     FROM PAYROLL.EMP_PUBLIC
    WHERE EMPLOYEE_NO = :WS-EMP-NO
END-EXEC.

The COBOL host variables must match the result data types and nullability. The program is precompiled and its DBRM is bound into a package as usual. That package records dependencies on the referenced objects, so view or base-table changes can lead to invalidation or a required rebind.

The Db2 packages guide explains DBRMs, BIND PACKAGE, collections, and REBIND behavior for static SQL.

Find a view definition in the Db2 catalog

Db2 for z/OS records view information across several SYSIBM catalog tables. Useful starting points are:

Catalog table	What it records
SYSIBM.SYSTABLES	One object row for the view, including its name, schema, and type information.
SYSIBM.SYSCOLUMNS	One row for each view column.
SYSIBM.SYSVIEWS	One or more rows containing the CREATE VIEW definition text and related attributes.
SYSIBM.SYSVIEWDEP	Dependencies of the view on tables, other views, functions, and supported object types.
SYSIBM.SYSTABAUTH	Table and view privilege information.

SELECT NAME, CREATOR, SEQNO, TEXT
  FROM SYSIBM.SYSVIEWS
 WHERE NAME = 'EMP_PUBLIC'
   AND CREATOR = 'PAYROLL'
 ORDER BY SEQNO;

Large definitions can span catalog rows, so preserve sequence order when reconstructing the text. Catalog columns can differ across supported function levels and releases; select only documented columns available on the target subsystem.

Check view dependencies before a change

SELECT DNAME, DCREATOR, BNAME, BCREATOR, BTYPE
  FROM SYSIBM.SYSVIEWDEP
 WHERE DNAME = 'EMP_PUBLIC'
   AND DCREATOR = 'PAYROLL';

SYSVIEWDEP identifies base objects used by a view. Reverse the filter to find views that depend on a table before a DROP or incompatible ALTER. Also inspect SYSIBM.SYSPACKDEP and SYSIBM.SYSPLANDEP when application packages or plans might be affected.

Dependency checks are part of change planning, not a report to run after the application fails. The Db2 objects guide shows how tables, indexes, views, packages, and plans relate.

Drop a view carefully

DROP VIEW PAYROLL.EMP_PUBLIC;

DROP VIEW removes the view definition and its privileges. Db2 for z/OS can also drop dependent views, synonyms, triggers, functions, and materialized query tables according to the documented dependency rules, while dependent packages can be invalidated.

Do not assume that recreating a view immediately restores everything. Capture the definition and grants, list downstream views and packages, plan the maintenance sequence, and confirm rebind requirements before the drop.

Common Db2 view mistakes

• Using Db2 LUW catalog names such as SYSCAT.VIEWS on Db2 for z/OS.
• Assuming an ordinary view stores a snapshot of its result rows.
• Using SELECT * and creating an undocumented column contract.
• Expecting every join or aggregate view to accept INSERT, UPDATE, or DELETE.
• Granting view access without reviewing direct base-table privileges.
• Dropping a view without checking SYSVIEWDEP and package dependencies.
• Assuming a view improves performance without examining the access path.

Indexes belong to the base tables, not the ordinary view. Use the Db2 indexing guide when EXPLAIN shows costly scans, weak matching, or missing index-only access.

Db2 view checklist

• Give the view a qualified, stable schema and name.
• Select only the columns and rows required by its consumers.
• Use explicit, unique column names for expressions and joins.
• Confirm whether DML is permitted before coding updates through the view.
• Grant only the required view privileges.
• Review EXPLAIN for the resulting query rather than assuming a speed change.
• Check catalog dependencies before altering or dropping base objects.
• Keep the CREATE VIEW and GRANT statements under source control.

Official IBM references

• IBM: Db2 views concepts
• IBM: CREATE VIEW statement
• IBM: retrieving catalog information about views
• IBM: SYSIBM.SYSVIEWDEP catalog table
• IBM: DROP statement and dependency effects

Db2 views FAQ

Does a Db2 view store data?

An ordinary Db2 view does not store its own rows. It stores a named query definition, and Db2 returns the rows produced from the underlying tables or views when the view is referenced.

Which catalog tables describe Db2 for z/OS views?

SYSIBM.SYSVIEWS records view-definition text, while SYSIBM.SYSVIEWDEP records the tables, views, functions, and other objects on which a view depends.

Can a COBOL program select from a Db2 view?

Yes. Embedded SQL can reference a view like a table when the package owner or runtime authorization context has the required privileges. Static SQL packages also carry dependencies that must be considered when objects change.

Does DROP VIEW affect dependent objects?

Yes. Db2 for z/OS can cascade the drop to views and certain other objects defined on the view, remove its privileges, and invalidate dependent packages. Check dependencies before dropping it.

Treat a view as a maintained SQL interface: keep its definition, grants, dependent packages, and underlying access path together in every change review.