Sample Header Ad - 728x90

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

0 votes
2 answers
6290 views
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
Asked by Nachiket Kate (101 rep)
Jun 5, 2014, 08:49 AM
Last activity: Aug 15, 2014, 05:09 PM