Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

DROP TABLE IF EXISTS sometable;

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
1  
@ivotron : Nick's right on this one. An example of Nick's answer is in StackOverflow : stackoverflow.com/a/3976691/491757. BTW +1 for Nick !!! –  RolandoMySQLDBA Dec 21 '11 at 18:01
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.