Is there an equivalent in DB2 for MySQL's (or PostgreSQL's):
DROP TABLE IF EXISTS sometable;
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 only takes a minute to sign up.
Sign up to join this communityYou'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.
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
You can use an annonymous block
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
BEGIN END;
EXECUTE IMMEDIATE 'DROP TABLE sometable';
END @