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

2 thoughts on “DB2 – Drop Table “IF EXISTS”

  1. I tried to put that sentence in a script file to drop several tables but ibm data studio sends an error “DB2 for linux, unix, and windows:”begin declare continue handler for sqlstate ‘42704’” appears to be misplaced. how do I put these sentence in a script to drop several objects and continues even it fails when does not find an object? is that possible? Thank you

    • Hey Martin,

      I haven’t tried that before. Not sure if the below helps, but I am assuming you can create a PROCEDURE or just a FOR loop, then iterate over the list of tables you would want to DROP and pass the table name to this script “EXECUTE IMMEDIATE ‘DROP TABLE {SCHEMA_NAME.TABLE_NAME}’” inside of that PROCEDURE.

      Get the list of tables for a given Schema:

      select tabname from syscat.tables where `tabschema = ‘DBO’

      More reading on LOOPS:
      http://www.informit.com/articles/article.aspx?p=31963&seqNum=4

      Thanks,
      Rakesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s