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

3 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.

New Member

Re: View validity check

My solution by stored procedure:

create multiset table invalidViews (databaseName varchar(128), viewName varchar(128));

replace procedure getInvalidViews(dbName varchar(128))
dynamic result sets 1
begin
  declare xRes varchar(1000);
  declare cRes cursor with return only for sRes;

  delete from invalidViews;
  
  FOR i AS c cursor for
    select db.databaseName
         , tab.tvmName viewName
         , 'select * from ' || DatabaseName || '.' || viewName qry
      from dbc.tvm tab
      join dbc.dbase db on db.databaseId = tab.databaseId
     where tableKind = 'V'
       and databaseName = dbName
     order by db.databaseName
         , tab.tvmName
  DO
    begin
      declare xCmd varchar(1000);
      DECLARE cCmd CURSOR FOR sCmd;
     
      DECLARE exit HANDLER FOR SqlException 
      begin 
        insert into invalidViews values (i.databaseName, i.viewName);
      end;   
    
      SET xCmd = i.qry;
      PREPARE sCmd FROM xCmd;
    end;
  END FOR;
  
  set xRes = 'select * from invalidViews';
  PREPARE sRes FROM xRes;
  open cRes;
end;

call getInvalidViews('MyDatabase');