User-defined functions in DB2 for z/OS

What a user-defined function is

A user-defined function is a named routine you invoke in SQL the same way you invoke a built-in function: in a select list, a predicate, a SET assignment, or (for table functions) in the FROM clause with TABLE(). Db2 stores the definition in the catalog, mainly SYSIBM.SYSROUTINES and related parameter tables. Invocation uses the function name and argument list. Administration uses the specific name, which uniquely identifies one overload.

Think of three implementation families:

• External — a program outside SQL (COBOL, C, Java, Assembler, PL/I)
• SQL — the body is SQL PL in the CREATE FUNCTION statement
• Sourced — the body is “call this other function”

CREATE FUNCTION

CREATE FUNCTION registers the function at the current server. The statement form you use must match the kind of function. You cannot combine EXTERNAL, SOURCE, and an SQL BEGIN/RETURN body in one CREATE. Schema qualification matters: an unqualified name is created in the schema that ownership rules assign (often the current SQL authorization ID for dynamic SQL). You need CREATEIN on that schema, or ownership/SYSADM-class authority.

Common pieces that appear across forms:

• function-name (parameters) — the SQL name used at invocation
• RETURNS — a scalar type, or TABLE (...) for table functions
• SPECIFIC specific-name — unique name for ALTER, DROP, GRANT, and operator commands. If you omit it, Db2 generates one
• DETERMINISTIC / NOT DETERMINISTIC — same inputs, same result or not
• NO EXTERNAL ACTION / EXTERNAL ACTION — whether the function has side effects outside SQL (files, messages, and so on)
• CONTAINS SQL / READS SQL DATA / MODIFIES SQL DATA / NO SQL — how much SQL the body is allowed to run

Inlined SQL scalar functions

An inlined SQL scalar function has a single RETURN of a simple expression. Db2 does not call a package. It copies the expression into the invoking statement, so the optimizer can treat it like other SQL. No package is generated.

1
2
3
4
5
6
CREATE FUNCTION HR.FULL_NAME (FIRST VARCHAR(12), LAST VARCHAR(15))
  RETURNS VARCHAR(30)
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  RETURN FIRST CONCAT ' ' CONCAT LAST;

When you process CREATE FUNCTION for an SQL scalar, Db2 first tries to create an inlined function. If the body uses features that cannot be inlined, it creates a compiled SQL scalar function instead.

Compiled SQL scalar functions

A compiled SQL scalar function supports SQL PL: DECLARE, IF, WHILE, nested SQL, and a RETURN that can use a scalar fullselect. Db2 generates a package that holds the body. Each invocation runs that package one or more times. Compiled functions can use options similar to native SQL procedures (versioning-related options, special register inheritance, and so on, depending on function level).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE FUNCTION HR.REVERSE (INSTR VARCHAR(4000))
  RETURNS VARCHAR(4000)
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  BEGIN
    DECLARE REVSTR VARCHAR(4000) DEFAULT '';
    DECLARE RESTSTR VARCHAR(4000);
    DECLARE LEN INTEGER;
    IF INSTR IS NULL THEN
      RETURN NULL;
    END IF;
    SET RESTSTR = INSTR;
    SET LEN = LENGTH(INSTR);
    WHILE LEN > 0 DO
      SET REVSTR = SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR;
      SET RESTSTR = SUBSTR(RESTSTR, 2, LEN - 1);
      SET LEN = LEN - 1;
    END WHILE;
    RETURN REVSTR;
  END

SQL table functions

An SQL table function returns a table. The body is a RETURN of a SELECT. Db2 inlines that query into the caller. Invoke it with TABLE(function(args)) and a correlation name.

1
2
3
4
5
6
7
8
9
10
11
CREATE FUNCTION HR.DEPT_EMPS (DEPT CHAR(3))
  RETURNS TABLE (EMPNO CHAR(6), LASTNAME VARCHAR(15))
  LANGUAGE SQL
  READS SQL DATA
  RETURN
    SELECT EMPNO, LASTNAME
      FROM HR.EMPLOYEE
     WHERE WORKDEPT = DEPT;
 
SELECT E.EMPNO, E.LASTNAME
  FROM TABLE(HR.DEPT_EMPS('A00')) AS E;

External scalar and external table functions

An external scalar function is a program that returns one value. An external table function is a program that Db2 starts repeatedly; each start returns the next row until the program signals no more rows. You register the executable with EXTERNAL NAME, LANGUAGE, PARAMETER STYLE, and usually a WLM ENVIRONMENT.

