Checking how DB2 resolves functions by using DSN_FUNCTION_TABLE

Because multiple user-defined functions can have the same name, you should ensure that DB2® invokes the function that you intended to invoke. One way to check that the correct function was invoked is to use a function table called DSN_FUNCTION_TABLE.

Procedure

To check how DB2 resolves a function by using DSN_FUNCTION_TABLE:

  1. If your_userID.DSN_FUNCTION_TABLE does not already exist, create this table by following the instructions in DSN_FUNCTION_TABLE.
  2. Populate your_userID.DSN_FUNCTION_TABLE with information about which functions are invoked by a particular SQL statement by performing one of the following actions:
    • Execute the EXPLAIN statement on the SQL statement.
    • Ensure that the program that contains the SQL statement is bound with EXPLAIN(YES) and run the program.
    DB2 puts a row in your_userID.DSN_FUNCTION_TABLE for each function that is referenced in each SQL statement.
  3. Check the rows that were added to your_userID.DSN_FUNCTION_TABLE to ensure that the appropriate function was invoked. Use the following columns to help you find applicable rows: QUERYNO, APPLNAME, PROGNAM, COLLID, and EXPLAIN_TIME.