|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/8/2008 12:10:50 PM
Posts: 4,
Visits: 3
|
|
In our development and test environments, it is very common for a view to be created and then the underlying object changed or dropped. This renders the view invalid and any action attempted with it results in one of the following errors:
*** Failure 3807 Object '___' does not exist.
*** Failure 3810 Column/Parameter '___' does not exist.
Is there some way to query the data dictionary to find these invalid views? Having a maintenance script that tries to access every view in the database seems horribly inefficient.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/11/2006 7:59:00 PM
Posts: 5,
Visits: 1
|
|
There is no straight forward answer to your problem
How ever i used to generate dynamic sql statements
for each view like sel * from view1 where 1 = 2;
I used put all this in bteq script.
use ''.SET ERROROUT STDOUT;" after your .log on credentials.
When you run the script and open your log file you will find where ever you see those errors numbers, those are the INVALID vws
Hope this helps
Ranga
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/5/2009 2:58:22 PM
Posts: 134,
Visits: 13
|
|
I wrote a bteq script which helps in identifying invalid views under a particular parent (eg SOUTHEASTDW in this case).
It is run in windows enviornement (file/etl server) but can easily be changed to an UNIX script. I have used a global temporary table called INVALID_VIEWS but may also be a perm table (if needed).
This can also be changed for capturing other types of errors.
In this sample example, I have captured only 3807.
Use it if you want to identify invalid views.
see attachment for script.
Vinay
|
|
|
|