Altering Db2 views

To alter a view, you must drop the view and create a new view with your modified specifications.

Procedure

To drop and re-create a view:

  1. Issue the DROP VIEW SQL statement.
  2. Commit the drop.
    When you drop a view, Db2 also drops the dependent views.
  3. Re-create the modified view using the CREATE VIEW SQL statement.

What to do next

Attention: When you drop a view, Db2 invalidates packages that are dependent on the view and revokes the privileges of users who are authorized to use it. Db2 attempts to rebind the package the next time it is executed, and you receive an error if you do not re-create the view.

To tell how much rebinding and reauthorizing is needed if you drop a view, see the following table.

Table 1. Catalog tables to check after dropping a view
Catalog table What to check
SYSIBM.SYSPACKDEP Packages dependent on the view
SYSIBM.SYSVIEWDEP Views dependent on the view
SYSIBM.SYSTABAUTH Users authorized to use the view