Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Exclamation Unanswered: DB2 Drop ALL TABLE

    Hi everybody,

    I need some help in DB2 (Version 7/8). I am looking for a command that will "Drop all Table" in a database.

    Can anyone help me?

    Thank you very much
    Sabrina

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No such command that I know about. But you can drop the database and recreate it.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Drop ALL TABLE

    Nope ...

    Such a thing has to be scripted, like ...

    db2 -x list tables | nawk '{print $2"."$1} | while read tname
    do
    db2 drop table tname
    done

    This is just an example ... It may be more complex as list tables, lists aliases and views also

    Sathyaram

    Originally posted by Sabrina
    Hi everybody,

    I need some help in DB2 (Version 7/8). I am looking for a command that will "Drop all Table" in a database.

    Can anyone help me?

    Thank you very much
    Sabrina
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Sep 2003
    Posts
    3

    Smile THANKS

    THANKS for your help!

    This is so unbelievable. Is there really no chance to "drop all table".

    Does anybody have more information or a good webside for this problem?

    Sabrina

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: THANKS

    I'm sorry ... I can't see this as a problem ...

    I would have required to drop all tables, 3-4 times only...

    Please can you let us know if you find any other recommendaton for achieving this

    Cheers
    Sathyaram

    Originally posted by Sabrina
    THANKS for your help!

    This is so unbelievable. Is there really no chance to "drop all table".

    Does anybody have more information or a good webside for this problem?

    Sabrina
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can also create SQL statements to achieve this:

    Select 'DROP TABLE ', tabname, ';' from syscat.tables

    Save the output of the above SQL statement and then run it with a commit at the end.

  7. #7
    Join Date
    Sep 2003
    Posts
    3

    :-)

    Hi Markus_A

    it works :-)
    Is it possible to create only one statement without save separate output.

    THANKS Sabrina

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, you cannot drop all tables with one statement (unless you drop the database). That alone has probably saved many careers.

  9. #9
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    If all the tables are in 1 tablespace, you could just drop and re-create the tablespace.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I know this was the case in ealier versions, but now you can no longer do that, atleast v7, fp 7 and after did not permit ..

    Cheers
    Sathyaram

    Originally posted by quigleyd
    If all the tables are in 1 tablespace, you could just drop and re-create the tablespace.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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