DB2 10.5 for Linux, UNIX, and Windows

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
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
DBADM authority

Syntax


>>-ALTER VIEW--view-name---------------------------------------->

     .-----------------------------------------------------------------.     
     V        .-COLUMN-.                                               |     
>--+---ALTER--+--------+--column-name--ADD SCOPE--+-typed-table-name-+-+-+-><
   |                                              '-typed-view-name--'   |   
   '-+-ENABLE--+--QUERY OPTIMIZATION-------------------------------------'   
     '-DISABLE-'

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.