7

Is there an equivalent in DB2 for MySQL's (or PostgreSQL's):

DROP TABLE IF EXISTS sometable;

ivotron
  • 533
  • 3
  • 6
  • 11

3 Answers3

4

No.

You'll need an IF clause to check for the existence of the table and then a separate statement to actually drop it if it does exist.

mustaccio
  • 25,896
  • 22
  • 57
  • 72
Nick Chammas
  • 14,670
  • 17
  • 75
  • 121
  • 1
    @ivotron : Nick's right on this one. An example of Nick's answer is in StackOverflow : http://stackoverflow.com/a/3976691/491757. BTW +1 for Nick !!! – RolandoMySQLDBA Dec 21 '11 at 18:01
2

Alternative solution is given here:

Create the stored function db2perf_quiet_drop():

CREATE PROCEDURE db2perf_quiet_drop( IN statement VARCHAR(1000) )
LANGUAGE SQL
BEGIN
   DECLARE SQLSTATE CHAR(5);
   DECLARE NotThere    CONDITION FOR SQLSTATE '42704';
   DECLARE NotThereSig CONDITION FOR SQLSTATE '42883';

   DECLARE EXIT HANDLER FOR NotThere, NotThereSig
      SET SQLSTATE = '     ';

   SET statement = 'DROP ' || statement;
   EXECUTE IMMEDIATE statement;
END

and use it as Begin atomic call db2perf_quiet_drop('table my_table'); End

Community
  • 1
dma_k
  • 193
  • 1
  • 2
  • 9
2

You can use an annonymous block

BEGIN
 DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
   BEGIN END;
 EXECUTE IMMEDIATE 'DROP TABLE sometable';
END @
AngocA
  • 575
  • 5
  • 17