When you drop and recreate a table, DB2 marks any views that query it inoperative. You will get the following error message if you try to query one of them:
View or materialized query table "TRENDADM.IWM_CONVERSION" Â cannot be used because it has been marked inoperative.. Â SQLCODE=-575, SQLSTATE=51024, DRIVER=3.61.65
This has changed somewhat in DB2 9.7 with the introduction of DB2_DDL_SOFT_INVAL and auto_reval configuration settings. There are also ways to avoid it using transactions, CREATE OR REPLACE statements, and other measures.
However, suppose you do have some inoperative views, and youâ€™d like to get a list of them. The following query will fetch the list:
SELECT viewname, viewschema, valid FROM syscat.views WHERE viewschema = 'TRENDADM' AND valid <> 'Y' ;