ALTER VIEW statement

The ALTER VIEW statement modifies an existing view by altering a reference type column to add a scope. The ALTER VIEW statement also enables or disables a view for use in query optimization.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • ALTERIN privilege on the schema of the view
  • Owner of the view to be altered
  • CONTROL privilege on the view to be altered
  • SCHEMAADM authority on the schema of the view
  • DBADM authority
To enable or disable a view for use in query optimization, the privileges held by the authorization ID of the statement must also include at least one of the following authorities for each of the tables or underlying tables of views that are referenced in the FROM clause of the view fullselect:
  • ALTER privilege on the table
  • ALTERIN privilege on the schema of the table
  • SCHEMAADM authority on the schema of the table
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagramALTER VIEWview-nameALTERCOLUMNcolumn-nameADD SCOPEtyped-table-nametyped-view-nameENABLEDISABLEQUERY OPTIMIZATION

Description

view-name
Specifies the view that is to be changed. It must be a view that is described in the catalog.
ALTER COLUMN column-name
Specifies the name of the column that is to be altered. The column-name must identify an existing column of the view (SQLSTATE 42703). The name cannot be qualified.
ADD SCOPE
Adds a scope to an existing reference type column that does not already have a scope defined (SQLSTATE 428DK). The column must not be inherited from a superview (SQLSTATE 428DJ).
typed-table-name
Specifies the name of a typed table. The data type of column-name must be REF(S), where S is the type of typed-table-name (SQLSTATE 428DM). No checking is done of any existing values in column-name to ensure that the values actually reference existing rows in typed-table-name.
typed-view-name
Specifies the name of a typed view. The data type of column-name must be REF(S), where S is the type of typed-view-name (SQLSTATE 428DM). No checking is done of any existing values in column-name to ensure that the values actually reference existing rows in typed-view-name.
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether or not the view and any associated statistics are to be used to improve the optimization of queries. DISABLE QUERY OPTIMIZATION is the default when a view is created.
ENABLE QUERY OPTIMIZATION
Specifies that the view includes statistics that can be used to improve the optimization of queries that involve this view or queries that include subqueries similar to the fullselect of this view.
DISABLE QUERY OPTIMIZATION
Specifies that the view and any associated statistics are not to be used to improve the optimization of queries.

Rules

  • A view cannot be enabled for query optimization if:
    • The view directly or indirectly references a materialized query table (MQT). Note that an MQT or statistical view can reference a statistical view
    • The view directly or indirectly references a catalog table.
    • It is a typed view

Notes

  • To be considered for optimizing a query, a view:
    • Cannot contain an aggregation or distinct operation
    • Cannot contain a union, except, or intersect operation
    • Cannot contain an OLAP specification
  • If a view is altered to disable query optimization, cached query plans that used the view for query optimization are invalidated. If a view is altered to enable query optimization, cached query plans are invalidated if they reference the same tables as the newly enabled view references, either directly or indirectly through other views. The invalidation of these cached query plans results in implicit revalidation that takes the view's changed query optimization property into account.

    The query optimization property for a view has no impact on static embedded SQL statements.

  • If a view is enabled for query optimization, and the view is invalidated, query optimization is disabled. Use ALTER VIEW after the view is revalidated to enable query optimization again.