DB2 SQL PL procedures and functions

Four LANGUAGE SQL objects

External procedures and external UDFs (COBOL, C, Java) are not SQL PL; they have a LANGUAGE other than SQL and an EXTERNAL NAME. Sourced functions wrap another function and have no SQL PL body. This page stays on LANGUAGE SQL.

Native SQL procedures

CREATE PROCEDURE (SQL — native) registers a routine whose body is SQL PL. IBM transforms that body into a program and binds a package. First CREATE makes version V1 unless you specify VERSION. ALTER PROCEDURE ADD/REPLACE VERSION and ACTIVATE VERSION are how shops roll out logic without renaming the CALL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE PROCEDURE HR.GIVE_RAISE
     (IN  P_EMPNO CHAR(6),
      IN  P_PCT   DECIMAL(5,2),
      OUT P_NEWSAL DECIMAL(9,2))
     LANGUAGE SQL
     MODIFIES SQL DATA
     DETERMINISTIC
     COMMIT ON RETURN NO
   BEGIN
     UPDATE DSN8C10.EMP
        SET SALARY = SALARY * (1 + P_PCT / 100)
      WHERE EMPNO = P_EMPNO;
     SELECT SALARY INTO P_NEWSAL
       FROM DSN8C10.EMP
      WHERE EMPNO = P_EMPNO;
   END

• LANGUAGE SQL — native SQL PL; not a WLM load module (debug can still use a WLM ENVIRONMENT FOR DEBUG MODE).
• SQL data access — CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA. Pick the weakest that is still true so Db2 can reject illegal statements at CREATE time.
• DYNAMIC RESULT SETS n — needed if you leave WITH RETURN cursors open.
• COMMIT ON RETURN — NO leaves the unit of work to the caller; YES commits when CALL succeeds (result-set cursors need WITH HOLD to survive).
• DETERMINISTIC / NOT DETERMINISTIC — documents whether the same inputs yield the same effect; Db2 does not prove it.

Invoke with CALL HR.GIVE_RAISE(:HV-EMP, :HV-PCT, :HV-NEW). Nested CALL is how SQL PL procedures reuse each other. Nested and recursive CALL share the same depth limit.

Compared with external SQL procedures (deprecated): native SQL lives in the catalog and package; you do not precompile a generated C program. New work should be native.

SQL scalar functions

An SQL scalar function returns one value per invocation. The body is SQL PL. Db2 distinguishes inlined and compiled scalar functions from the CREATE FUNCTION text.

Inlined SQL scalar functions

If the definition is a simple RETURN expression, Db2 can inline that expression into the statement that called the function. There is no separate package to EXPLAIN as a routine. That is fast and simple — and limited. You do not get a full compound with handlers and loops.

1
2
3
4
5
6
7
CREATE FUNCTION HR.YR_SAL (P_SAL DECIMAL(9,2))
  RETURNS DECIMAL(11,2)
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  NO EXTERNAL ACTION
  RETURN P_SAL * 12;

Use inlined scalars for formulas you want in many queries without repeating the arithmetic. If you later add BEGIN, IF, or other enhanced CREATE FUNCTION features, Db2 creates a compiled function instead.

Compiled SQL functions

Compiled SQL scalar functions support the larger SQL PL statement set: compound statements, IF, loops, GET DIAGNOSTICS, and an enhanced RETURN that can reference a scalar fullselect. A package is generated. Each invocation runs that package (one or more times).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE FUNCTION HR.REVERSE_STR (INSTR VARCHAR(100))
  RETURNS VARCHAR(100)
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  NO EXTERNAL ACTION
  BEGIN
    DECLARE I INT;
    DECLARE REV VARCHAR(100) DEFAULT '';
    SET I = LENGTH(INSTR);
    WHILE I > 0 DO
      SET REV = REV || SUBSTR(INSTR, I, 1);
      SET I = I - 1;
    END WHILE;
    RETURN REV;
  END

RETURN supplies the scalar result and ends the function. You can RETURN from more than one branch of IF. Forgetting RETURN is a CREATE-time or run-time problem — every path should return a value (or SIGNAL).

Options that matter in SQL:

