DB2 Drop table if exists equivalent

Question

I need to drop a DB2 table if it exists, or drop and ignore errors.

What context are you running this in? If you're executing a script, the necessary abilities _may_ not be available. If you're doing this from a higher level/application (RPG, Java, C#, etc), this is usually trivial. — Clockwork-Muse, Nov 25 '11 at 16:03
See if this helps: http://bytes.com/topic/db2/answers/509144-drop-table-view-only-if-exists — Levin Magruder, Nov 25 '11 at 17:07

score 8 · Answer 1 · answered Apr 03 '17 at 10:02

8

Try this one:

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

answered Apr 03 '17 at 10:02

deltascience

3,321
5
42
71

You should consider the schema name also, otherwise, the table name might exist in another schema, but not in yours, and exception will be thrown. – Wilhelm Sorban Aug 23 '17 at 08:51
And you may also want to check that it's a table (and not a view). – Wilhelm Sorban Aug 23 '17 at 08:57
5

DB2 does not support this kind of programmatic flow control – user119588 Feb 26 '18 at 13:32
Like user1195888 said, this answer is misleading because it doesn't work on DB2 as was asked. – Sebastian Nov 22 '22 at 12:42

score 5 · Answer 2 · answered Nov 06 '19 at 20:30

5

The below worked for me in DB2 which queries the SYSCAT.TABLES view to check if the table exists. If yes, it prepares and executes the DROP TABLE statement.

BEGIN    
   IF EXISTS (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SCHEMA_NAME' AND TABNAME = 'TABLE_NAME') THEN 
      PREPARE stmt FROM 'DROP TABLE SCHEMA_NAME.TABLE_NAME';
      EXECUTE stmt;
   END IF;                                                        
END

answered Nov 06 '19 at 20:30

Îsh

196
5
13

Note that this won't work in a Spring Boot initialization script (schema.sql), because Spring Boot will truncate this statement by the first semicolon. See [here](https://stackoverflow.com/q/32903696/8707976) for more info. – Sebastian Nov 23 '22 at 08:59

score 4 · Answer 3 · answered Apr 24 '17 at 09:27

search on systable : if you are on as400 (power i, system i) the system table name is QSYS2.SYSTABLES else try sysibm.systables or syscat.tables (This depends on the operating system)

BEGIN    
IF EXISTS (SELECT NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'YOURLIBINUPPER' AND TABLE_NAME = 'YOUTABLENAMEINUPPER') THEN           
  DROP TABLE YOURLIBINUPPER.YOUTABLENAMEINUPPER;                             
END IF;                                                        
END  ;

score 3 · Answer 4 · answered Jul 01 '20 at 08:21

3

This is simpler and works for me:

DROP TABLE SCHEMA.TEST IF EXISTS;

answered Jul 01 '20 at 08:21

Pato Navarro

262
2
11

2

That syntax is only available for Db2-LUW versions 11.5 and higher. So if you try to use this syntax on older versions (i.e. on almost all currently installed Db2-LUW installations) then it will fail. – mao Jul 01 '20 at 08:24

score 2 · Accepted Answer · answered Nov 26 '11 at 12:14

2

First query if the table exists, like

select tabname from syscat.tables where tabschema='myschema' and tabname='mytable'

and if it returns something issue your

drop table myschema.mytable

Other possibility is to just issue the drop command and catch the Exception that will be raised if the table does not exist. Just put that code inside try {...} catch (Exception e) { // Ignore } block for that approach.

answered Nov 26 '11 at 12:14

user918176

1,770
13
34

The try/catch isn't good becasue the exception might be not due to table's absence, but due to the fact some constraints are referencing this table and it cannot be dropped until those constraints are dropped. – mvmn Sep 27 '13 at 10:10
@mvmn: DB2 (at least the versions I know) always does a "cascading" drop. So any FK will silently be dropped anyway – Dec 13 '13 at 13:00

score 1 · Answer 6 · answered Apr 16 '21 at 14:46

To complement the other answers here, if you want to be ANSI compatible you could also use the queries bellow. It should work for IBM i and LUW:

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'MY_SCHEMA' AND TABLE_NAME = 'MY_TABLE';

then if any result is returned:

DROP TABLE MY_SCHEMA.MY_TABLE;

DB2 Drop table if exists equivalent

6 Answers6

Linked