ALTER VIEW statement

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 for ALTER VIEW

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for ALTER VIEW

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

  • Ownership of the view
  • SYSADM authority
  • SYSCTRL authority
  • System DBADM

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 for ALTER VIEW

Read syntax diagramSkip visual syntax diagramALTER VIEWview-nameREGENERATE USINGAPPLICATION COMPATIBILITYAPPLCOMPATapplcompat-level

Description for ALTER VIEW

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.

Generally, the REGENERATE keyword is used only for specific situations, such as when implicit regeneration fails for routines or objects, or Db2 maintenance requires objects or routines to be regenerated. For more information, see When to regenerate Db2 database objects and routines.

USING APPLICATION COMPATIBILITY applcompat-level
Specifies that the object is regenerated under applcompat-level application compatibility rules. The ALTER statement fails if it includes any syntax, semantics, or options that require a higher application compatibility level.

If USING APPLICATION COMAPTIBILITY is omitted, the regeneration uses the APPLCOMPAT value of the applicable SYSIBM.SYSENVIRONMENT catalog table row.

The following applcompat-level values can be specified:
VvvRrMmmm

Compatibility with the behavior of the identified Db2 function level. For example, V13R1M507 specifies compatibility with the highest available Db2 13 function level. The equivalent function level or higher must be activated.

Start of changeFor the new capabilities that become available in each application compatibility level, see: End of change

Tip: Start of changeExtra program preparation steps might be required to increase the application compatibility level for applications that use data server clients or drivers to access Db2 for z/OS®. For more information, see Setting application compatibility levels for data server clients and drivers.End of change
V12R1
Compatibility with the behavior of Db2 12 function level 500. This value has the same result as specifying V12R1M500.
V11R1
Compatibility with the behavior of Db2 11 new-function mode.
V10R1
Compatibility with the behavior of DB2® 10 new-function mode. For more information, see V10R1 application compatibility level.

Examples for ALTER VIEW

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;