DB2 CREATE PROCEDURE, ALTER, DROP and CALL

CREATE PROCEDURE

There are separate CREATE PROCEDURE forms in the SQL Reference: SQL native, SQL external (deprecated), and external (host language). This page emphasises native SQL, then shows an external skeleton.

Authorization typically includes SYSADM, system DBADM, or CREATEIN on the schema. The schema qualifier must be allowed for the owner. WLM ENVIRONMENT FOR DEBUG MODE also needs RACF authority on that application environment.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE HR.RAISE_SALARY
     (IN  P_EMPNO CHAR(6),
      IN  P_RATE  DECIMAL(6,2))
     LANGUAGE SQL
     MODIFIES SQL DATA
     DYNAMIC RESULT SETS 0
     COMMIT ON RETURN NO
     QUALIFIER HR
     ASUTIME LIMIT 10000
   BEGIN
     UPDATE EMP
        SET SALARY = SALARY * (1 + P_RATE)
      WHERE EMPNO = P_EMPNO;
     IF SQLCODE <> 0 THEN
       SIGNAL SQLSTATE '75001'
         SET MESSAGE_TEXT = 'Raise failed';
     END IF;
   END

When VERSION is omitted and the procedure does not exist, Db2 creates the initial version V1 and binds a package using the options on the statement (QUALIFIER, CURRENT DATA, DYNAMICRULES, EXPLAIN, SQL PATH, and the rest of the option-list).

MODIFIES SQL DATA allows INSERT/UPDATE/DELETE. READS SQL DATA allows SELECT but not changes. CONTAINS SQL is even tighter (no read or modify of table data). Db2 checks nested routines against this specification.

CALLED ON NULL INPUT is the native SQL behaviour: the procedure is still called if parameters are null. All parameters are nullable.

External form (body not in the statement):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE APP.SUMMOD
     (IN V1 INTEGER, OUT V2 CHAR(9))
     LANGUAGE C
     DETERMINISTIC
     NO SQL
     EXTERNAL NAME SUMMOD
     COLLID SUMCOLL
     ASUTIME LIMIT 900
     PARAMETER STYLE GENERAL WITH NULLS
     STAY RESIDENT NO
     WLM ENVIRONMENT PAYROLL
     PROGRAM TYPE MAIN
     SECURITY DB2
     DYNAMIC RESULT SETS 10
     COMMIT ON RETURN NO;

ALTER PROCEDURE

Native SQL ALTER is how you version:

• ADD VERSION id — new body and options; does not change which version CALL uses until you activate it
• REPLACE VERSION id — replace that version’s body and options. Options you omit go back to system defaults, even if CREATE had set them — re-specify what you care about
• ACTIVATE VERSION id — make that version the one CALL runs by default
• DROP VERSION id — remove one version (you cannot drop the last / active version without a replacement story)
• Option-only ALTER (for example ASUTIME) on the active version

1
2
3
4
5
6
7
8
9
10
11
12
ALTER PROCEDURE TEST.UPDATE_BALANCE
     REPLACE VERSION V2
     (IN CUSTOMER_NO INTEGER,
      IN AMOUNT DECIMAL(9,2))
     MODIFIES SQL DATA
     ASUTIME LIMIT 100
   BEGIN
     UPDATE ACCOUNTS
        SET BAL = BAL + AMOUNT
      WHERE CUSTNO = CUSTOMER_NO
        AND CUSTSTAT = 'A';
   END

Function level 507: CREATE OR REPLACE PROCEDURE … VERSION V2 adds V2 if missing or replaces it if present. CREATE OR REPLACE without VERSION replaces the procedure definition (privileges on the procedure are kept).

External ALTER changes LANGUAGE, WLM ENVIRONMENT, EXTERNAL NAME, DYNAMIC RESULT SETS, and similar — not an SQL PL body.

DROP PROCEDURE

1
DROP PROCEDURE HR.RAISE_SALARY RESTRICT;

DROP removes the procedure from the catalog, including versions. Packages for native SQL go away with it. Callers fail until you recreate. Prefer DROP VERSION when you only want to retire an old native version. Do not confuse DROP with -STOP PROCEDURE, which only blocks scheduling.

CALL

CALL invokes the procedure. Arguments must be compatible types. The defined encoding scheme of a parameter comes from CREATE PROCEDURE (or the subsystem default).

1
2
3
4
5
6
7
8
9
10
CALL HR.RAISE_SALARY('000010', 0.05);
 
-- Embedded SQL (host variables)
-- EXEC SQL CALL HR.RAISE_SALARY (:HV-EMPNO, :HV-RATE);
 
-- Procedure name in a host variable
-- EXEC SQL CALL :PROCNM (:HV-EMPNO, :HV-RATE);
 
-- SQLDA
-- EXEC SQL CALL HR.RAISE_SALARY USING DESCRIPTOR :SQLDA;

Simple CALL with literals works in SPUFI/CLP for IN parameters. OUT and INOUT need host variables (or JDBC parameter markers) so Db2 has somewhere to return values.

Nested CALL: a procedure may CALL another. Recursion is possible but must be bounded (ASUTIME helps). Do not assume Language Environment variables persist between external CALLs — another user’s procedure or another SPAS may run in between.

If an external procedure abends enough times (STOP AFTER n FAILURES or subsystem MAX ABEND COUNT), Db2 stops it (STOPABN on that member in data sharing). Other members may still run it. Fix the abend, then START PROCEDURE.

CALL and versions

Native SQL: CALL uses the active version unless you specify a version or SET CURRENT ROUTINE VERSION. That register is covered with operations and debugging. BIND DEPLOY copies a version to another subsystem — a promotion path instead of shipping COBOL loadlibs.

Explain It Like I'm Five

CREATE PROCEDURE is writing the recipe card and pinning it on the kitchen board (and Db2 secretly prints a package cookbook). ALTER is taping a new revision on the card, or adding a second card labelled V2 and later saying “cook from V2.” DROP throws the cards away. CALL is shouting the dish name through the window. COMMIT ON RETURN YES is the kitchen also ringing up the bill when the plate comes out. STOP PROCEDURE is a “closed” sign; the recipes are still in the drawer.

Exercises

1. Write CREATE PROCEDURE with one IN CHAR(6) and LANGUAGE SQL that SELECTs a salary into an OUT DECIMAL. Which SQL data-access clause do you use?
2. ALTER REPLACE VERSION without repeating QUALIFIER. What happens to QUALIFIER?
3. Write CALL with two host-variable placeholders as you would in embedded SQL.
4. A developer drops a procedure to “refresh” it and loses GRANTs. Which CREATE OR REPLACE behaviour would have kept privileges?
5. Contrast COMMIT ON RETURN YES with AUTONOMOUS in one sentence each.