Table function considerations

 

An external table function is a user-defined function (UDF) that delivers a table to the SQL statement in which it is referenced. A table function reference is valid only in a FROM clause of a SELECT statement.

When using table functions, observe the following:

• Even though a table function delivers a table, the physical interface between DB2^® and the UDF is one-row-at-a-time. There are five types of calls made to a table function: OPEN, FETCH, CLOSE, FIRST, and FINAL. The existence of FIRST and FINAL calls depends on how you define the UDF. The same call-type mechanism that can be used for scalar functions is used to distinguish these calls.

• The standard interface used between DB2 and user-defined scalar functions is extended to accommodate table functions. The SQL-result argument repeats for table functions; each instance corresponding to a column to be returned as defined in the RETURNS TABLE clause of the CREATE FUNCTION statement. The SQL-result-ind argument likewise repeats, each instance related to the corresponding SQL-result instance.

• Not every result column defined in the RETURNS clause of the CREATE FUNCTION statement for the table function has to be returned. The DBINFO keyword of CREATE FUNCTION, and corresponding dbinfo argument enable the optimization that only those columns needed for a particular table function reference need be returned.

• The individual column values returned conform in format to the values returned by scalar functions.

• The CREATE FUNCTION statement for a table function has a CARDINALITY n specification. This specification enables the definer to inform the DB2 optimizer of the approximate size of the result so that the optimizer can make better decisions when the function is referenced. Regardless of what has been specified as the CARDINALITY of a table function, exercise caution against writing a function with infinite cardinality; that is, a function that always returns a row on a FETCH call. DB2 expects the end-of-table condition, as a catalyst within its query processing. So a table function that never returns the end-of-table condition (SQL-state value '02000') will cause an infinite processing loop.

 

Parent topic:

Writing UDFs as external functions