Saturday, February 19, 2011

How to check a procedure/view/table exists or not before dropping it in db2 9.1?

How do we write below pseudo code in db2,

If (Proc exists)
  Drop Proc
  Create Proc
Else
 Create Proc

One solution I found, after googling is to ignore the return codes. Do we have a more elegant way to do this?

Thanks


Update: With the help of the answer below we wrote a proc as below to drop the procedures

  CREATE PROCEDURE SVCASNDB.DROPSP(IN P_SPECIFICNAME VARCHAR(128))
        SPECIFIC DROPSP

        P1: BEGIN


        -- Drop the SP if it already exists
        if exists (SELECT SPECIFICNAME FROM SYSIBM.SYSROUTINES WHERE SPECIFICNAME = trim(upper(p_SpecificName))) then
         begin
          DECLARE v_StmtString VARCHAR (1024);
          SET v_StmtString = 'DROP SPECIFIC PROCEDURE SCHEMA.' || p_SpecificName;
          PREPARE stmt1 FROM v_StmtString ;
          EXECUTE stmt1;
         end;
        end if;

    END P1
From stackoverflow
  • this query: SELECT DISTINCT ROUTINENAME, RESULT_SETS, REMARKS FROM SYSIBM.SYSROUTINES where ROUTINESCHEMA='' AND FUNCTION_TYPE NOT IN ('S', 'T') (where you specify your schema name at the placeholder) gives you all procs in a schema. So the Proc exists part is simply an EXISTS query on that view with the proper proc name.

0 comments:

Post a Comment