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.

Any pointers will be helpful.

NOTE: I am using DB2/LUW 9.5

score 0 · Answer 1 · answered Jun 05 '14 at 08:57

0

You can try CREATE OR REPLACE, this is the syntax in ORACLE and according to this website it should work in db2 as well: http://iprodeveloper.com/database/use-sql-create-or-replace-improve-db2-i-object-management

Good luck

answered Jun 05 '14 at 08:57

gomuli

16
2

That is present from db2 9.7 not from 9.5, Thanks anyways – Nachiket Kate Jun 05 '14 at 09:03
You try the solution here: it might help you to check if the procedure exists: http://stackoverflow.com/questions/355687/how-to-check-a-procedure-view-table-exists-or-not-before-dropping-it-in-db2-9-1 – gomuli Jun 05 '14 at 09:18
I have seen that, that is just deleting the procedure not creating it again. I am facing issues while recreating the procedure in same script. – Nachiket Kate Jun 05 '14 at 09:26

score 0 · Accepted Answer · answered Jun 05 '14 at 11:03

0

Stuff your drop statement in a procedure like:

create procedure drop_procedure (procschema varchar(128), 
                                 procname   varchar(128))
LANGUAGE SQL
BEGIN
    DECLARE tmpstmt VARCHAR(100);
    SET tmpstmt = 'drop procedure ' || procschema || '.' || procname;

    A: BEGIN
        -- Do nothing if drop procedure fails
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
            BEGIN
            END;

        EXECUTE IMMEDIATE tmpstmt;
    END;
END @

Then you can call that regardless whether there exists a procedure or not

CALL DROP_PROCEDURE('ITIMUSER', 'GIAM_PRIVILEGE_MIGRATION') @
CREATE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION() ...

answered Jun 05 '14 at 11:03

Lennart - Slava Ukraini

6,936
1
20
32

Thanks Lennart, one more query I have. Is there any ways in which I can execute DCL command (grant) using DDL script? – Nachiket Kate Jun 06 '14 at 06:59
Can you be more specific in what you are trying to do, afaik there is no problem granting as long as you have the auth. – Lennart - Slava Ukraini Jun 06 '14 at 07:24
Actually I am executing DDL script with ADMIN user so by default there will not be execute permission on created procedures to other users like ITIMUSER. so I wanted to grant them permissions through the same DDL file. I tried running that, but did not get success. got below error – Nachiket Kate Jun 06 '14 at 08:08
GRANT EXECUTE ON PROCEDURE ITIMUSER.DROP_PROCEDURE TO ITIMUSER CALL ITIMUSER.DROP_PROCEDURE() 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 "GRANT EXECUTE ON PROCEDURE ITIMUSER.DROP_PR" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "". SQLSTATE=42601 – Nachiket Kate Jun 06 '14 at 08:09
I think it should be: `GRANT EXECUTE ON PROCEDURE ITIMUSER.DROP_PROCEDURE TO USER ITIMUSER @` and then `CALL ... @` Note the statement terminator between GRANT and CALL – Lennart - Slava Ukraini Jun 06 '14 at 08:14

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

2 Answers2