DB2 procedure operations, security and debugging

Procedure versions and CURRENT ROUTINE VERSION

Native SQL procedures are versioned objects. The first CREATE PROCEDURE builds V1 (or the VERSION id you specify) and binds a package for that version. You add more versions without changing the SQL name callers use.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ALTER PROCEDURE HR.RAISE_SALARY
     ADD VERSION V2
     (IN P_EMPNO CHAR(6), IN P_RATE DECIMAL(6,2))
     LANGUAGE SQL
     MODIFIES SQL DATA
   BEGIN
     UPDATE EMP
        SET SALARY = SALARY * (1 + P_RATE),
            BONUS  = BONUS  * (1 + P_RATE)
      WHERE EMPNO = P_EMPNO;
   END;
 
ALTER PROCEDURE HR.RAISE_SALARY
     ACTIVATE VERSION V2;
 
SET CURRENT ROUTINE VERSION = 'V1';
CALL HR.RAISE_SALARY('000010', 0.02);
SET CURRENT ROUTINE VERSION = '';

CURRENT ROUTINE VERSION is a special register. When it matches a version id, CALL (and versioned SQL functions) use that version instead of the active one — useful for a canary test in one session. Empty or unmatched register → active version. Do not create extra versions of IBM-supplied SYSPROC routines; only IBM’s versions are supported.

BIND PACKAGE … DEPLOY copies a native SQL version to another subsystem (promotion) without re-typing CREATE. Replacing a version can change the access path even if the SQL text looks the same — treat it like a rebind.

START PROCEDURE and STOP PROCEDURE

These commands do not refresh load modules in a WLM stored-procedure address space. After you change COBOL/C, VARY WLM,APPLENV=envname,REFRESH. Use STOP only when you want to quiesce CALLs (change window, poison procedure). STOP AFTER n FAILURES / MAX ABEND COUNT can put a procedure in STOPABN on the member that abended; other data-sharing members may still be STARTED.

1
2
3
-DISPLAY PROCEDURE
-STOP PROCEDURE(HR.RAISE_SALARY) ACTION(REJECT)
-START PROCEDURE(HR.RAISE_SALARY)

Procedure packages

Native SQL: each version has a package bound with the CREATE/ALTER options (QUALIFIER, CURRENT DATA, DYNAMICRULES, EXPLAIN, isolation, and so on). REBIND PACKAGE can change some bind options later; REPLACE VERSION rebinds as part of ALTER.

External: you BIND the host program’s DBRM into a collection (COLLID on CREATE PROCEDURE). The SQL in COBOL is that package; CREATE PROCEDURE is only the registration. Keep collection and WLM STEPLIB in the same change ticket.

PACKAGE OWNER on native CREATE sets the owner who needs table privileges for static SQL. That is the usual security split: owner can UPDATE EMP; callers only EXECUTE the procedure.

Procedure security

1
2
3
4
GRANT EXECUTE ON PROCEDURE HR.RAISE_SALARY TO ROLE_HR_CLERK;
GRANT EXECUTE ON PROCEDURE HR.RAISE_SALARY TO PUBLIC;  -- usually too wide
 
REVOKE EXECUTE ON PROCEDURE HR.RAISE_SALARY FROM ROLE_HR_CLERK;

EXECUTE is the privilege to CALL. schema.* grants EXECUTE on all procedures in a schema, including ones not created yet — SYSADM required. WITH GRANT OPTION lets the grantee grant EXECUTE further.

External SECURITY clause: DB2 (definer/package auth as documented), USER (run with the caller’s z/OS identity — RACF), DEFINER. Combined with WLM and APF, USER is how some shops audit the end user inside COBOL. Native SQL uses SQL authorization and package owner, not that LANGUAGE COBOL SECURITY keyword.

Dynamic SQL inside a procedure follows DYNAMICRULES (RUN, BIND, DEFINEBIND, …) on the version. INVOKERUN means dynamic SQL authorization is the caller — a different security model than static SQL in the same procedure. Spell DYNAMICRULES on purpose.

