Syntax of if exists in IBM Db2

问题:

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 exists 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.

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

回答2:

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.