• LANGUAGE — ASSEMBLE, C, COBOL, JAVA, or PLI
• PARAMETER STYLE SQL (or JAVA for Java) — how arguments and null indicators are passed
• FENCED — the default; the program runs outside Db2 storage so a bad pointer cannot overlay Db2. On z/OS this is the supported model for external UDFs
• SCRATCHPAD / FINAL CALL — keep state across invocations (typical for table functions that read a file)
• ALLOW PARALLEL / DISALLOW PARALLEL — whether Db2 may run copies in parallel
• CARDINALITY — estimated rows for an external table function, used by the optimizer
• STAY RESIDENT — whether the load module stays in memory
• PROGRAM TYPE MAIN or SUB — entry-point style
• ASUTIME — CPU service-unit limit
• STOP AFTER n FAILURES / CONTINUE AFTER FAILURE — abend handling

1
2
3
4
5
6
7
8
9
10
11
12
CREATE FUNCTION PAYROLL.TAX_RATE (SALARY DECIMAL(9,2))
  RETURNS DECIMAL(5,4)
  SPECIFIC PAYROLL.TAXRATE1
  EXTERNAL NAME TAXRT
  LANGUAGE COBOL
  PARAMETER STYLE SQL
  DETERMINISTIC
  FENCED
  NO SQL
  NO EXTERNAL ACTION
  WLM ENVIRONMENT DSNWLM
  STAY RESIDENT NO;

Sourced functions

A sourced function is implemented by another scalar or aggregate function that already exists. It has no package of its own. If the source is an external scalar, the sourced function inherits EXTERNAL attributes through the chain. The usual reason to write one is strong typing on distinct types: built-in “+” does not accept MONEY until you source a “+” that takes MONEY.

1
2
3
4
5
CREATE TYPE HR.MONEY AS DECIMAL(9,2);
 
CREATE FUNCTION HR."+" (HR.MONEY, HR.MONEY)
  RETURNS HR.MONEY
  SOURCE SYSIBM."+" (DECIMAL(), DECIMAL());

You can also source to change the parameter types of an existing UDF, for example wrapping an INTEGER/FLOAT function so both arguments are INTEGER.

User-defined aggregate functions

Sourced functions can be based on an existing aggregate (column) function as well as a scalar function. That is how you give a distinct type a SUM or AVG that makes sense. There is no separate “write an aggregate from scratch in COBOL” form on z/OS in the same way as a scalar external UDF; you source aggregates from functions that already aggregate.

Function parameters

Each parameter has a name (optional on some forms) and a data type. Types follow the same rules as other SQL: built-in types, distinct types, LOBs (often with AS LOCATOR), and for some compiled/SQL forms a table locator for a transition table from a trigger. A function with a table parameter can only be invoked from a trigger’s triggered action.

• RETURNS NULL ON NULL INPUT — if any argument is null, return null without running the body (common default)
• CALLED ON NULL INPUT — invoke even when an argument is null; the program must handle null indicators
• PARAMETER CCSID — ASCII, EBCDIC, or UNICODE for string parameters
• VARYING / NULTERM / STRUCTURE — C string conventions on external functions

Overloads are distinguished by the number and types of parameters, not by the return type alone. Two functions named TAX_RATE that both take DECIMAL(9,2) collide even if they return different types.

Determinism and volatility

DETERMINISTIC means the same argument values always produce the same result. NOT DETERMINISTIC means the result can change (random numbers, current time, a row count that moves). Db2 uses this for rewriting and for scrollable cursors: if a SELECT list calls a non-deterministic function, fetching the same row twice can return different values.

EXTERNAL ACTION vs NO EXTERNAL ACTION is the related “volatility” story for side effects. A function that writes a file or sends a message is EXTERNAL ACTION. The optimizer must not skip or duplicate those calls casually. Marking a function DETERMINISTIC and NO EXTERNAL ACTION when it is not is a correctness bug, not a free performance win.

Function security

Privileges and runtime identity are separate questions.

• EXECUTE — required to invoke the function
• CREATEIN on the schema — required to create the function there
• ALTERIN / DROPIN — alter or drop functions in that schema
• SECURITY DB2 (external default) — the function runs under Db2 authorization rules
• SECURITY USER — the external environment uses the user’s identity (for example a RACF user associated with the invoker)
• SECURITY DEFINER — the external environment uses the definer’s identity
• SECURED / NOT SECURED — a secure function can be used from a row permission or column mask. Creating a secure object requires the CREATE_SECURE_OBJECT privilege (typically held by SECADM)

Static SQL in a compiled function package uses the package owner’s privileges for embedded SQL, similar to other packages. Dynamic SQL inside a function follows DYNAMICRULES and CURRENT SQLID rules for that package.

Function packages

External functions execute a load module in a WLM-managed address space. Compiled SQL scalar functions execute a Db2-generated package. Inlined SQL scalar functions and SQL table functions do not have a package of their own. Sourced functions inherit the source’s implementation.

Collection, WLM environment, STAY RESIDENT, and PROGRAM TYPE are operational knobs for external functions. After you change the load module, you often need to STOP then START the function (or recycle the WLM application environment) so Db2 picks up the new copy. SQL functions change when you ALTER or DROP/CREATE; compiled SQL functions rebind as part of that DDL.

