create a stored procedure if it doesnt exist using a ddl in db2

Question

My requirements are,

I want to create a ddl script which will check if a stored procedure exists on DB, if yes then drop it and recreate it.

What I tried is,

IF EXISTS (select procname into Migration_procname from sysibm.sysprocedures where procname like 'GIAM_PRIVILEGE_MIGRATION') THEN 
    DROP PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION;
ELSE
CREATE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL 
BEGIN 

......
......
update/select statements
......

END
@

which didn't work. so I even tried with creating another procedure which is doing same task still no success.

The error message was

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "IF EXISTS (select procname" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "". SQLSTATE=42601

Any pointers will be helpful.

NOTE: I am using DB2/LUW 9.5

Answer 1

IBM Added The OR REPLACE option in version 7.1

So your code should work like this :

CREATE OR REPLACE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL 
BEGIN 

......
......
update/select statements
......

END

Answer 2

In DB2 9.5 for LUW, create or replace does not work. It is added in 9.7. For 9.5+ compatibility, which my shop still has to maintain, I have created the following approach. Essentially, I create a utility stored procedure that will determine whether or not the other stored procedure exists or not, and then drops it conditionally so no error messages will appear. Then, I call that to drop the stored procedure I want to drop. Then, I clean up after myself since we can't reliably track where this has or has not been created.

NOTE: I'm currently working on refining this. It was done in a hurry and can obviously be simplified to use fewer variables. I'd also like to base it on SYSCAT.PROCEDURES, as in the query below:

SELECT * FROM SYSCAT.PROCEDURES WHERE PROCSCHEMA = CURRENT_SCHEMA AND PROCNAME = 'MYPROC'

So, here's the actual SP:

--#SET TERMINATOR !

CREATE PROCEDURE SP_Drop_Proc_If_Exists  (IN v_ProcName varchar(255))
Drop_Proc_If_Exists: BEGIN
    DECLARE V_EXISTENCE     CHAR(1) DEFAULT 'N';
    DECLARE V_FOUND_COUNT   INT DEFAULT 0;
    DECLARE V_DROP_STMT     VARCHAR(255);

    SELECT  COUNT(*)
    INTO    V_FOUND_COUNT
    FROM SYSIBM.SYSROUTINES
    WHERE ROUTINENAME = v_ProcName;

    IF (V_FOUND_COUNT > 0) THEN
        SET V_DROP_STMT = 'DROP PROCEDURE ' || v_ProcName;
        EXECUTE IMMEDIATE V_DROP_STMT;
    END IF;

END Drop_Proc_If_Exists
!


CALL SP_Drop_Proc_If_Exists  ('DROP_FK_CONSTRAINTS')
!

DROP PROCEDURE SP_Drop_Proc_If_Exists
!

--#SET TERMINATOR ;