DB2 CREATE VIEW

What CREATE VIEW creates

CREATE VIEW adds a view definition to the Db2 catalog. A regular view does not copy the selected rows into separate storage. When an application selects from the view, Db2 expands or otherwise incorporates the stored query definition, checks authorization, optimizes the complete statement, and reads the underlying tables or views. Changes to base-table data are therefore visible through the view when they satisfy its query.

This distinction affects performance and operations. A view is not automatically a snapshot, cache, backup, or index. It can simplify SQL and enforce an access boundary, but its underlying query still needs efficient predicates, useful indexes, current statistics, and sensible access paths. If the requirement is to store and refresh a physical query result, investigate a materialized query table rather than assuming a regular view does so.

Prerequisites and authority

Start with a tested SELECT statement and an explicit authorization plan. The statement authorization ID needs authority to create the view in the chosen schema and the required privileges on every referenced object. Highly privileged administrative authorities can satisfy those checks, but application deployment should normally use narrowly assigned privileges instead of SYSADM-level access. Exact authorization alternatives vary by Db2 version, ownership model, and site security policy, so confirm the CREATE VIEW authority rules for your installed release.

Privilege provenance matters beyond the initial successful DDL. Determine which primary or secondary authorization ID owns the view, how required privileges are held, who can grant access to the view, and what happens if an underlying privilege is revoked. Do not test only with a DBA identity and then assume the deployment ID will behave the same way.

CREATE VIEW syntax and explicit column names

The core syntax names the new view, optionally gives every result column an explicit name, and follows AS with a fullselect. The schema qualifier is strongly recommended in repeatable DDL. The number of names in the view column list must equal the number of expressions returned by the SELECT.

1
2
3
4
5
6
7
CREATE VIEW schema-name.view-name
       (view-column-1, view-column-2, ...)
AS
SELECT expression-1, expression-2, ...
  FROM schema-name.source-table
 WHERE search-condition
WITH CHECK OPTION;

Explicit view columns form a stable public contract. They prevent expressions from receiving unclear generated labels, distinguish same-named columns from joined tables, and tell reviewers exactly what consumers will see. They also reduce accidental coupling to source-column naming. Avoid SELECT * in durable view definitions. A wildcard hides the intended interface, makes reviews harder, and creates maintenance surprises when the base table changes.

Create a simple base-table view

Assume the sample table APP.CUSTOMER contains CUSTOMER_ID, CUSTOMER_NAME, REGION_CODE, STATUS, CREDIT_LIMIT, and INTERNAL_RISK_CODE. The following view exposes only the fields needed by a customer directory. It deliberately omits the internal risk value and uses a fixed status predicate.

1
2
3
4
5
6
7
8
9
CREATE VIEW APP.ACTIVE_CUSTOMER
       (CUSTOMER_ID, CUSTOMER_NAME, REGION_CODE, CREDIT_LIMIT)
AS
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       REGION_CODE,
       CREDIT_LIMIT
  FROM APP.CUSTOMER
 WHERE STATUS = 'A';

This is a projection and filter over one base table. Selecting from it is straightforward, and Db2 can often map an update of a directly exposed column back to one customer row. However, updatability is a property to verify, not a promise made merely because CREATE VIEW succeeded. Base-table constraints, omitted required columns, expressions, and the exact fullselect all affect which INSERT, UPDATE, or DELETE operations are valid.

1
2
3
4
5
6
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       REGION_CODE
  FROM APP.ACTIVE_CUSTOMER
 WHERE REGION_CODE = 'NE'
 ORDER BY CUSTOMER_NAME;

Create a join view

Views are useful for centralizing common joins. Suppose APP.CUSTOMER references APP.REGION through REGION_CODE. A reporting view can present the customer and readable region name together so consumers do not repeat the join. Qualifying every column is important because joins commonly introduce duplicate names.

1
2
3
4
5
6
7
8
9
10
CREATE VIEW APP.CUSTOMER_REGION
       (CUSTOMER_ID, CUSTOMER_NAME, REGION_CODE, REGION_NAME)
AS
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       C.REGION_CODE,
       R.REGION_NAME
  FROM APP.CUSTOMER AS C
  JOIN APP.REGION AS R
    ON R.REGION_CODE = C.REGION_CODE;

