DB2 procedure parameters and result sets

Procedure parameters

The parameter list on CREATE PROCEDURE (and on every version) is an ordered list of name, usage, and type. All parameters are nullable. The number, data types, and IN/OUT/INOUT usage must match across versions of the same procedure (synonyms such as INT and INTEGER count as a match). Parameter names may differ between versions.

Inside a native SQL procedure, a parameter is an SQL variable: you SET it, use it in SQL, no colon. In COBOL, you pass host variables in CALL order. JDBC uses registerOutParameter for OUT/INOUT.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE HR.EMP_SALARY
     (IN  P_EMPNO  CHAR(6),
      OUT P_SALARY DECIMAL(9,2),
      OUT P_MSG    VARCHAR(70))
     LANGUAGE SQL
     READS SQL DATA
   BEGIN
     SELECT SALARY INTO P_SALARY
       FROM EMP
      WHERE EMPNO = P_EMPNO;
     SET P_MSG = 'OK';
   END

P_EMPNO is IN: even if the procedure SET P_EMPNO = '999999', the caller still sees the employee number they passed. P_SALARY and P_MSG are OUT. If the SELECT finds no row and you do not handle it, an exception returns to the caller; if you handle NOT FOUND and forget SET P_SALARY, the caller gets null for that OUT.

Types, CCSID, and locators for LOBs

Built-in types follow CREATE TABLE rules, with larger max lengths for VARCHAR / VARBINARY / VARGRAPHIC parameters than for table columns (IBM: 32704 / 16352). PARAMETER CCSID ASCII/EBCDIC/UNICODE (or a CCSID clause on the parameter) sets encoding; otherwise DEF ENCODING SCHEME from DSNTIPF applies.

AS LOCATOR on a LOB (or distinct type based on LOB) parameter passes a locator instead of the whole value — fewer bytes on the CALL. Autonomous procedures cannot use LOB, XML, or LOB-based array parameters.

Datetime parameters are passed as character ISO. Distinct types are passed as their source type. Array types are allowed on native SQL (not for autonomous).

IN, OUT, and INOUT in one CALL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE APP.ADJUST_BONUS
     (IN    P_EMPNO  CHAR(6),
      INOUT P_BONUS  DECIMAL(9,2),
      OUT   P_NEWPAY DECIMAL(9,2))
     LANGUAGE SQL
     MODIFIES SQL DATA
   BEGIN
     UPDATE EMP
        SET BONUS = P_BONUS
      WHERE EMPNO = P_EMPNO;
     SET P_BONUS = P_BONUS * 1;  -- echoed INOUT
     SELECT SALARY + BONUS INTO P_NEWPAY
       FROM EMP
      WHERE EMPNO = P_EMPNO;
   END

The caller supplies EMPNO and a proposed bonus, gets the (possibly adjusted) bonus back on the same host variable, and a separate OUT for total pay. Map host variables in the same order; null indicators are required in embedded SQL because parameters are nullable.

Result sets

DYNAMIC RESULT SETS integer (synonyms RESULT SET / RESULT SETS) is the maximum number of query result sets the procedure can return, 0–32767. Default 0.

Pattern:

• CREATE PROCEDURE … DYNAMIC RESULT SETS 2
• In the body: DECLARE cursor WITH RETURN (native SQL uses WITH RETURN TO CALLER or TO CLIENT as documented for that release)
• OPEN the cursor; do not CLOSE it before END
• Caller fetches after CALL

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE HR.LIST_DEPT
     (IN P_DEPT CHAR(3))
     LANGUAGE SQL
     READS SQL DATA
     DYNAMIC RESULT SETS 1
   BEGIN
     DECLARE C1 CURSOR WITH RETURN FOR
       SELECT EMPNO, LASTNAME, SALARY
         FROM EMP
        WHERE WORKDEPT = P_DEPT
        ORDER BY LASTNAME;
     OPEN C1;
   END

If COMMIT ON RETURN YES, define the cursor WITH HOLD or the commit on CALL return closes it and the client sees no rows.

Result set locators, ASSOCIATE LOCATORS, DESCRIBE PROCEDURE

Host programs declare result set locator variables (SQL TYPE IS RESULT_SET_LOCATOR in COBOL/C). After CALL:

1
2
3
4
5
6
7
8
CALL HR.LIST_DEPT('D11');
 
ASSOCIATE LOCATORS (:LOC1) WITH PROCEDURE HR.LIST_DEPT;
 
ALLOCATE C_DEPT CURSOR FOR RESULT SET :LOC1;
 
-- FETCH C_DEPT INTO ... until SQLCODE +100
-- CLOSE C_DEPT

ASSOCIATE LOCATORS (ASSOCIATE RESULT SET LOCATOR[S]) copies locator values for each result set. If you list more locators than sets, extras are 0. If you list fewer, you only see that many sets. The procedure name must be written the same way as on CALL (one-part vs schema.proc vs location.schema.proc). A host variable may hold the name. The statement is embeddable and can be dynamically prepared; it is not an interactive SPUFI one-liner in the usual course.

If the same procedure is called more than once from the same program, only the most recent CALL’s result sets are accessible.

DESCRIBE PROCEDURE INTO an SQLDA returns how many result sets and locator information when you do not know the shape at compile time. SQLD is the count. Then ASSOCIATE LOCATORS using those values, ALLOCATE CURSOR, FETCH. Same naming rule as ASSOCIATE: must match CALL.

1
2
3
CALL SITE2.MYSCHEMA.P1;
DESCRIBE PROCEDURE SITE2.MYSCHEMA.P1 INTO :SQLDA;
ASSOCIATE LOCATORS (:LOC1, :LOC2) WITH PROCEDURE SITE2.MYSCHEMA.P1;

JDBC/ODBC hide locators: CallableStatement execute, then getResultSet / getMoreResults. The Db2 concepts are the same: DYNAMIC RESULT SETS on the server, multiple result sets on the client.

Explain It Like I'm Five

IN is handing the kitchen a note they may read but must give you back unchanged. OUT is an empty box they fill; if they forget, the box is empty (null). INOUT is a box you send with something in it that they may replace. A result set is not a box — it is a conveyor belt of plates (rows). DYNAMIC RESULT SETS is how many belts they are allowed to leave running. ASSOCIATE LOCATORS is you grabbing the belt handles. DESCRIBE PROCEDURE is asking “how many belts did you start?” before you grab.

Exercises

1. Change EMP_SALARY so P_EMPNO is INOUT and the procedure returns the EMPNO found. Why is IN the wrong choice for that?
2. Write CREATE PROCEDURE with DYNAMIC RESULT SETS 2 and two WITH RETURN cursors (employees and projects for a department).
3. COMMIT ON RETURN YES with a result set. Which cursor option is required and why?
4. CALL uses HR.LIST_DEPT but ASSOCIATE uses LIST_DEPT. Predict the problem.
5. ASSOCIATE LOCATORS lists three locators but the procedure returns one set. What are the extra locators set to?