Create a DB2 UDF

Choose the UDF shape

Scalar functions return one value and fit expressions such as SELECT SALES.NORMALIZE_PHONE(PHONE). SQL scalar functions use SQL PL; external scalar functions call compiled code. Table functions return rows: SQL table functions return a query expression that Db2 can inline, while external table functions return rows from a program.

Sourced functions expose an existing built-in, SQL, or external function with a new name or distinct-type signature. They inherit the underlying function attributes and do not have their own package. Pick the narrowest form that expresses the requirement; do not create a UDF merely to hide a simple built-in function.

Prerequisites and design

Confirm CREATEIN on the target schema, EXECUTE governance, parameter and return data types, null behavior, SQL-data access classification, determinism, and owner. Deterministic means the same arguments give the same result; do not claim it for a function that reads changing tables, current time, or external state.

UDFs can run many times in a query. A function in a predicate or SELECT list can become expensive over millions of rows, especially if it issues SQL or crosses into external code. Use EXPLAIN and representative volume before putting a UDF on a high-frequency path.

Create a SQL scalar function

SQL scalar UDFs encapsulate a repeatable calculation. Declare RETURNS to match the output exactly, choose CALLED ON NULL INPUT or appropriate null behavior, and use an SQL body simple enough for maintainers to reason about.

1
2
3
4
5
6
7
8
CREATE FUNCTION SALES.NET_AMOUNT
  (P_GROSS DECIMAL(11,2), P_DISCOUNT DECIMAL(5,2))
  RETURNS DECIMAL(11,2)
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  RETURN P_GROSS - (P_GROSS * P_DISCOUNT / 100);

Create table and external functions

An SQL table function uses RETURNS TABLE and a RETURN SELECT expression. Db2 can inline that expression into the caller query, so its access path is visible to the optimizer. Test it in FROM TABLE(function(...)) and inspect the resulting plan.

External scalar and table functions identify compiled code with EXTERNAL NAME and specify LANGUAGE and PARAMETER STYLE. The load module need not exist at CREATE time but must be reachable when invoked. Use WLM and package planning appropriate to your external-routine model.

1
2
3
4
5
6
7
8
CREATE FUNCTION SALES.OPEN_ORDERS(P_CUSTOMER BIGINT)
  RETURNS TABLE (ORDER_ID BIGINT, AMOUNT DECIMAL(11,2))
  LANGUAGE SQL
  READS SQL DATA
  RETURN SELECT ORDER_ID, AMOUNT
           FROM SALES.ORDERS
          WHERE CUSTOMER_ID = P_CUSTOMER
            AND STATUS = 'OPEN';

Verify and grant

Verify with a direct VALUES function call for scalar functions or SELECT FROM TABLE for table functions. Test null input, boundary values, duplicate names/signatures, and authorization from the real application role. Catalog queries show the definition, but executable tests show whether the signature and privileges work.

Grant EXECUTE narrowly. Version SQL text with the application, record dependencies, and use DROP/CREATE or supported replace/change procedures carefully. A UDF used by views, triggers, or packages can create an outage if its return type changes.

Common errors

CREATE failures often come from missing schema authority, duplicate signatures, incompatible return types, invalid SQL data-access clauses, or a body that violates SQL PL rules. Invocation errors often mean a wrong argument type, missing EXECUTE, or unexpected null semantics.

Performance problems come from non-sargable predicates, row-by-row external calls, incorrect cardinality expectations, or marking a changing function DETERMINISTIC. Start with EXPLAIN and query metrics, then simplify the function or move logic into a set-based query.