DB2 fix : SQL0575N View or materialized query table cannot be used because it has been marked inoperative

29 March,2016 by Jack Vamvas

A user reported the following error , when executing a SELECT statement on a VIEW.  

[user1@server1 ~]$ db2 "select count(*) from MYSCHEMA.MYTABLE”
SQL0575N View or materialized query table "MYSCHEMA.MYTABLE" cannot
be used because it has been marked inoperative. SQLSTATE=51024

Another way to check on the error message and as a way to validate the initial error message is to check the the syscat.view valid column. If you see anything other than a Y , then action is required

db2 "SELECT viewschema,viewname,valid FROM syscat.views where viewname = 'MYTABLE'"

The main reason for the error is because an alias, privilege, table or view dependant on by the DB2 VIEW has been changed.

Usually recreating the VIEW and resetting the privileges will fix the issue. If problems persist , check that the underlying objects such as tables referenced are still valid

Once the VIEW is reset , connect on as a valid user and execute a SELECT statement to confirm the VIEW is valid again/

Read More on SQL Views

DB2 How To get View definition - DBA DB2

Trace sql statements in DB2 database - DBA DB2

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 fix : SQL0575N View or materialized query table cannot be used because it has been marked inoperative

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright