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.

Db2 View Classification: Simple, Join, Aggregate, and Updatable Views



Db2 views are classified by definition and update rules.

A COBOL program can select from a Db2 view exactly as it selects from a table, but not every view behaves the same way. A simple view over one table might support updates. A join view or aggregate view is normally read-only. A security view might hide salary columns from most programs while still showing employee names and departments.

This refresh keeps the existing classification URL and focuses on the practical question: what kind of view are you using, and what does that mean for COBOL SQL, data changes, security, and performance?

What Is a Db2 View?

A Db2 view is a named query definition. IBM's CREATE VIEW documentation states that the statement creates a view on tables or views at the current server, and the fullselect defines the rows returned by the view.

A view does not usually store its own base-table rows. When a program queries the view, Db2 processes the view definition with the SQL statement that references it.

Simple Views

A simple view is based on one table and usually selects a subset of columns or rows. It is often used to give programs a cleaner interface or to hide columns that should not be exposed.

CREATE VIEW HR.ACTIVE_EMP_VIEW AS
  SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT
    FROM HR.EMPLOYEE
   WHERE STATUS = 'A';

A COBOL program can query this view without carrying the active-status predicate in every cursor.

EXEC SQL
    SELECT LASTNAME, WORKDEPT
      INTO :WS-LASTNAME, :WS-DEPT
      FROM HR.ACTIVE_EMP_VIEW
     WHERE EMPNO = :WS-EMPNO
END-EXEC.

Join Views

A join view combines columns from two or more tables. It can make reporting SQL easier to read, but it can also hide join cost from the caller.

CREATE VIEW HR.EMP_DEPT_VIEW AS
  SELECT E.EMPNO,
         E.LASTNAME,
         D.DEPTNAME
    FROM HR.EMPLOYEE E
    JOIN HR.DEPARTMENT D
      ON D.DEPTNO = E.WORKDEPT;

Before using a join view in a high-volume batch program, explain the final SQL that references the view. Do not assume the view is cheap because the outer query looks short.

Aggregate Views

An aggregate view uses functions such as COUNT, SUM, MIN, or MAX, usually with GROUP BY. These views are useful for summaries, dashboards, and control totals.

CREATE VIEW HR.DEPT_HEADCOUNT AS
  SELECT WORKDEPT,
         COUNT(*) AS EMP_COUNT
    FROM HR.EMPLOYEE
   GROUP BY WORKDEPT;

Aggregate views are not a replacement for proper summary tables when the data volume is large and the summary is queried constantly. In those cases, discuss materialized query tables or batch-maintained summary tables with the DBA.

Read-Only and Updatable Views

The most important classification for application code is whether a view can be updated. A simple one-table view may allow insert, update, or delete operations when it satisfies Db2 rules. Views that include joins, aggregates, grouping, distinct results, or certain expressions are normally read-only.

View pattern	Typical classification	Application impact
One table, direct columns	Potentially updatable	Can sometimes support update through the view.
Join view	Usually read-only	Use for selection/reporting, not data maintenance.
Aggregate view	Read-only	Use for summary queries.
View with calculated columns	Often read-only or partly restricted	Do not assume every column can be updated.

WITH CHECK OPTION

WITH CHECK OPTION matters when a view is updatable. It tells Db2 to reject inserts or updates through the view when the resulting row would not satisfy the view definition.

CREATE VIEW HR.ACTIVE_EMP_MAINT AS
  SELECT EMPNO, FIRSTNME, LASTNAME, STATUS
    FROM HR.EMPLOYEE
   WHERE STATUS = 'A'
  WITH CASCADED CHECK OPTION;

Without a check option, an update through a view can sometimes make the row disappear from that same view. That behavior is confusing in maintenance programs, so make the rule explicit when the view is intended for updates.

Security Views

A security view exposes only the rows and columns a caller should see. For example, one view might show employee number, name, and department while hiding salary and tax identifiers.

Security views are only part of the control. Grants, ownership, package authorization, and application roles still matter. Do not treat a view as a complete security model by itself.

Performance Checks

A view can make SQL easier to read, but Db2 still has to process the underlying fullselect. For performance work, explain the complete statement that references the view.

• Check whether predicates can be pushed into the view definition.
• Review indexes on base tables, not on the view name.
• Watch for hidden joins or aggregations in views used by batch jobs.
• Avoid stacking several views if it hides the real query shape.
• Use the related Db2 SQL Optimization Tips for COBOL Programs guide for access-path checks.

FAQ

Is a Db2 view the same as a table?

No. A view is a named query definition over tables or other views. The base-table rows remain in the underlying table spaces.

Can COBOL update a Db2 view?

Sometimes. The view must satisfy Db2 rules for updatable views, and the program must have the required privileges. Join and aggregate views are normally read-only.

Does a view improve performance?

Not by itself. A view can simplify SQL and security, but Db2 still processes the underlying query. Use EXPLAIN to check the full access path.

Classify the view before using it in application code. A reporting view, a security view, and an updatable maintenance view need different tests and different grants.