Function schema, path, resolution, and overloading

Functions live in a schema. Unqualified references are resolved with the SQL path: special register CURRENT PATH for dynamic SQL, and the PATH bind option for static SQL. Db2 walks the schema list in order and picks the best match for the name and argument types. SYSIBM is on the path so built-in functions remain visible.

Overloading means several functions share a function name but have different signatures. Resolution picks one overload. If none fits, you get a function not found error (commonly SQLCODE -440). If more than one fits equally well, you get an ambiguous function error. Casts and distinct types change which overload wins—this is why sourced functions exist.

The specific name is never used to invoke the function in SQL. It is used to tell ALTER FUNCTION, DROP FUNCTION, GRANT, REVOKE, COMMENT, DISPLAY FUNCTION, START FUNCTION, and STOP FUNCTION which overload you mean.

ALTER FUNCTION and DROP FUNCTION

ALTER FUNCTION changes attributes of an existing function. What you can change depends on the kind: WLM environment, ASUTIME, SECURED, and similar options for external functions; SQL body replacement for some SQL functions. Identify the target by signature or by SPECIFIC name.

1
2
3
4
5
ALTER FUNCTION PAYROLL.TAX_RATE (DECIMAL(9,2))
  WLM ENVIRONMENT DSNWLM_NEW;
 
ALTER SPECIFIC FUNCTION PAYROLL.TAXRATE1
  STOP AFTER 5 FAILURES;

DROP FUNCTION removes the catalog definition. Dependent views, triggers, and other routines can block the drop. DROP SPECIFIC FUNCTION is the safe form when the SQL name is overloaded.

1
2
DROP SPECIFIC FUNCTION PAYROLL.TAXRATE1;
DROP FUNCTION HR.FULL_NAME (VARCHAR(12), VARCHAR(15));

Nesting and recursion

A function can invoke other functions. That is nesting. Keep SQL access (NO SQL vs READS SQL DATA vs MODIFIES SQL DATA) consistent: a NO SQL function cannot call a function that reads SQL data. Nested UDFs that modify data participate in the same unit of work as the caller.

Recursion is a function calling itself (directly or through a cycle). Compiled SQL scalar functions with SQL PL can be written recursively. Inlined functions are expressions; they do not recurse as procedures. Recursion needs a terminating condition and a realistic depth—unbounded recursion burns CPU and stack in the WLM or Db2 engine.

START FUNCTION and STOP FUNCTION

These are operator commands, not SQL:

• -STOP FUNCTION SPECIFIC — Db2 stops accepting SQL that invokes the named external functions. Built-in functions and sourced functions cannot be stopped this way
• -START FUNCTION SPECIFIC — starts a stopped external function, drains the queue, and resets abend counts. New functions do not need a START; first invocation starts them

1
2
3
4
-STOP FUNCTION SPECIFIC(PAYROLL.TAXRATE1)
-START FUNCTION SPECIFIC(PAYROLL.TAXRATE1,PAYROLL.USERFN2)
-START FUNCTION SPECIFIC(PAYROLL.TAX*)
-START FUNCTION SPECIFIC(*.*) SCOPE(GROUP)

Names are schema.specific-name. Wildcards: *.* for all functions, or schema.prefix* for a set. SCOPE(LOCAL) is the current member; SCOPE(GROUP) is the data sharing group. Query SYSIBM.SYSROUTINES if you did not assign SPECIFIC on CREATE.

Explain It Like I'm Five

A user-defined function is a recipe you write and give a name. When SQL says “use FULL_NAME,” Db2 follows your recipe instead of a built-in one. Sometimes the recipe is a tiny sticky note (inlined SQL) stuck onto the query. Sometimes it is a whole cookbook chapter with page numbers (a compiled package). Sometimes it is “ask the COBOL program in the next room” (external). Sometimes it is “do the same thing PLUS already does, but for my special money type” (sourced). If two recipes share a name, Db2 looks at the ingredients (arguments) and the list of kitchens to search (the SQL path). Operators can pause a recipe with STOP and start it again with START, using the recipe’s unique library code (specific name), not the everyday nickname.

Exercises

1. Write CREATE FUNCTION for an inlined SQL scalar that returns UPPER(last) concatenated with a comma and first name. Mark it DETERMINISTIC and NO EXTERNAL ACTION.
2. Explain why CREATE FUNCTION ... SOURCE is the usual way to allow MONEY + MONEY after CREATE TYPE MONEY AS DECIMAL(9,2).
3. Look up a function in SYSIBM.SYSROUTINES and find NAME versus SPECIFICNAME. Which one would you put on -STOP FUNCTION SPECIFIC?
4. Decide whether an external table function that reads a sequential file should be DETERMINISTIC, and whether it needs a SCRATCHPAD.
5. Describe how CURRENT PATH would make HR.TAX_RATE win over PAYROLL.TAX_RATE for an unqualified TAX_RATE(DECIMAL) call.