CODEWITHSUNDEEP
sqldb2sql-drop
Roman Iuvshin
Roman Iuvshin

Reputation: 1912

DB2 Drop table if exists equivalent

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

Upvotes: 16

Views: 43322

Answers (6)

lbrandao
lbrandao

Reputation: 4373

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;

Upvotes: 1

Pato Navarro
Pato Navarro

Reputation: 300

This is simpler and works for me:

DROP TABLE SCHEMA.TEST IF EXISTS;

Upvotes: 5

Îsh
Îsh

Reputation: 316

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

Upvotes: 5

Esperento57
Esperento57

Reputation: 17472

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  ;

Upvotes: 5

deltascience
deltascience

Reputation: 3380

Try this one: 

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

Upvotes: 8

user918176
user918176

Reputation: 1800

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.

Upvotes: 2

Related Questions