View validity check

Database
Enthusiast

View validity check

Hi,

How Can I check which views in particular database are invalid using system tables? Invalid means that objects referenced by view are not present now and were dropped so view will give error now saying table does not exist.

Thanks

2 REPLIES
Senior Supporter

Re: View validity check

its not possible to get this info out of dbc

it can change any time and maybe in 5 min the view is valid again.

try the following bteq script

.logon system/user,passwd;

.set width 500;
create volatile table invalid_views (databasename varchar(30), tablename varchar(30)) unique primary index (databasename, tablename) on commit preserve rows;

.OS rm /yourpath/out.bteq

.export data file=/yourpath/out.bteq
.RECORDMODE OFF
.FOLDLINE ALL

select 'show select * from ' || trim(databasename) || '.' || trim(tablename) || ';',
'.if errorcode = 0 then .goto next;',
'insert into invalid_views values ('''||trim(databasename)||''',''' ||trim(tablename)||''');',
'.label next;'
from dbc.tables
where tablekind = 'V'
order by 1;

.export reset

.run file = /yourpath/out.bteq

.FOLDLINE OFF ALL

select *
from invalid_views
order by 1,2
;
Enthusiast

Re: View validity check

Thanks Ulrich. It got me all invalid views.