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