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

Example 1: Definition for an external user-defined scalar function: A programmer develops a user-defined function that searches for a string of maximum length 200 in a CLOB value whose maximum length is 500 KB. This CREATE FUNCTION statement defines the user-defined function:

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.

Example 2: Definition for an external user-defined scalar function: A programmer has written a user-defined function for division. That is, this user-defined function is invoked when an application program executes a statement using the division operator (/), such as the following statement:

UPDATE TABLE1 SET INTCOL1="/"(INTCOL2,INTCOL3);

The user-defined function takes two integer values as input. The output from the user-defined function is of type integer. The user-defined function is in the MATH schema, is written in assembler, and contains no SQL statements. This CREATE FUNCTION statement defines the user-defined function:

CREATE FUNCTION MATH."/" (INT, INT)
  RETURNS INTEGER
  SPECIFIC DIVIDE
  EXTERNAL NAME 'DIVIDE'
  LANGUAGE ASSEMBLE
  PARAMETER STYLE SQL
  NO SQL
  DETERMINISTIC;

Example 3: Definition for an external user-defined table function: An application programmer develops a user-defined function that receives two input values and returns a table. The two input values are:

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;