Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    AUS
    Posts
    26

    Drop Table If Exists

    Is there any db2 command equivalent to mysql's DROP TABLE IF EXISTS

    Cheers

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, that syntax is not supported. However, you can just drop the table and intercept the return code that DB2 returns if the table does not exist.
    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
    Aug 2003
    Location
    AUS
    Posts
    26
    I found a solution but I am not sure it's safe to do or not.

    IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'tab_name') THEN
    DROP TABLE tab_name;
    END IF;

    cheers
    Melb

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Keep in mind that you also need the TABSCHEMA column to uniquely qualify a row in the SYSCAT.TABLES. Also, not all entries in SYSCAT.TABLES are base tables (some are views, nicknames, etc), so use the TYPE column if that matters to you.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    use case .. when statement , to drop table , if exists.


    sample as below



    select case when exist ( select 'X' from sysibm.systables where name 'PSLOCK' ) then ' drop table '||name else ' ' end from sysibm.systables where name = 'PSLOCK' and type = 'T'


    Thank You


    Lekharaju Ennam

Posting Permissions

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