DB2 SQL PL handlers and conditions

Exception handling in SQL PL

Every SQL statement produces a completion: success (SQLSTATE class 00), warning (01), not found (02), or exception (anything else). In a COBOL program you might WHENEVER NOT FOUND GO TO … or check SQLCODE after each statement. In SQL PL you declare handlers in the compound statement. IBM compares them directly to WHENEVER.

General form:

1
2
DECLARE handler-type HANDLER FOR condition
  sql-procedure-statement;

When a statement raises a matching condition, Db2 runs that sql-procedure-statement (often a SET, or a nested BEGIN that logs and RESIGNALs). When the handler body finishes successfully, the handler type decides where execution continues. If no handler matches an exception, the compound (and usually the whole CALL) fails with that SQLSTATE.

Declaration order still applies: variables and conditions first, cursors second, handlers third, executable SQL last. A handler can only see names declared above it in that compound.

Handler types

CONTINUE handlers

CONTINUE is the cursor-loop workhorse. NOT FOUND on FETCH should not kill the procedure; it should set a flag and let WHILE see the flag.

1
2
3
4
DECLARE V_AT_END INT DEFAULT 0;
DECLARE NOT_FOUND CONDITION FOR '02000';
DECLARE CONTINUE HANDLER FOR NOT_FOUND
  SET V_AT_END = 1;

After FETCH hits end-of-data, the handler SETs V_AT_END, then execution continues at the statement after FETCH. Your WHILE V_AT_END = 0 test then fails and the loop ends. IBM also documents SQLEXCEPTION CONTINUE handlers that swallow expected errors (DROP TABLE before CREATE in a demo procedure) — use that pattern only when the error is truly ignorable.

Special resume rule: if the condition fired while evaluating a search condition of IF, CASE, WHILE, or REPEAT (not a statement nested inside THEN), CONTINUE resumes after END IF / END CASE / END WHILE / END REPEAT. That is why testing SQLSTATE in the WHILE condition itself is brittle; set a variable in the handler and test the variable.

EXIT handlers

EXIT runs the handler body, then continues at the end of the compound that declared the handler. If that compound is the procedure body, the procedure returns. Use EXIT for “this CALL cannot continue, but set a message for the caller first.”

1
2
3
DECLARE NO_TABLE CONDITION FOR '42704';
DECLARE EXIT HANDLER FOR NO_TABLE
  SET OUT_BUFFER = 'Table does not exist';

SQLSTATE 42704 is “name is an undefined name” (missing table/object). The handler copies a readable string to an OUT parameter, then the BEGIN ends. The caller still needs a convention: maybe also SET P_RC = 8 so they do not treat a blank buffer as success.

UNDO handlers

UNDO is EXIT plus a rollback of SQL changes made in that compound statement. The compound must be ATOMIC. Before the handler body runs, Db2 undoes those changes (the block behaves as one unit). After the handler body, control is at the end of the compound, like EXIT.

1
2
3
4
5
6
P1: BEGIN ATOMIC
  DECLARE UNDO HANDLER FOR SQLEXCEPTION
    SET P_MSG = 'Unit rolled back';
  UPDATE ACCOUNTS SET BALANCE = BALANCE - P_AMT WHERE ID = P_FROM;
  UPDATE ACCOUNTS SET BALANCE = BALANCE + P_AMT WHERE ID = P_TO;
END P1;

If the second UPDATE fails, UNDO restores the first UPDATE’s change inside this ATOMIC block (subject to IBM’s ATOMIC/savepoint rules and what COMMIT you are allowed to issue in that context). NOT ATOMIC compounds cannot declare UNDO — there is no single unit to undo. Introductory z/OS handler examples stress CONTINUE and EXIT because those cover 90% of procedures; UNDO belongs with ATOMIC money-move blocks.

UNDO does not replace a well-designed unit of work with COMMIT/ROLLBACK at the application boundary. Nested COMMIT inside ATOMIC is restricted. Know whether your shop wants the procedure to COMMIT ON RETURN or leave the UOW to the caller.

Condition declarations

You can handle a class, a raw SQLSTATE, or a name you define:

1
2
3
DECLARE DUPKEY CONDITION FOR '23505';
DECLARE EXIT HANDLER FOR DUPKEY
  SET P_RC = 8;

• SQLSTATE string — five characters in quotes, not '00000'.
• condition-name — DECLARE name CONDITION FOR 'xxxxx'. The name must be unique in the procedure body and is visible in that compound. Do not use delimited lowercase names.
• SQLEXCEPTION / SQLWARNING / NOT FOUND — general classes. A more specific handler (exact SQLSTATE or condition name) beats a general one when both could match.

You may list several conditions on one handler: DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLWARNING SET V_NOTE = 1. That single SET then covers both classes.

SQLSTATE and SQLCODE

In the outermost compound you may declare:

1
2
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;

Db2 sets them after each SQL statement: SQLSTATE is the five-character class+subclass (portable); SQLCODE is the integer Db2 code (negative for errors, +100 for not found, other positives for warnings). Handlers are keyed off SQLSTATE (and classes), not off SQLCODE numbers — you cannot DECLARE CONDITION FOR SQLCODE -803, you DECLARE for '23505'.

