Is there a efficient way to delete every view/function/table/sp from a database?

Question

In a DB2 federated database (based on remote servers and nicknames), I need to clean up the model and recreate it from another database. I need to delete every database object except those servers and nicknames.

I know how to retrieve the list of objects from the SYSCAT schema. Now I need to run the DROP statements on each. Obviously the dependencies will get in the way.

The brute force approach would be to run the DROPs in a loop until all have succeeded, but depending on the order (lucky or not), it could take a very long time.

Would you know a way to efficiently order the DROP statement so that the total time for the deletion is the shortest possible?

A perfect solution is not expected. A reasonably clever solution is good enough.

Thank you

Answer 1

You might want to see the references of each table (which you can do with syscat.references according to http://www.ibm.com/developerworks/data/library/techarticle/dm-0401melnyk/) and built a tree of the dependencies yourself (should be doable e.g. with temporary tables, if you are restricted to sql only). Then you may drop from the bottom of that tree.

So, basically, my answer to your question would be that in order to do it quick, just order the tables based on the references they have between themselves before deleting. Since there should not be any dependency cycles, you should always be able to pick one table which is not referenced. Drop it and repeat.

You might also wish to see this (similar?) question: DB2 cascade delete command? in case you want to delete the data first.

If I am wrong at some point, please correct. This answer is based on my experiences with other databases, therefore it might not be fully suitable for DB2. Although it should work ;)