• DETERMINISTIC — same inputs, same result. Lets the optimizer reuse results more aggressively. If you read a table that can change, you are not deterministic.
• NO EXTERNAL ACTION / EXTERNAL ACTION — whether the function changes something outside SQL (or must be treated as if it might).
• READS SQL DATA versus CONTAINS SQL — whether the body SELECTs. Scalar functions that MODIFIES SQL DATA are tightly restricted compared with procedures; prefer a procedure for updates.
• SECURED — required in some row-permission / column-mask / secure context uses. Unsecured functions can be rejected in those statements.
• CALLED ON NULL INPUT (typical default) versus RETURNS NULL ON NULL INPUT — whether a null argument still runs the body.

Invocation is ordinary SQL: SELECT HR.REVERSE_STR(LASTNAME) FROM EMP. Function resolution uses schema, name, and argument types (overloading). QUALIFIER / PATH special registers affect which schema’s function you get, just like built-in versus user function name collisions.

SQL table functions

An SQL table function returns a set of rows. IBM’s CREATE FUNCTION (SQL table) statement defines RETURNS TABLE (column definitions) and an SQL routine body that is a RETURN statement whose expression is a SELECT. That SELECT is copied into the invoking query. No package is generated for the table function itself.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FUNCTION HR.DEPTEMPLOYEES (DEPTNO CHAR(3))
  RETURNS TABLE (
    EMPNO    CHAR(6),
    LASTNAME VARCHAR(15),
    FIRSTNME VARCHAR(12)
  )
  LANGUAGE SQL
  READS SQL DATA
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN
    SELECT EMPNO, LASTNAME, FIRSTNME
      FROM DSN8C10.EMP
     WHERE WORKDEPT = DEPTNO;

Call it in FROM:

1
2
SELECT T.EMPNO, T.LASTNAME
  FROM TABLE(HR.DEPTEMPLOYEES('A00')) AS T;

CARDINALITY integer on CREATE FUNCTION is a hint to the optimizer about expected rows — it does not enforce a limit. Because the body is a single RETURN SELECT, you do not write WHILE or handlers in an SQL table function. If you need procedural staging, return a result set from a native procedure instead, or keep the table function as a thin parameterized view and put loops in a procedure that fills a DGTT.

Data access and side effects

Procedures are the right home for MODIFIES SQL DATA plus OUT parameters. Functions in a SELECT list that quietly UPDATE will surprise every EXPLAIN and every auditor. Triggers can also contain SQL PL; they fire on data change rather than CALL or function invocation — a later tutorial section.

Choosing among them

• Reusable multi-statement business API — native SQL procedure, CALL from COBOL/JDBC/SQL PL
• One computed value in many queries — SQL scalar function (inlined if it stays a formula; compiled if it needs SQL PL)
• Parameterized row set in FROM — SQL table function
• Need a package you can EXPLAIN, BIND options, versions — native procedure or compiled scalar function
• Need result-set cursors or INOUT — procedure only

DROP PROCEDURE / DROP FUNCTION remove the object. ALTER FUNCTION for compiled SQL scalars follows similar versioning ideas to procedures on recent function levels — check your Db2 FL before you assume CREATE OR REPLACE. GRANT EXECUTE is what callers need; static SQL inside a compiled function or native procedure uses the package owner’s table privileges unless DYNAMICRULES say otherwise.

Explain It Like I'm Five

A native SQL procedure is a recipe card you keep in the kitchen (Db2). Someone must say CALL to cook it; they can hand you ingredients (IN) and get a plate back (OUT), or even a whole tray of cookies (result set). A scalar function is a measuring spoon: you use it inside a sentence (“give me reverse of this name”) and you get one spoonful. If the spoon is inlined, Db2 copies the measuring trick into the sentence. If it is compiled, Db2 keeps a tiny machine for that spoon. A table function is a magic lunchbox: you open it in FROM and find a list of sandwiches already made from a SELECT. You do not put a washing-machine loop inside the lunchbox — that loop belongs on a recipe card (procedure) or a compiled spoon.

Exercises

1. Write CREATE PROCEDURE LANGUAGE SQL with one IN and one OUT that SELECTs a last name. Which data-access clause do you need?
2. Convert that lookup to a compiled SQL scalar function. How does the caller invoke it differently?
3. Write an SQL table function that returns EMPNO and LASTNAME for a department. Show the TABLE(…) FROM clause.
4. List two CREATE FUNCTION options you would set if the function is used in a row permission and always returns the same output for the same input.
5. Why is a native SQL procedure a better place than a table function for a WHILE loop that writes an audit table?