DB2 SQL PL variables and parameters

Variables versus parameters

Think of a native SQL procedure as a small program that Db2 compiles into a package. Parameters are the procedure’s public interface: the CREATE PROCEDURE argument list. The caller of CALL supplies IN and INOUT values and receives OUT and INOUT values when CALL returns. SQL variables are private scratch space. The caller never sees them. You declare them inside BEGIN … END for data you use only within the body.

IBM documents SQL variables as similar to host variables in an external stored procedure: same families of data types and lengths as procedure parameters, usable on the left or right of assignment, and usable in SQL statements the way a host variable would be. The important surface difference for COBOL programmers is the missing colon. In a COBOL program you write WHERE EMPNO = :HV-EMPNO. Inside SQL PL you write WHERE EMPNO = P_EMPNO or WHERE EMPNO = V_EMPNO — the name is an SQL identifier, not a host variable.

Scope is the compound statement that declared the name, including SQL statements nested in that compound (IF, WHILE, inner BEGIN if your function level allows nested compounds). A name declared in an inner BEGIN does not leak to the outer BEGIN. A parameter name is visible throughout the procedure version; it must not duplicate another parameter of the same version, and it should not duplicate an SQL variable in the same compound.

SQL variables

Declare SQL variables at the top of the compound statement, before cursors and handlers:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE HR.EMP_NAME
     (IN  P_EMPNO CHAR(6),
      OUT P_FULL  VARCHAR(40))
     LANGUAGE SQL
     READS SQL DATA
   BEGIN
     DECLARE V_FIRST VARCHAR(12);
     DECLARE V_MID   CHAR(1)     DEFAULT ' ';
     DECLARE V_LAST  VARCHAR(15);
     DECLARE V_FOUND INT         DEFAULT 0;
 
     SELECT FIRSTNME, MIDINIT, LASTNAME
       INTO V_FIRST, V_MID, V_LAST
       FROM DSN8C10.EMP
      WHERE EMPNO = P_EMPNO;
 
     SET P_FULL = V_FIRST || ' ' || V_MID || ' ' || V_LAST;
   END

DECLARE form

The general form is DECLARE sql-variable-name data-type, optionally followed by DEFAULT constant or DEFAULT NULL. You may list several names that share one type:

1
2
3
DECLARE DATEINDEX, WEEKENDINDEX INT DEFAULT 1;
DECLARE V_MSG VARCHAR(100) DEFAULT 'OK';
DECLARE V_SAL DECIMAL(9,2);   -- starts as NULL

• data-type — a built-in type (or a user-defined array type) with the same length rules as a procedure parameter. INTEGER, INT, SMALLINT, BIGINT, DECIMAL, CHAR, VARCHAR, DATE, TIMESTAMP, and so on are typical. Match the column you will SELECT INTO so you do not truncate or overflow.
• DEFAULT constant — the variable is initialized to that constant when the procedure is called (more precisely, when the compound statement begins). Use this for counters, flags, and “empty string” starting points.
• DEFAULT NULL or omitted DEFAULT — the variable starts as NULL. That is easy to forget: an INT with no DEFAULT is not zero; it is null, and V_COUNT = V_COUNT + 1 yields null until you SET a real number.
• RESULT_SET_LOCATOR VARYING — a special declaration when this procedure will ASSOCIATE LOCATORS for a result set returned by a nested CALL. That is locator machinery, not a business column.

Names are SQL identifiers. Db2 folds ordinary identifiers to uppercase. Do not use a delimited identifier with lowercase letters for an SQL variable name — IBM’s SQL PL rules reject that form. Keep names unique within the compound statement. Do not reuse a parameter name as a variable name.

You can also declare the special return-code variables SQLSTATE and SQLCODE in the outermost compound statement. After each SQL statement Db2 sets them. You may assign to them, but handlers ignore that assignment, and the next SQL statement overwrites the value. Copy SQLCODE into your own V_SQLCODE immediately if you need to log it. Those two names are covered with handlers on the next SQL PL pages; declare them only once, in the outer BEGIN.

Qualification and name hiding

If a label sits on the compound statement, you can write label.variable to be explicit. That matters when a SELECT list or WHERE clause uses a name that exists both as a column and as a variable. IBM’s rule is: the identifier is a column when both interpretations are possible. Beginners write WHERE LASTNAME = LASTNAME and wonder why every row qualifies. Prefix variables (V_LASTNAME) or qualify (P1.V_LASTNAME) so the predicate compares a column to a variable.

Parameters

Parameters are named on CREATE PROCEDURE (and CREATE FUNCTION). Each has a usage and a type:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE PROCEDURE HR.RAISE_BONUS
     (IN    P_EMPNO   CHAR(6),
      IN    P_PCT     DECIMAL(4,2),
      INOUT P_BONUS   DECIMAL(9,2),
      OUT   P_RC      INTEGER)
     LANGUAGE SQL
     MODIFIES SQL DATA
   BEGIN
     SET P_RC = 0;
     SET P_BONUS = P_BONUS * (1 + P_PCT / 100);
     UPDATE DSN8C10.EMP
        SET BONUS = P_BONUS
      WHERE EMPNO = P_EMPNO;
   END

