External functions
An external user-defined function is a function that is written in a programming language. An external function is defined to the database with a reference to an external program that contains the logic that is executed when the function is invoked.
An external user-defined function that returns a single value is a scalar function. An external user-defined function that returns a table is a table function.
You can write an external user-defined function in assembler, C, C++, COBOL, PL/I, or Java™. User-defined functions that are written in COBOL can include object-oriented extensions, just as other DB2® COBOL programs can. User-defined functions that are written in Java follow coding guidelines and restrictions specific to Java. For information about writing Java user-defined functions, see Java stored procedures and user-defined functions.
Examples
CREATE FUNCTION FINDSTRING (CLOB(500K), VARCHAR(200))
RETURNS INTEGER
CAST FROM FLOAT
SPECIFIC FINDSTRINGCLOB
EXTERNAL NAME 'FINDSTR'
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION;
The function returns a status code as an integer. The CAST FROM clause is specified because the function operation results in a floating point value, and users are expecting an integer result for their SQL statements. The user-defined function is written in C and contains no SQL statements.
Suppose that you want a FINDSTRING user-defined function to work on BLOB data types, as well as CLOB types. You can define another instance of a FINDSTRING user-defined function that specifies a BLOB type as input:
CREATE FUNCTION FINDSTRING (BLOB(500K), VARCHAR(200))
RETURNS INTEGER
CAST FROM FLOAT
SPECIFIC FINDSTRINGBLOB
EXTERNAL NAME 'FNDBLOB'
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
DETERMINISTIC;
Each instance of FINDSTRING uses a different application program to implement the logic for the user-defined function.
UPDATE TABLE1 SET INTCOL1="/"(INTCOL2,INTCOL3);
CREATE FUNCTION MATH."/" (INT, INT)
RETURNS INTEGER
SPECIFIC DIVIDE
EXTERNAL NAME 'DIVIDE'
LANGUAGE ASSEMBLE
PARAMETER STYLE SQL
NO SQL
DETERMINISTIC;
- A character string of maximum length 30 that describes a subject
- A character string of maximum length 255 that contains text to search for
The user-defined function scans documents on the subject for the search string and returns a list of documents that match the search criteria, with an abstract for each document. The list is in the form of a two-column table. The first column is a character column of length 16 that contains document IDs. The second column is a varying-character column of maximum length 5000 that contains document abstracts.
The user-defined function is written in COBOL, uses SQL only to perform queries, and always produces the same output for given input. The CARDINALITY option specifies that you should expect an invocation of the user-defined function to return about 20 rows.
The following CREATE FUNCTION statement defines the user-defined function:
CREATE FUNCTION DOCMATCH (VARCHAR(30), VARCHAR(255))
RETURNS TABLE (DOC_ID CHAR(16), DOC_ABSTRACT VARCHAR(5000))
EXTERNAL NAME 'DOCMTCH'
LANGUAGE COBOL
PARAMETER STYLE SQL
READS SQL DATA
DETERMINISTIC
CARDINALITY 20;