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

One thought on “Find a list of views marked inoperative

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.