Function Resolution and the DSN_FUNCTION_TABLE

< Day Day Up >

Function Resolution and the `DSN_FUNCTION_TABLE`

In addition to cost estimates and access paths, EXPLAIN also can populate function resolution information. Simply by defining an appropriate DSN_FUNCTION_TABLE , also known as the function table , EXPLAIN will populate that function table with information about the UDFs used during the plan, package, or SQL statement. Refer to Listing 25.3 for DSN_FUNCTION_TABLE DDL.

Listing 25.3. DDL to Create the `DSN_FUNCTION_TABLE`

 CREATE TABLE  userid  .DSN_FUNCTION_TABLE   (QUERYNO        INTEGER      NOT NULL WITH DEFAULT,    QBLOCKNO       INTEGER      NOT NULL WITH DEFAULT,    APPLNAME       CHAR(8)      NOT NULL WITH DEFAULT,    PROGNAME       CHAR(128)    NOT NULL WITH DEFAULT,    COLLID         CHAR(128)    NOT NULL WITH DEFAULT,    GROUP_MEMBER   CHAR(8)      NOT NULL WITH DEFAULT,    EXPLAIN_TIME   TIMESTAMP    NOT NULL WITH DEFAULT,    SCHEMA_NAME    CHAR(128)    NOT NULL WITH DEFAULT,    FUNCTION_NAME  CHAR(128)    NOT NULL WITH DEFAULT,    SPEC_FUNC_NAME CHAR(128)    NOT NULL WITH DEFAULT,    FUNCTION_TYPE  CHAR(2)      NOT NULL WITH DEFAULT,    VIEW_CREATOR   CHAR(128)    NOT NULL WITH DEFAULT,    VIEW_NAME      CHAR(128)    NOT NULL WITH DEFAULT,    PATH           VARCHAR(254) NOT NULL WITH DEFAULT,    FUNCTION_TEXT  VARCHAR(254) NOT NULL WITH DEFAULT   ) IN  database.tablespace  ;

When a function is invoked in an SQL statement, DB2 must choose the correct function to run to satisfy the request. DB2 will check for candidate functions to satisfy the function request. The manner in which DB2 chooses which function to run is documented in Chapter 4, "Using DB2 User -Defined Functions and Data Types."

Changes for DB2 Version 8

For DB2 V8, several columns were extended to support long names , as follows :

PROGNAME was modified from CHAR(8) to VARCHAR(128) .
COLLID was modified from CHAR(18) to VARCHAR(128) .
SCHEMA_NAME was modified from CHAR(8) to VARCHAR(128) .
FUNCTION_NAME was modified from CHAR(18) to VARCHAR(128) .
SPEC_FUNC_NAME was modified from CHAR(18) to VARCHAR(128) .
VIEW_CREATOR was modified from CHAR(8) to VARCHAR(128) .
VIEW_NAME was modified from CHAR(18) to VARCHAR(128) .

Additionally, two columns were extended in size for DB2 V8. The PATH column was extended from VARCHAR(254) to VARCHAR(2048) , and the FUNCTION_TEXT column was extended from VARCHAR(254) to VARCHAR(1500) .

The `DSN_FUNCTION_TABLE` Columns

A description and definition of the DSN_FUNCTION_TABLE columns is provided in Chapter 4. Please refer to that chapter for the detail.

< Day Day Up >