DB2 SQL PL introduction and compound statements

SQL PL introduction

IBM’s name is SQL procedural language (SQL PL). SQL control statements make SQL a structured language for:

• Native SQL procedures (CREATE PROCEDURE … LANGUAGE SQL)
• SQL functions (CREATE FUNCTION with an SQL body)
• Advanced triggers (SQL PL in the trigger body)

The routine or trigger body must be a single SQL statement, which may be a control statement. In practice that statement is a compound BEGIN … END that holds many inner statements. Db2 transforms the body into a package (native procedure) when you CREATE it.

Control statements IBM lists: assignment, CALL, CASE, compound, FOR, GET DIAGNOSTICS, GOTO, IF, ITERATE, LEAVE, LOOP, REPEAT, RESIGNAL, RETURN, SIGNAL, WHILE. Later pages cover variables, IF/loops, and handlers in depth. Here we stay on the compound wrapper.

BEGIN / END and compound statements

A compound statement groups declarations and executable SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE HR.READ_EMP
     (IN  P_EMPNO CHAR(6),
      OUT P_NAME  VARCHAR(30))
     LANGUAGE SQL
     READS SQL DATA
   P1: BEGIN
     DECLARE V_FIRST VARCHAR(12);
     DECLARE V_LAST  VARCHAR(15);
     DECLARE V_SQLCODE INTEGER DEFAULT 0;
     DECLARE C1 CURSOR FOR
       SELECT FIRSTNME, LASTNAME FROM EMP WHERE EMPNO = P_EMPNO;
     DECLARE CONTINUE HANDLER FOR NOT FOUND
       SET P_NAME = NULL;
     OPEN C1;
     FETCH C1 INTO V_FIRST, V_LAST;
     CLOSE C1;
     SET P_NAME = V_FIRST || ' ' || V_LAST;
   END P1

P1 is a label. If you put a label after END, it must match the beginning label. The label qualifies variables (P1.V_FIRST) when names clash, and is the target of LEAVE P1 to jump out of the block.

SQL-variable-name is an SQL identifier. IBM: do not use a delimited identifier that contains lowercase letters or special characters for the unqualified variable name.

Declaration order

Put a SET or UPDATE before DECLARE CURSOR and CREATE PROCEDURE fails. Handlers must come before the OPEN/FETCH they protect. This order is the most common beginner syntax error after a missing END.

Nested compound statements

A nested compound statement is a BEGIN/END inside another BEGIN/END. Each inner block can have its own variables, cursors, and handlers. Name scoping follows the innermost declaration; labels disambiguate.

History matters on z/OS. External SQL procedures and older SQL PL rejected nested compounds (“NESTED COMPOUND STATEMENTS NOT ALLOWED”). Native SQL procedures on current Db2 releases document nested compounds in the SQL Reference (including a summary of name scoping). If your subsystem still rejects them, keep a single outer BEGIN and nest logic with IF/WHILE instead of a second BEGIN.

1
2
3
4
5
6
7
BEGIN
  DECLARE V_OUTER INTEGER DEFAULT 0;
  INNER: BEGIN
    DECLARE V_INNER INTEGER DEFAULT 1;
    SET V_OUTER = V_INNER;
  END INNER;
END

Inner handlers are the usual reason to nest: a CONTINUE handler that should apply only to one DELETE, not to the whole procedure. Do not nest just to indent.

ATOMIC blocks and NOT ATOMIC blocks

The compound header can specify how errors interact with the unit of work of that block:

• NOT ATOMIC — an error inside the compound does not automatically roll back the whole compound. Statements that already succeeded stay done unless you issue ROLLBACK or an UNDO handler. IBM’s z/OS syntax diagram calls this out explicitly.
• ATOMIC — the compound is one atomic unit. An unhandled error undoes the SQL changes of that block. COMMIT/ROLLBACK inside an ATOMIC block is restricted (you typically cannot COMMIT half of an ATOMIC procedure body).

1
2
3
4
BEGIN ATOMIC
  UPDATE ACCOUNTS SET BAL = BAL - 50 WHERE ACCT = 1;
  UPDATE ACCOUNTS SET BAL = BAL + 50 WHERE ACCT = 2;
END

That transfer either happens as a pair (from the compound’s point of view) or is undone if the second UPDATE raises an unhandled error. With NOT ATOMIC, the first UPDATE could remain if the second fails and you do not handle/rollback — a half-transfer, which is why money movement belongs in ATOMIC (or a single UOW with good handlers).

NOT ATOMIC is a better fit for a batch loop: one failed INSERT should not erase the two thousand that already worked; a CONTINUE handler plus a log table is the usual pattern (next pages).

ATOMIC is not the same as COMMIT ON RETURN YES. ATOMIC is about the compound’s error semantics. COMMIT ON RETURN is about committing the caller’s UOW when CALL comes back. AUTONOMOUS is a separate UOW for the whole procedure.

A minimal mental model

Think of CREATE PROCEDURE as the envelope and SQL PL as the letter:

• Envelope: LANGUAGE SQL, parameters, DYNAMIC RESULT SETS, bind options
• Letter: one compound statement — declarations in order, then procedural SQL

SQL inside the letter is ordinary Db2 SQL (SELECT, UPDATE, OPEN) plus control statements. QUALIFIER on CREATE PROCEDURE prefixes unqualified table names in that SQL. SQL variables never get a colon; table columns are still table columns. If a name is both, qualify it (EMP.SALARY vs V_SALARY).

1
2
3
4
5
6
7
BEGIN NOT ATOMIC
  DECLARE V_COUNT INTEGER DEFAULT 0;
  SELECT COUNT(*) INTO V_COUNT FROM EMP;
  IF V_COUNT = 0 THEN
    SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'No employees';
  END IF;
END

Explain It Like I'm Five

SQL PL is writing a cooking checklist in the same language as the ingredients list. BEGIN and END are the start and stop lines on the checklist. You must list your bowls (variables), then your spoons (cursors), then your “if it burns” notes (handlers), then the actual steps — never mix the order. Nesting is a mini-checklist inside a step. ATOMIC means “if any step on this card fails, dump the whole bowl.” NOT ATOMIC means “keep the cookies that already baked even if the last tray burns.”

Exercises

1. Rearrange a broken compound that DECLAREs a handler after OPEN. Write the legal order.
2. Add a label L1 to BEGIN/END and a LEAVE L1 after a successful FETCH. When would you use that instead of nested IF?
3. Write BEGIN ATOMIC with two UPDATEs that must both happen. Explain what NOT ATOMIC would risk.
4. Why is SET V = 1; DECLARE V INTEGER illegal even if it “reads top to bottom” in your head?
5. Your CREATE PROCEDURE fails with nested compound not allowed. How do you rewrite an inner BEGIN that only existed to hold one IF?