What happens to an existing DB2 view, if the table is dropped ?

Question

If we have created a view on an existing DB2 table and then drop the table. What will happen to the view ?

Answer 1

The view becomes invalid/inoperative. Attempts to select from it will fail.

To try it:

create table TEST_TABLE (
TEST_COL INTEGER
);

INSERT INTO TEST_TABLE VALUES(1);

SELECT * FROM TEST_TABLE;

create view TEST_VIEW AS
SELECT * FROM TEST_TABLE;

SELECT * FROM TEST_VIEW;

DROP TABLE TEST_TABLE;

SELECT * FROM TEST_VIEW;

The last statement gives the error:

[IBM][CLI Driver][DB2/NT] SQL0575N  View or materialized query table
"TEST_VIEW" cannot be used because it has been marked inoperative.
SQLSTATE=51024

Answer 2

When a view is invalidated, as shown in the above example, DB2 will allow you to recreate that view without dropping it first. This makes it possible to re-run your view DDL files (or simply dump the TEXT column of SYSCAT.VIEWS and execute that).