Problem(Abstract)Cannot access DB2 database VIEW called DEVUSER.V_APPRBOOKTEMPL although
the DB2 database TABLE called NCIM.INTERFACE is accessible.
Symptom
db2 => select * from DEVUSER.V_APPRBOOKTEMPL
SQL0575N View or materialized query table "DEVUSER.V_APPRBOOKTEMPL" cannot be used because it has been marked inoperative. SQLCODE=-575, SQLSTATE=51024, DRIVER=3.62.56
EnvironmentDB2 DEVUSERdatabase
Diagnosing the problemCheck to see if OTHER database views are inoperative as well ...
Use the SQL command below.
[db2inst1@redhat4 ~]$ db2 "select viewschema,viewname,valid from syscat.views where viewschema like 'DE%' and valid <> 'Y'"
If the database column valid = "Y", the view is OPERATIVE, but if the
database column valid is "X" or "N" or any other value, the database
view is INOPERATIVE.
The following command will tell us WHO has ACCESS / PRIVILEGES for
the NCIM.INTERFACES database VIEW, as an example.
[db2inst1@redhat4 ~]$ db2 "select definer from syscat.views where viewschema='NCIM' and viewname='INTERFACES'"
Try accessing the SAME database view as the definer userid reported from
the command above to see if the INOPERATIVE error still occurs for the
definer as well ..
Resolving the problem
The following statement is extracted from DB2 documentation:
"When a CREATE VIEW statement is executed that creates a VIEW with the
same schema name and view name as an existing inoperative view, the new
view replaces the inoperative view. ..Since all privileges held on a view are
revoked when a view becomes inoperative, the definer of the view must
grant these privileges again after recreating the inoperative view."
So, CREATE the VIEW again from the commands located in the database
creation script and then GRANT privileges to the VIEW as appropriate.
This should restore the VIEW to OPERATIVE.
