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.