The join view is an excellent read interface, but do not assume it supports writes. A joined result combines columns from multiple objects, and Db2 must be able to determine an unambiguous target row and table for a data change. Many join views are read-only or permit fewer operations than a simple view. For application design, expose a join view for queries and use a controlled table, procedure, or simple updatable view for changes unless testing and documentation establish the required behavior.

Read-only and updatable views

An updatable view lets Db2 translate a data-change statement against the view into a change against an underlying base table. The easiest case is a single-table fullselect containing direct column references and an optional WHERE predicate. A view becomes read-only when its result no longer maps cleanly to one source row. Aggregates, GROUP BY, HAVING, DISTINCT, set operations, and derived values are common reasons.

Even when a view is updatable, an INSERT can fail if it does not supply a required base column that is hidden by the view. The omitted column must accept null or receive a default, generated value, identity value, or another valid Db2-provided value. UPDATE can also be restricted for derived columns. Test each intended operation with the deployment authorization and representative constraints instead of treating “updatable” as meaning “every data-change statement will work.”

Protect filtered views with WITH CHECK OPTION

Without WITH CHECK OPTION, an update through a filtered view might change a row so that it no longer satisfies the view predicate. The operation can appear to make the row disappear from the application's own result. WITH CHECK OPTION makes the boundary explicit: rows inserted or updated through the view must remain visible through that view after the change.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE VIEW APP.ACTIVE_CUSTOMER_MAINT
       (CUSTOMER_ID, CUSTOMER_NAME, REGION_CODE, STATUS, CREDIT_LIMIT)
AS
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       REGION_CODE,
       STATUS,
       CREDIT_LIMIT
  FROM APP.CUSTOMER
 WHERE STATUS = 'A'
WITH CHECK OPTION;
 
-- Rejected because the resulting row would not satisfy STATUS = 'A':
UPDATE APP.ACTIVE_CUSTOMER_MAINT
   SET STATUS = 'I'
 WHERE CUSTOMER_ID = 10020;

CHECK OPTION applies to changes made through the view. It is not a substitute for a base table CHECK constraint and does not stop a separately authorized process from changing APP.CUSTOMER directly. For nested views, Db2 also supports check-option behavior that can involve predicates from underlying views; use the exact LOCAL or CASCADED semantics supported by your release when layered view boundaries matter.

Security and authorization design

A view can expose a smaller security surface than its source. For example, users can be granted SELECT on APP.ACTIVE_CUSTOMER without being granted SELECT on sensitive columns in APP.CUSTOMER. A row predicate can limit the population, and separate views can expose different interfaces to operations, reporting, and audit teams. This is valuable, but a view is only one part of authorization design.

• Grant view privileges to roles or authorization IDs according to least privilege.
• Do not grant direct base-table access when it would bypass the intended view boundary.
• Remember that a hard-coded predicate is not automatically a per-user row-security policy.
• Review ownership and privilege dependencies before revoking source-object authority.
• Test static SQL packages and dynamic SQL identities because their privilege checks differ operationally.

If the policy depends on the identity of the current user, tenant, or trusted context, evaluate Db2 row permissions, column masks, roles, trusted contexts, and application controls rather than building an improvised security scheme from ordinary views alone. Also avoid exposing sensitive data through expressions that appear harmless but can be recombined or inferred.

Dependencies and deployment order

A view depends on every table, view, function, or other object referenced by its definition. Other views, aliases, routines, triggers, and static SQL packages can in turn depend on the view. A successful CREATE statement proves that the current definition is acceptable; it does not prove that a future drop or source-table alteration is harmless.

Build deployment order from the bottom up: source tables first, then lower-level views, then views that depend on them, followed by grants and package validation. For removal or replacement, analyze the order in reverse and plan for invalidation or rebind effects. Keep the DDL, COMMENT statements, GRANT statements, and verification queries together so recreation does not silently lose documentation or access.

Verify the view and catalog metadata

