ALTER VIEW

The ALTER VIEW statement regenerates a view using an existing view definition at the current server. ALTER VIEW is primarily used during DB2® migration or when DB2 maintenance is applied. To change a view definition (for example, to add additional columns), you must drop the view and create a new view using the CREATE VIEW statement.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set that is defined below must include at least one of the following:

  • Ownership of the view
  • SYSADM authority
  • SYSCTRL authority
  • Start of changeSystem DBADMEnd of change
Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is the union of the privilege sets that are held by each authorization ID and role of the process.

Syntax

Read syntax diagram
>>-ALTER VIEW--view-name--REGENERATE---------------------------><

Description

view-name
Identifies the view to be regenerated. The name must identify a view that exists at the current server.
REGENERATE
Specifies that the view is to be regenerated. The view definition in the catalog is used, and existing authorizations and dependent views are retained. The catalog is updated with the regenerated view definition. If the view cannot be successfully regenerated, an error is returned.

Examples

Check the catalog to find any views that were marked with view regeneration errors during catalog migration:
   SELECT CREATOR,NAME FROM SYSIBM.SYSTABLES
     WHERE TYPE = 'V' AND STATUS = 'R' AND TABLESTATUS = 'V';
Assume that the query returned MYVIEW as the name of a view with a regeneration error. Issue an ALTER VIEW statement to regenerate the view:
   ALTER VIEW MYVIEW REGENERATE;