Application Development Guide

Table Function Considerations

An external table function is a UDF which delivers a table to the SQL in which it is referenced. A table function reference is only valid in a FROM clause of a SELECT. 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 call 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. There are many situations where DB2 expects the end-of-table condition, as a catalyst within its query processing. Using GROUP BY or ORDER BY are examples where this is the case. DB2 cannot form the groups for aggregation until end-of-table is reached, and it cannot sort until it has all the data. So a table function that never returns the end-of-table condition (SQL-state value '02000') can cause an infinite processing loop if you use it with a GROUP BY or ORDER BY clause.