• IN — input only. This is the default if you omit IN/OUT/INOUT. Provide values the procedure needs: keys, options, cut-off dates. You may SET an IN parameter inside the body (for example to normalize a code to uppercase), but that new value is not passed back. After CALL, the caller’s host variable still holds what it passed in.
• OUT — output only. Db2 sets it to NULL on entry. You assign the result the caller should receive. If the procedure fails to SET one or more OUT (or INOUT) parameters, Db2 does not raise a special “unset parameter” error; it returns whatever the parameter holds on the way out (NULL for an untouched OUT). Always initialize OUT status codes so the caller can tell success from “we never got that far.”
• INOUT — both directions. The caller’s value is the starting value. The last assignment is what CALL returns. A classic pattern is “here is the current bonus; give me the new bonus.” If you never SET it, the original input comes back — which can look like success when you actually skipped the UPDATE.

Parameter names can differ across versions of the same native procedure; the usage (IN/OUT/INOUT) of corresponding parameters must match across versions. Types must be compatible with what CALL passes. Indicator variables on the CALL side still matter in COBOL when an argument can be null.

Db2 for z/OS native SQL procedures do not use the Db2 for i / LUW style of DEFAULT values on the CREATE PROCEDURE parameter list as a way to omit arguments on CALL. Pass every parameter, or design OUT/INOUT so unused inputs are explicit NULLs you document.

Assignment

Assignment is how variables and parameters get values after DECLARE. IBM lists the assignment-statement (SET) as an SQL control statement. Fetching and SELECT INTO are ordinary SQL statements that happen to target SQL variables.

SET

SET evaluates an expression and stores it in an SQL variable, SQL parameter, or array element. It does not update a table. Typical uses: initialize OUT parameters, bump a loop index, concatenate a name, store CARDINALITY of an array, set a flag that a CONTINUE handler just fired.

1
2
3
4
5
SET V_FOUND = 1;
SET P_RC = 0;
SET V_FULL = V_FIRST || ' ' || V_LAST;
SET DATEINDEX = DATEINDEX + 1;
SET WEEKENDS[WEEKENDINDEX] = MYDATES[DATEINDEX];

Assignment follows Db2’s usual assignment and comparison rules (truncation, numeric overflow, datetime conversion). Assigning NULL is allowed when the target is nullable, which SQL variables are. If you need a NOT NULL business field, check for null yourself before you UPDATE a NOT NULL column.

SELECT INTO and VALUES INTO

SELECT INTO produces a result table of at most one row and assigns each select-list value to a target in order. Targets can be SQL variables, SQL parameters, global variables, or array elements. The number of targets must equal the number of values. A target must not appear twice in the INTO list.

1
2
3
4
5
6
7
SELECT COUNT(*)
  INTO V_NUM
  FROM DSN8C10.EMP
 WHERE WORKDEPT = P_DEPT;
 
VALUES (CURRENT DATE, CURRENT TIME)
  INTO V_DAY, V_TM;

If the result is empty, Db2 does not assign to the targets (they keep their previous values) and raises NOT FOUND (SQLSTATE 02000, SQLCODE +100). If more than one row qualifies, that is an error — use a cursor or an aggregate. Handle NOT FOUND with a CONTINUE handler that sets a flag, or test SQLSTATE after the SELECT if you declared the return-code variables.

Statement strings you PREPARE later cannot embed SQL variable names as host-style substitutions. Dynamic SQL uses parameter markers (?) and EXECUTE … USING or OPEN … USING. That is a later page; the rule belongs here because beginners try SET STMT = '… WHERE DEPT = P_DEPT' and then wonder why the literal letters P_DEPT appear in the table name.

SQL variables compared with host variables

• Prefix — host variables use a colon in embedded SQL; SQL variables do not.
• Declaration place — host variables live in the COBOL/PL/I/C program; SQL variables live in BEGIN … END.
• Nulls — host variables often pair with a NULL indicator halfword; SQL variables are themselves nullable unless you always SET them.
• Lifetime — SQL variables last for the compound statement (one CALL of that procedure version, unless you are in a nested compound). They are not static across CALLs.

When a COBOL program CALLs a native SQL procedure, the COBOL side still uses host variables on the CALL statement. Inside the procedure, those values arrive as SQL parameters. You do not re-declare them with DECLARE; the CREATE PROCEDURE list already did that.

Explain It Like I'm Five

A parameter is a labelled box you hand through a window. IN is a box the other person may look at but must give back unchanged. OUT is an empty box they fill for you. INOUT is a box that already has something in it; they may replace the contents before handing it back. An SQL variable is a sticky note on their desk. You never see the sticky note. SET writes on a sticky note or in a box. SELECT INTO copies one row from a filing cabinet onto sticky notes. If the cabinet has no matching folder, the sticky notes stay as they were and Db2 says “not found.”

Exercises

1. Declare three SQL variables for first name, last name, and a found-flag. Give the flag a DEFAULT of 0 and explain what happens if you omit DEFAULT on the flag.
2. Write CREATE PROCEDURE with IN P_DEPT CHAR(3), OUT P_COUNT INTEGER. Inside, SELECT COUNT(*) INTO P_COUNT. Why is P_COUNT an OUT parameter rather than a local variable only?
3. Inside a procedure, SET an IN parameter to uppercase and also SET an OUT parameter. After CALL, which change does the COBOL caller see?
4. A SELECT INTO finds zero rows. What happens to the INTO variables, and which SQLSTATE should you handle?
5. Explain why WHERE LASTNAME = LASTNAME is a bad predicate if LASTNAME is both a column and an SQL variable, and how you would fix it.