Developing SQL User Defined Functions

To develop a SQL User Defined Functions (UDF) in Db2 for z/OS:
  • Use a .udfsql file extension for UDF.
  • This file should include a single CREATE FUNCTION statement that defines your UDF.
  • Enter a CREATE FUNCTION statement.
  • Use syntax validation in the SQL editor to check for issues before deploying.
  • To quickly start with a template, use the createFunctionTable: CREATE FUNCTION (UDF) statement
Example of SQL User-Defined Function:
CREATE FUNCTION ADMF001.REVERSE(INSTR VARCHAR(4000))
    RETURNS VARCHAR(4000)
    VERSION V1
    DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
    BEGIN
    DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
    DECLARE LEN INT;
    IF INSTR IS NULL THEN
    RETURN NULL;
    END IF;
    SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
    WHILE LEN > 0 DO
    SET (REVSTR, RESTSTR, LEN) 
        = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, 
        SUBSTR(RESTSTR, 2, LEN - 1),
        LEN - 1);
    END WHILE;
    RETURN REVSTR;
END
Note: The SQL Editor supports templates for creating SQL UDF. Start typing CREATE FUNCTION and trigger content assist to see the templates.

For more information, see Creation of user-defined functions in the Db2 for z/OS documentation.