Syntax of if exists in IBM Db2

Question

The follow query drops a table if the table exists but it doesnt seem to work for IBM Db2.

Begin atomic

if( exists(

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

Whereas the same if exisits syntax works if i have a DML statement instead of table drop statement. Any help on this is appreciated

Update 1: I read that you cannot run DDL statement within begin atomic block hence my first statement fails but the second goes fine.

Answer 1

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

Answer 2

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 just has one stmt execute immediate @dynsql;