Those are good ideas I’ll examine those approaches. I’ve been focused on identifying the settings that allow the sql statement to execute in a few seconds while the same statement require between 4 – 10 minutes to return results when wrapped in a stored procedure.
Thanks for your suggestions.

I was too quick to celebrate. After recompiling all of my UDFs and SPs with the new settings I called the main store procedure, but forgot to uncomment all of the lines with references to the UDFs before recompiling. I’m back to scratching my head. The solution I implemented had no effect. To summarize, my stored procedure runs very slow; when I take the sql statement out of the sp and run it directly in iNavigator the result set is generated...

I recompiled all of my user defined functions and stored procedures with the following settings: READ SQL DATA, DETERMINISTIC, and NOT FENCED. DETERMINISTIC directs DB2 to store the results of a UDF, it will return this same result without re-executing the UDF whenever the function is executed with same parameter as previously used during same process. NOT FENCED, used in the iSeries environment, directs DB2 to execute all called functions and procedures...

sp code with static values

-- Generate SQL
-- Version: V5R4M0 060210
-- Generated on: 08/30/10 11:21:30
-- Relational Database: CAPM01
-- Standards Option: DB2 UDB iSeries


CREATE PROCEDURE SHIPPED_VS_ALLO CATE_TEST3 (
IN BEGIN_DATE DATE ,
IN END_DATE DATE ,
IN REPORTTYPE CHAR(1) )
DYNAMIC...

I have a sql stored procedure that wraps a query that joins 2 tables and uses several UDFs that call other stored procedures. The performance of the procedure is slow,
it takes about 5 min to complete.
To troubleshoot the problem, I copied the query into iNavigator 6 (db2 iSeries v5R4). I substituted the parameters with static values. This query returns results in 5 - 10 seconds. I now replaced the parameters in the sp with static...