Using HANDLER in SQL procedure

[SaulS]

Having quite a lot experience with DB2/COBOL stored procedures newer was on a project with DB2/SQL procedures.
And now I started to look like “green horn” again.
In DB2 V8 environment made changes to the EXISTED SQL procedure by introducing a HANDLER to avoid some abends.
Procedure defined as
CREATE PROCEDURE XYZABC (
IN some stuff,
OUT oRetCode INTEGER )

LANGUAGE SQL
and etc.

DECLARE dupe CONDITION FOR SQLSTATE VALUE '23505';
(this is nothing but -803 on INSERT)
And the last declaration
DECLARE CONTINUE HANDLER FOR dupe SET oRetCode = -803;

Surprisingly got a persistent compile error. Looks like it does not like the word HANDLER (or CONTINUE).

DSNH016I E DSNHPARS LINE 82 COL 12 "GLOBAL" REQUIRED
DSNH199I E DSNHPARS LINE 82 COL 12 INVALID KEYWORD "CONTINUE"; VALID SYMBOLS ARE: GLOBAL

All my efforts to find the reason going through manuals did not give an answer.
Any suggestions?
Regards.

[dbzthedinosaur]

you have a continue handler, yet you have not declared a handler
look for 13.3.1 Using handlers in an SQL procedure
that link is the html version of the manual.


here is a link to the download page for the redbook DB2 for z/OS Stored Procedures: Through the CALL and Beyond.pdf.

[SaulS]

Please read my private message to you

[dbzthedinosaur]

got a feeling you have to phrase it as:

DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '23505' SET oRetCode = -803;

[dbzthedinosaur]

vsn8:
Types of handlers: The handler type determines what happens after the completion of the SQL-procedure-statement. You can declare the handler type to be either CONTINUE or EXIT:

CONTINUE
Specifies that after SQL-procedure-statement completes, execution continues with the statement after the statement that caused the error.

EXIT
Specifies that after SQL-procedure-statement completes, execution continues at the end of the compound statement that contains the handler.

[dbzthedinosaur]

sorry about my first post. was off base and did not explain myself properly.

vsn 9 is the same as vsn 8

the only handler-types are EXIT and CONTINUE.
you can have compound statements by using the BEGIN...END syntax.