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
Best Answer
IBM Added The OR REPLACE option in version 7.1
So your code should work like this :