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).

Answer 3

Nothing happened. Just don't use that view. You can recreate the table again to use the view again later.

Answer 4

It becomes inoperative. Same information can be found using following query:

SELECT viewscheama,viewname,valid FROM syscat.views

. For the perticular view , if the "Valid" column has any value apart of 'Y' , then the view will be inoperative.