DB2 10.5 stored Procedure question

Question

I'm having an issue with a stored procedure in DB2 LUW 10.5 using Data Studio 4.1.3 when trying to call more then one statement at a time. I've narrowed the problem down to the way semi-colon's (;) are being treated. For easier trouble-shooting I created a really simple example of what I am trying to do.

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";
    insert into "DB2ADMIN"."TestTable"
    values('a','a','a');
    insert into "DB2ADMIN"."TestTable"
    values('b','b','b');
    select * from "DB2ADMIN"."TestTable";

If I run the code above it completes fine but when I look at the sp I only see:

--<ScriptOptions statementTerminator=";"/>

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";

It only reads to the first semi-colon and then stops. If I remove all semi-colons but the last one the procedure will not get created. My code in real life is much longer but is behaving the same. Any help or ideas would be greatly appreciated.

Answer 1

Inside a stored procedure body you have to use a semi-colon to delimit statements. But semi-colons are also the default for delimiting statements in most clients so you end up needing to change your client's delimiter to something else (I use @). You end up with something like:

create procedure cleanup()
begin
    delete from mytable;
    delete from myothertable;
end @