Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    30

    Unanswered: Removing Schema's

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Mar 2007
    Posts
    30
    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

  5. #5
    Join Date
    Oct 2005
    Posts
    8
    look in the catalog to see if the function exist

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Mar 2007
    Posts
    30
    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. :-)

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by drogomoss
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •