Search code examples
sqlviewibm-midrangedb2-400drop

DROP VIEW without drop dependent views

I need to change a VIEW, so I have to DROP and CREATE it.

DB2 manual say "Any views that are dependent on the view being dropped are made inoperative."

I try but dependent view are dropped, not made inoperative.

Is there a way to bypass the dependent views drop?

Solution

  • sysibm.views provides access to the view_definition

    SELECT char(table_SCHEMA,10) libname,       
       char(table_NAME,30) view_name,       
       length(a.view_definition) lgth ,     
       cast(substr(a.view_definition,1,500) 
             as varchar(5000)) view_defn    
FROM   sysibm.views a

    you should be able to first retrieve the definition of the dependent views. Then drop and recreate the view. And finally, create the dependent views from the saved view_definition.