Recovering inoperative views

An inoperative view is a view that is no longer available for SQL statements.

About this task

When the auto_reval database configuration parameter is set to disabled, views can become inoperative:
  • As a result of a revoked privilege on an underlying table
  • If a table, alias, or function is dropped.
  • If the superview becomes inoperative. (A superview is a typed view upon which another typed view, a subview, is based.)
  • When the views they are dependent on are dropped.

If you do not want to recover an inoperative view, you can explicitly drop it with the DROP VIEW statement, or you can create a view with the same name but a different definition.

An inoperative view has entries only in the SYSCAT.TABLES and SYSCAT.VIEWS catalog views; all entries in the SYSCAT.TABDEP, SYSCAT.TABAUTH, SYSCAT.COLUMNS, and SYSCAT.COLAUTH catalog views are removed.

Procedure

The following steps can help you recover an inoperative view:

  1. Determine the SQL statement that was initially used to create the view. You can obtain this information from the TEXT column of the SYSCAT.VIEW catalog view.
  2. Set the current schema to the content of the QUALIFIER column.
  3. Set the function path to the content of the FUNC_PATH column.
  4. Re-create the view by using the CREATE VIEW statement with the same view name and same definition.
  5. Use the GRANT statement to regrant all privileges that were previously granted on the view. (Note that all privileges granted on the inoperative view are revoked.)