Table function node

A table function node represents a table function that is invoked by the SQL statement.

A table function is a user-defined function that returns a table. This function is specified in the FROM clause of an SQL statement. An SQL statement can invoke the same table function multiple times; each invocation is represented by a table function node.

A table function node is labeled with the name of the table function and is, by default, displayed as a hexagon.

How DB2 accesses a table function

DB2® can use a table space scan (TBSCAN) to access a table function. Sometimes DB2 places the result of a table function in a work file and then uses a work file scan (WFSCAN) to access the work file.

The DB2 optimizer uses the table function statistics that are listed in the table below to determine the access plan. These statistics are listed as attributes on the Descriptor window for a table function node. You can specify these attributes by updating the catalog table SYSIBM.SYSROUTINES. You can also specify the value for the cardinality attribute when you define the table function using the CREATE FUNCTION statement. If you do not specify these attributes, DB2 uses the default values that are listed in the following table.
Table 1. Table function statistics that the optimizer uses
Attribute Explanation Default value
Cardinality The function's predicted cardinality 10 000
Initial CPU Cost Estimated number of instructions that are executed the first time and last time that the function is invoked 40 000
Initial IO Cost Estimated number of inputs and outputs that are performed the first time and last time the function is invoked 0
CPU Cost Estimated number of instructions per invocation 4 000
IO Cost Estimated number of inputs and outputs per invocation 0

Feedback