Dropping all views from a schema

[Former Member]

Hi,

We are facing a invalid objects problem , during DB2 upgrade to 10.5. We have been asked by sap to drop all view that belongs to a particular schema.

Could we get help on how to drop all views in a particular schema in db2 9.7 . we have around 300 views.

Regards,

Prakaash

[Frank-Martin]

Hi Prakaash,

the following Db2 CLP command will retrieve all views for a given <schema> e.g. SAPSIDSHD .

db2 " select viewschema, viewname from SYSCAT.VIEWS WHERE VIEWSCHEMA = '<schema>' "

You can extend this to retrieve all DB2 CLP DROP VIEW commands.

db2 " select 'db2 '' DROP VIEW ' || RTRIM(viewschema) || '.' || x'22' || RTRIM(viewname) || x'22' ||  ' ''' from SYSCAT.VIEWS WHERE VIEWSCHEMA =  '<schema>' "

If this works on UNIX you can simply pipe the output to a shell.

db2 " select 'db2 '' DROP VIEW ' || RTRIM(viewschema) || '.' || x'22' || RTRIM(viewname) || x'22' ||  ' ''' from SYSCAT.VIEWS WHERE VIEWSCHEMA =  '<schema>' " | sh

Regards

                Frank

[hugo_amo]

Hi Prakaash,

Here is a procedure which will help you to drop all views from the specific schema:

- Create a simple text file and call it clean_schema.sql

- Paste the following content into that file and save it:

select 'drop view '||rtrim(viewschema)||'.'||rtrim(viewname) from syscat.views where viewschema = '<schema>'

- Call this file as follows with DB2:
db2 -xf clean_schema.sql > drop_objects_from_schema.sql

- Check the contents of the file drop_objects_from_schema.sql to be sure that only objects from your schema are being dropped

- Execute the second SQL file as follows:

db2 -xvf  drop_objects_from_schema.sql > dropped.out

Be careful using procedures like above. They will query all view objects for a specific schema and drop them without reasking. Better to have a good backup before executing statements like this.

Hope this helps.

Regards,

Hugo