If syntax exists in IBM Db2

Question

The following query discards the table if the table exists, but it does not seem to work for IBM Db2.

Start atomic

if (exists (

SELECT 1 FROM SYSIBM.SYSTABLES WHERE NAME = 'EMAIL' AND TYPE = 'T' AND creator = 'schema1')) then drop table EMAIL; end if; the end

While the same thing if the exisits syntax works, if I have a DML instruction instead of a drop drop statement. Any help on this is appreciated.

Update 1: I read that you cannot run the DDL instruction at the beginning of the atomic block, so my first statement does not work, but the second goes fine.

Answer 1

The way I did it is as follows

Begin atomic if( exists( SELECT 1 FROM SYSIBM.SYSTABLES WHERE NAME='EMAIL' AND TYPE='T' AND creator = 'schema1' ) ) then customStoredproc('drop table EMAIL'); end if; End 

My customStoredProc has only one start of stmt immediately @dynsql;

Answer 2

You are correct that DB2 forbids DDL in an atomic SQL block. IBM has released a free add-in called db2perf_quiet_drop that works the way you want.