--#SET TERMINATOR @
CREATE PROCEDURE drop_rc0 ( IN obj_typ_and_obj_id VARCHAR(1000) )
--
-- procedure facilitates DROP TABLE, DROP PROCEDURE, etc.
-- by suppressing negative return code if object does not exist.
-- 
-- usage:         db2 "call drop_rc0 ('TABLE my-sch.my-table')"
-- or:            db2 "call drop_rc0 ('ALIAS schema.alias-name FOR SEQUENCE')"
-- or:    Begin atomic call drop_rc0 ('TABLE my-sch.my-table'); End
-- or, in a command file, invoked by: db2 -stvf ...:
--                     call drop_rc0 ('PROCEDURE proc-name') ;
--
-- This CREATE PROCEDURE produces followig warning (but works ok):
-- SQL0385W  Assignment to an SQLSTATE or SQLCODE variable in an SQL routine may
-- be over-written and does not activate any handler.  SQLSTATE=01643
--
LANGUAGE SQL
BEGIN
  DECLARE SQLSTATE CHAR(5) ;
  DECLARE NotThere    CONDITION FOR SQLSTATE '42704' ;
  -- SQLSTATE 42704: An undefined object or constraint name was detected.
  DECLARE NotThereSig CONDITION FOR SQLSTATE '42883' ;
  -- SQLSTATE 42883: No routine was found with a matching signature.
  DECLARE EXIT HANDLER FOR NotThere, NotThereSig
     SET SQLSTATE = '     ' ;
  SET drop_stmt = 'DROP ' || obj_typ_and_obj_id ;
  EXECUTE IMMEDIATE drop_stmt ;
END@
--#SET TERMINATOR ;