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.
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
.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
select 'show select * from ' || trim(databasename) || '.' || trim(tablename) || ';',
'.if errorcode = 0 then .goto next;',
'insert into invalid_views values ('''||trim(databasename)||''',''' ||trim(tablename)||''');',
where tablekind = 'V'
order by 1;
.run file = /yourpath/out.bteq
.FOLDLINE OFF ALL
order by 1,2