Providing cost information, for accessing user-defined table functions, to Db2

User-defined table functions add additional access cost to the execution of an SQL statement.

About this task

Begin program-specific programming interface information.For Db2 to factor in the effect of user-defined table functions in the selection of the best access path for an SQL statement, the total cost of the user-defined table function must be determined.

The total cost of a table function consists of the following three components:

  • The initialization cost that results from the first call processing
  • The cost that is associated with acquiring a single row
  • The final call cost that performs the clean up processing

These costs, though, are not known to Db2 when I/O costs are added to the CPU cost.

Procedure

To assist Db2 in determining the cost of user-defined table functions:

Use the following fields in SYSIBM.SYSROUTINES catalog table:
IOS_PER_INVOC
The estimated number of I/Os per row.
INSTS_PER_INVOC
The estimated number of instructions.
INITIAL_IOS
The estimated number of I/Os performed the first and last time the function is invoked.
INITIAL_INSTS
The estimated number of instructions for the first and last time the function is invoked.

These values, along with the CARDINALITY value of the table being accessed, are used by Db2 to determine the cost. The results of the calculations can influence such things as the join sequence for a multi-table join and the cost estimates generated for and used in predictive governing.

You can determine values for the four fields by examining the source code for the table function:

  1. Estimate the I/Os by examining the code executed during the FIRST call and FINAL call.
  2. Look for the code executed during the OPEN, FETCH, and CLOSE calls.
  3. The costs for the OPEN and CLOSE calls can be amortized over the expected number of rows returned.
  4. Estimate the I/O cost by providing the number of I/Os that are issued. Include the I/Os for any file access.
  5. Calculate the instruction cost by counting the number of high level instructions executed in the user-defined table function and multiplying it by a factor of 20. For assembler programs, the instruction cost is the number of assembler instructions.

Example

The following statement shows how these fields can be updated. The authority to update is the same authority as that required to update any catalog statistics column.

UPDATE SYSIBM.SYSROUTINES SET
   IOS_PER_INVOC   = 0.0,
   INSTS_PER_INVOC = 4.5E3,
   INITIAL_IOS     = 2.0
   INITIAL_INSTS   = 1.0E4,
   CARDINALITY     = 5E3
WHERE
   SCHEMA = 'SYSADM' AND
   SPECIFICNAME = 'FUNCTION1' AND
   ROUTINETYPE = 'F';
End program-specific programming interface information.