How can I check a procedure / view / table exists or not before dropping it in db2 9.1?
As we write below pseudocode in db2,
If (Proc exists)
Drop Proc
Create Proc
Else
Create Proc
One solution I found after googling is to ignore return codes. Do we have a more elegant way to do this?
thank
Update: With the below answer, we wrote a proc as shown below to remove procedures
CREATE PROCEDURE SVCASNDB.DROPSP(IN P_SPECIFICNAME VARCHAR(128))
SPECIFIC DROPSP
P1: BEGIN
-- Drop the SP if it already exists
if exists (SELECT SPECIFICNAME FROM SYSIBM.SYSROUTINES WHERE SPECIFICNAME = trim(upper(p_SpecificName))) then
begin
DECLARE v_StmtString VARCHAR (1024);
SET v_StmtString = 'DROP SPECIFIC PROCEDURE SCHEMA.' || p_SpecificName;
PREPARE stmt1 FROM v_StmtString ;
EXECUTE stmt1;
end;
end if;
END P1
+1
source to share
1 answer
this query:
SELECT DISTINCT ROUTINENAME, RESULT_SETS, REMARKS
FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA='<schema>' AND FUNCTION_TYPE NOT IN ('S', 'T')
(where you give your schema name in placeholder) gives you all the procs in the schema. So the Proc part is just an EXISTS request on that view with the appropriately named proc.
+2
source to share