Verification should cover behavior, metadata, authorization, and access paths. First run a small SELECT and compare representative rows with the equivalent base-table query. Then inspect the catalog under the exact schema and name. Db2 for z/OS stores view information in SYSIBM.SYSVIEWS, object identity in the broader table catalog, and dependency information in catalog tables such as SYSIBM.SYSVIEWDEP. Long view text may be represented in catalog-specific forms, so consult the catalog reference for your release.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT CREATOR,
       NAME,
       TEXT
  FROM SYSIBM.SYSVIEWS
 WHERE CREATOR = 'APP'
   AND NAME = 'ACTIVE_CUSTOMER';
 
SELECT *
  FROM SYSIBM.SYSVIEWDEP
 WHERE BCREATOR = 'APP'
   AND BNAME = 'ACTIVE_CUSTOMER';
 
SELECT COUNT(*) AS ACTIVE_COUNT
  FROM APP.ACTIVE_CUSTOMER;

After metadata verification, test with an ordinary application authorization ID. Confirm that allowed SELECT or data-change statements succeed and forbidden direct access fails. Explain representative SQL with the view in place and review the resulting access path. A view improves abstraction, but a poorly selective join or missing base-table index can still make the final query expensive.

ALTER VIEW versus DROP and CREATE

Do not assume Db2 for z/OS offers a universal CREATE OR REPLACE VIEW operation or that ALTER VIEW can freely replace the stored SELECT text. ALTER VIEW capabilities are release-dependent and are generally narrower than redefining the complete logical query. When columns, joins, expressions, or predicates change, a controlled DROP VIEW followed by CREATE VIEW may be required.

DROP and recreate is not a text-edit operation. It can affect dependent objects, package validity, object identifiers, cached statements, grants, ownership expectations, labels, comments, and operational monitoring. Before the change, extract the existing definition and grants, query dependencies, identify packages that reference the view, and decide whether a compatibility view or staged new name would reduce risk. After recreation, restore comments and privileges, run authorization tests, and follow site procedures for package validation or rebind.

Prefer additive interface changes when possible

A safer production pattern is often to create APP.ACTIVE_CUSTOMER_V2, grant it to a pilot consumer, migrate packages, and retire the old view only after dependency reports are clear. This uses an additive change rather than forcing all consumers to accept a new column order, type, predicate, or join at once. If the original name must remain stable, schedule a controlled replacement and preserve an exact rollback script.

Common CREATE VIEW errors

Db2 SQLCODE and SQLSTATE values provide the precise diagnosis, but the same design errors recur. Capture the complete message, statement, authorization ID, current schema, and package context before changing DDL. Guessing from a shortened application message can lead to unnecessary grants or an incorrect object replacement.

Production checklist

• Qualify the view and all source objects with intentional schemas.
• Use explicit, unique view-column names and avoid SELECT *.
• Run and explain the underlying SELECT before creating the view.
• Document whether the interface is read-only or which write operations are supported.
• Use WITH CHECK OPTION when writes must remain inside a filtered view boundary.
• Grant only required view privileges and test without broad base-table access.
• Record dependencies, grants, comments, package consumers, and recreation order.
• Verify catalog rows, representative results, authorization behavior, and access paths.

Explain it like I'm 5

Imagine a big toy cupboard is the base table. A view is a special window in the cupboard door. One window shows only blue toys, while another shows toy names without showing the expensive price stickers. The toys are still in the same cupboard; the window does not make copies. WITH CHECK OPTION is a rule saying, “If you put a toy through the blue-toy window, it must still be blue.” Permissions decide who may look through each window and who may open the whole cupboard.

Exercises

1. Create APP.CUSTOMER_DIRECTORY with explicit columns for customer ID, name, and region. Omit credit and internal-risk data, then verify the definition in SYSIBM.SYSVIEWS.
2. Create a view of customers whose STATUS is A and add WITH CHECK OPTION. Test an allowed name change and a rejected change from status A to status I.
3. Join APP.CUSTOMER to APP.REGION in a reporting view. Explain why every source column is qualified and why the result should not automatically be treated as updatable.
4. Design grants so REPORT1 can select the directory view but cannot select the hidden base-table columns. List the tests that prove the boundary works.
5. Write a replacement plan for adding a derived display column to an existing view. Include dependency discovery, package review, grants, comments, verification, and rollback.