DB2 – Drop Table “IF EXISTS”

Hello Folks,

As most of you may probably be aware that DB2 does NOT have a “If Exists” check safe keyword to safely execute the DML statements.

Here is one another way apart from the many ways one would find over the net.

BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘{SQL_STATE}’
BEGIN END;
EXECUTE IMMEDIATE ‘DROP TABLE {SCHEMA_NAME.TABLE_NAME}’
END
GO

This query is fail safe, meaning that it captures the SQL State error if the statement failed to execute and continuous with other below statements, if any.

Here the SQL_STATE can be found at http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/db2z_sqlstatevalues.html

and SCHEMA_NAME.TABLE_NAME is self explanatory.

Happy Learning !!

Advertisements