The following creates a table function written to return a row consisting of a single document identifier column for each known document in a text management system. The first parameter matches a given subject area and the second parameter contains a given string.
Within the context of a single session, the UDF will always return the same table, and therefore it is defined as DETERMINISTIC. Note the RETURNS clause which defines the output from DOCMATCH. FINAL CALL must be specified for each table function. In addition, the DISALLOW PARALLEL keyword is added as table functions cannot operate in parallel. Although the size of the output for DOCMATCH is highly variable, CARDINALITY 20 is a representative value, and is specified to help the optimizer.
CREATE FUNCTION DOCMATCH (VARCHAR(30), VARCHAR(255)) RETURNS TABLE (DOCID CHAR(16)) EXTERNAL NAME 'MYLIB/RAJIV(UDFMATCH)' LANGUAGE C PARAMETER STYLE DB2SQL NO SQL DETERMINISTIC NO EXTERNAL ACTION NOT FENCED SCRATCHPAD FINAL CALL DISALLOW PARALLEL CARDINALITY 20
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.