Hello All,

Good day.

I'd like to have a few ideas on how to effectively drop an unused schema. I've been using Toad for DB2 and dropping a schema from there is not working. I've already removed all the tables and functions and still it cannot be dropped.

Am I missing something here? Is there a way to drop a schema with the least commands?

Thanks a lot,

Drogo

There must be some objects somewhere that are tied to the schema, which prevents it from being dropped. If such objects do exist, you should get a SQLSTATE 42893 error message using the Drop Schema command (I don't know whether TOAD supports the drop schema).

i think there are some functionalities on v9 to manipulate with schema.

check following article (still requires v8.2.2)
http://www.ibm.com/developerworks/db...lau/index.html

I'll check them... I've dropped everything under the schema but still returns an error:

ERROR [42893] [IBM][DB2/6000] SQL0478N DROP or REVOKE on object type "SCHEMA" cannot be processed because there is an object "MDMETEST.SQL060207181935000", of type "FUNCTION", which depends on it. SQLSTATE=42893

I can't find this object. I don't see any functions in this schema anymore. How do I drop schemas using the db2 command line?

Thanks a lot guys,

Drogo

look in the catalog to see if the function exist

What do you mean with "I don't see any functions in this schema anymore"? How do you check that?

You should query the SYSCAT.ROUTINES catalog view to verify the existence of such a function exists. Also, the SQL060... in the specific function name - not the regular function name.

Well its in the interface of Toad for DB2. I selected the Schema... checked everything under Funtion (some sort of directory tree)... and then right clicked everything and dropped them. If I check again no function are under that directory anymore. :-)

Try looking at SYSCAT.ROUTINES. If DB2 says it cannot drop a schema and names the dependent object, try dropping the dependent object.