All Forums Database
Harpreet Singh 101 posts Joined 10/11
30 May 2013
View validity check

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.

ulrich 816 posts Joined 09/09
30 May 2013

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

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


select * 
from invalid_views
order by 1,2


feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Harpreet Singh 101 posts Joined 10/11
30 May 2013

Thanks Ulrich. It got me all invalid views.

You must sign in to leave a comment.