Assignment to SQLSTATE/SQLCODE is ignored by handlers; the next statement overwrites them. If a handler needs the original code, GET DIAGNOSTICS or copy into V_SQLCODE as the first statement of the handler. After a handler completes successfully, Db2 typically resets the return codes to success (00000 / 0) so the following statement does not look like it failed.

SIGNAL and RESIGNAL

SIGNAL

SIGNAL raises a condition on purpose: bad input, a business rule, “this department is frozen.” You may SIGNAL a condition-name or SQLSTATE VALUE 'xxxxx', optionally SET MESSAGE_TEXT = string-expression (up to the documented diagnostic length; IBM describes expressions of CHAR/VARCHAR used as message text).

1
2
3
4
IF P_DEPT IS NULL THEN
  SIGNAL SQLSTATE VALUE '75001'
    SET MESSAGE_TEXT = 'Department is required';
END IF;

Do not SIGNAL class 00 (success). Application-defined SQLSTATEs commonly use a class such as 7–9 so they do not collide with IBM-defined states. If a handler matches, it runs. If not, the routine fails and CALL sees the SQLSTATE. SIGNAL typically surfaces as SQLCODE +438 (warning-class states) or -438 (error-class states) in the SQLCA, with your text in the message field.

RESIGNAL

RESIGNAL is for handlers. Bare RESIGNAL rethrows the condition being handled so an outer handler or the caller still fails after you logged it. RESIGNAL SQLSTATE VALUE '…' SET MESSAGE_TEXT = … maps the low-level error to a friendlier application state.

1
2
3
4
5
6
7
8
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS CONDITION 1
    V_MSG = MESSAGE_TEXT;
  INSERT INTO HR.ERRLOG(TS, TXT)
    VALUES (CURRENT TIMESTAMP, V_MSG);
  RESIGNAL;
END;

Without RESIGNAL, an EXIT handler that only INSERTs into a log would swallow the error and look like success at the end of the compound. Logging plus RESIGNAL is the usual “audit then fail” pattern.

GET DIAGNOSTICS

GET DIAGNOSTICS copies items from the diagnostics area without changing SQLSTATE as a side effect of the GET itself. Common CONDITION 1 items:

• MESSAGE_TEXT — human-readable message (including SIGNAL text)
• RETURNED_SQLSTATE — the five-character state
• DB2_RETURNED_SQLCODE — the integer SQLCODE

STATEMENT-level items include ROW_COUNT after INSERT/UPDATE/DELETE. GET DIAGNOSTICS must run before another SQL statement overwrites the diagnostics area — make it the first thing in the handler.

1
2
3
4
GET DIAGNOSTICS CONDITION 1
  V_STATE = RETURNED_SQLSTATE,
  V_CODE  = DB2_RETURNED_SQLCODE,
  V_MSG   = MESSAGE_TEXT;

Putting it together

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE PROCEDURE HR.SUM_DEPT
     (IN  P_DEPT CHAR(3),
      OUT P_TOTAL DECIMAL(11,2),
      OUT P_MSG  VARCHAR(100))
     LANGUAGE SQL
     READS SQL DATA
   BEGIN
     DECLARE V_SAL DECIMAL(9,2);
     DECLARE V_AT_END INT DEFAULT 0;
     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
     DECLARE C1 CURSOR FOR
       SELECT SALARY FROM DSN8C10.EMP WHERE WORKDEPT = P_DEPT;
     DECLARE CONTINUE HANDLER FOR NOT FOUND
       SET V_AT_END = 1;
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN
       GET DIAGNOSTICS CONDITION 1 P_MSG = MESSAGE_TEXT;
       SET P_TOTAL = NULL;
     END;
 
     SET P_TOTAL = 0;
     SET P_MSG = 'OK';
     OPEN C1;
     FETCH C1 INTO V_SAL;
     WHILE V_AT_END = 0 DO
       SET P_TOTAL = P_TOTAL + V_SAL;
       FETCH C1 INTO V_SAL;
     END WHILE;
     CLOSE C1;
   END

NOT FOUND is CONTINUE so the loop can end cleanly. Any real exception EXITs after copying MESSAGE_TEXT to P_MSG. That split — expected end-of-data versus unexpected failure — is the whole point of multiple handlers.

Explain It Like I'm Five

A handler is a grown-up standing next to you while you do homework. If you reach the end of the worksheet (NOT FOUND), CONTINUE says “that’s fine, put your pencil down and go to the next instruction.” EXIT says “stop this whole desk’s work; we’re done here.” UNDO says “erase everything you wrote on this special ATOMIC page, then stop.” SIGNAL is you raising your hand to report a problem on purpose. RESIGNAL is the grown-up, after writing the problem in a logbook, raising their hand so the teacher (the caller) still knows something went wrong. GET DIAGNOSTICS is reading the note the teacher already wrote about what happened.

Exercises

1. Declare a named condition for SQLSTATE 23505 and an EXIT handler that sets P_RC = 8. Why is 23505 a better match than a generic SQLEXCEPTION handler for duplicate keys?
2. Write a CONTINUE handler for NOT FOUND used with FETCH. Show where V_AT_END is tested.
3. Explain why UNDO cannot be declared in BEGIN NOT ATOMIC.
4. Write SIGNAL for a missing IN parameter, then a handler that GET DIAGNOSTICS the MESSAGE_TEXT into an OUT parameter.
5. Why should RESIGNAL appear after you INSERT into an error log inside an SQLEXCEPTION EXIT handler?