CODEWITHSUNDEEP
sqldb2drop-table
bobbyrne01
bobbyrne01

Reputation: 6745

Drop table only if it exists, or ignore drop error

I have a table MYLOG and would like to try drop it before creating it using the SQL script below. If the table does not exist yet, the error below is throw. How could I bypass this error if the table does not exist?

The schema gets set in an earlier script, which is not available in the SQL script:

set current schema MYSCHEMA

SQL script:

DROP TABLE MYLOG;
CREATE TABLE MYLOG (
  TIME_STARTED TIMESTAMP NOT NULL,
  USER_EMAIL VARCHAR(254) NOT NULL,
  CONSTRAINT PK_TIME_STARTED_USER_EMAIL    PRIMARY KEY (TIME_STARTED, USER_EMAIL))  ORGANIZE BY ROW;
COMMIT;

Error:

DROP TABLE MYLOG
SQLError: rc = 0 (SQL_SUCCESS) 
SQLGetDiagRec: SQLState     : S0002
          fNativeError : -204
          szErrorMsg   : [IBM][CLI Driver][DB2/6000] SQL0204N  "MYSCHEMA.MYLOG" is an undefined name.  SQLSTATE=42704

Upvotes: 0

Views: 1895

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

by using this query 

select tabname from syscat.tables where 
tabschema='myschema' and tabname='MYLOG'

check that table in your schema

if exist then 

drop table myschema.MYLOG

then create

Upvotes: 0

mao
mao

Reputation: 12267

This is a FAQ

There's more than one way to do it.

You can use compound-SQL in your script with a continue-handler for the SQLSTATE corresponding to the error you get if the table is not found, but this requires that you also use an alternative statement delimiter like shown below

--#SET TERMINATOR @

set current schema myschema@

BEGIN
 DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
   BEGIN end;
 EXECUTE IMMEDIATE 'DROP TABLE MYLOG';
END @

CREATE TABLE MYLOG(... )@

You can also change the abort-on-first-error logic (if you use +s when running your script via the command line). You can udate the Db2 CLP options on the fly inside your script via update command options using s off (to continue on error) or update command options using s on to abort on error.

Upvotes: 1

Related Questions