Procedure debugging

Default follows the CURRENT DEBUG MODE special register. WLM ENVIRONMENT FOR DEBUG MODE names the application environment Db2 uses while debugging; if omitted, the install default stored-procedure AS is used. DISABLE ignores that WLM name.

ASUTIME LIMIT is a practical debug tool: a looping procedure is cancelled when it burns too many service units (parallel child tasks do not count toward the limit).

External procedures debug like any LE program in that WLM address space (CEEPIPI, Language Environment dump, IBM Debug). MSGFILE and RUN OPTIONS on CREATE PROCEDURE help.

Procedure error handling

Unhandled exception SQLSTATEs from statements in a native SQL body return to the CALLER — the CALL gets a negative SQLCODE. Handlers (CONTINUE, EXIT, UNDO) in the compound statement are how you swallow, log, or SIGNAL a business SQLSTATE. GET DIAGNOSTICS reads MESSAGE_TEXT. That is the next SQL PL pages; operationally: if production CALLs fail with the procedure’s SQLSTATE, look at handlers first, not only the caller’s COBOL.

External procedures can SET SQLSTATE before returning (PARAMETER STYLE SQL) so the caller sees a condition. Abends count toward STOP AFTER n FAILURES.

Procedure transaction behaviour

• COMMIT ON RETURN NO (default) — caller still owns the UOW
• COMMIT ON RETURN YES — commit caller + procedure on successful CALL; WITH HOLD for result sets
• AUTONOMOUS (native) — independent UOW; does not commit the caller; LOB/XML/array-LOB parameters forbidden; no global variable assignment
• COMMIT/ROLLBACK inside the procedure are allowed in many cases but interact with the caller’s UOW — autonomous is the clean split

Special registers: INHERIT (default) vs DEFAULT on CREATE PROCEDURE. CURRENT SQLID, PATH, and SCHEMA inside the procedure follow those rules — a common “wrong table” bug when QUALIFIER and PATH disagree with the caller.

Nested and recursive procedures

A procedure may CALL another. That is nesting. Each nested external routine is another WLM task (scheduling cost). Native nested CALL is another package execution in the engine. Recursion (a procedure CALL itself) must terminate; ASUTIME is your backstop. Do not assume storage persists between nested external CALLs.

WLM ENVIRONMENT (name,*) style definitions can keep a nested CALL in the caller’s address space instead of hopping. Wrong WLM routing is a frequent production incident after a nested design change.

Dynamic SQL in procedures

Native SQL PL can PREPARE/EXECUTE/EXECUTE IMMEDIATE according to the version’s DYNAMICRULES and the SQL PL statements allowed in your function level. Dynamic SQL is how a procedure builds a WHERE clause from parameters — and how it accidentally runs with the caller’s privileges (INVOKERUN) or the definer’s (BIND). Prefer static SQL when the statement is known; use parameter markers, not concatenated literals, when it must be dynamic.

Explain It Like I'm Five

Versions are editions of the same cookbook; ACTIVATE puts today’s edition on the counter; CURRENT ROUTINE VERSION is a sticky note “use the 2019 edition for this one customer.” START/STOP is the open/closed sign, not hiring a new chef (that is WLM REFRESH). EXECUTE is a ticket to order the dish without being allowed in the walk-in fridge. Debug mode is letting a teacher stand in the kitchen. Nested CALL is the sauce cook asking the grill cook for help on the same ticket. Autonomous is a side job paid from a different cash drawer.

Exercises

1. Add VERSION V2, ACTIVATE it, then use CURRENT ROUTINE VERSION to CALL V1 from one session only. What should you set the register to afterward?
2. You replaced a COBOL load module and issued -STOP/-START PROCEDURE. Calls still run old code. Which command did you skip?
3. Write GRANT EXECUTE for a role and explain why the role does not need UPDATE ON EMP.
4. A version was created DISABLE DEBUG MODE. How do you get a debuggable copy without dropping the procedure?
5. When would you choose AUTONOMOUS versus COMMIT ON RETURN YES for an audit-insert procedure?