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.