DROP VIEW without drop dependent views

Question

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?

I can't use ALTER, because I need to change Join and Where conditions — Nifriz, Sep 28 '21 at 09:16
I see. This is why its a good idea to have script that can re-create the whole environment if needed, or parts of it. — jarlh, Sep 28 '21 at 09:19
What platform and version of Db2? You have a `db2-400` tag, that platform offers `CREATE OR REPLACE VIEW` which should automatically recreate dependent views. — Charles, Sep 28 '21 at 13:29

score 1 · Accepted Answer · answered Sep 28 '21 at 10:50

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.

DROP VIEW without drop dependent views

1 Answers1