Find a list of views marked inoperative

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'
;