Slow running sql stored procedure

[tomaszp]

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 values; the procedure still takes about 5 minutes to return results.
Why does the sp run so slow? What should I modify to improve performance ot this sql sp

[Oralloy]

Is the only thing in the stored procedure your query?

It would help a lot, if you'd post the code.

Cheers!

[tomaszp]

-- 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_ALLOCATE_TEST3 (
IN BEGIN_DATE DATE ,
IN END_DATE DATE ,
IN REPORTTYPE CHAR(1) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC SHIPPED_VS_ALLOCATE_TEST3
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN


DECLARE BDATE DECIMAL ( 8 , 0 ) ;
DECLARE EDATE DECIMAL ( 8 , 0 ) ;
DECLARE SQL_SMT1 CHAR ( 2000 ) ;
DECLARE SQL_SMT2 CHAR ( 2000 ) ;
DECLARE SQL_SMT CHAR ( 5000 ) ;

DECLARE C1 CURSOR WITH RETURN FOR
SELECT PHPCTL "Pickticket #" , SUBSTR ( PDCREX , 1 , 35 ) "SAP Order #" , PDPKLN "Line #" , PDSTYL || PDSSFX || PDCOLR "SKU"
,
UDF_NUMBEROFHOLIDAYDAYS ( PHPDCR ) "Holidays" ,
UDF_DECIMALTODATE ( PHPDCR ) "CREATED DATE" ,
UDF_DECIMALTOTIME ( PHPTCR ) "CREATED TIME" ,
UDF_DECIMALTODATE ( PHSHDT ) "SHIP DATE" ,
UDF_DECIMALTOTIME ( PHPTLM ) "SHIP TIME",
UDF_DECIMALTOTIME ( 183000 ) "CutOff Time" ,
PHSOCN "Country" ,
PDPIQT , PDMIS5 || PDMIS6 "Seibel#" , PHORDS "Condition" , PHSVIA "Ship Via" ,
PHPTCR , PDPAKU , PHPTLM ,
PHDIV , PHPSTF , PHPDCR , PHSHDT ,
UDF_DECIMALTODATE ( UDF_SHIPDATEDECIMAL ( PHPDCR , PHPTCR , PHSOCN , CHAR ( 'E' ) ) ) "Required Ship Date" ,
CASE
WHEN PHSHDT > UDF_SHIPDATEDECIMAL ( PHPDCR , PHPTCR , PHSOCN , CHAR ( 'E' ) ) THEN 'LATE'
WHEN PHSHDT = 0 AND CURRENT_DATE <= UDF_DECIMALTODATE ( UDF_SHIPDATEDECIMAL ( PHPDCR , PHPTCR , PHSOCN , CHAR ( 'E' ) ) ) THEN ''
WHEN PHSHDT = 0 AND CURRENT_DATE > UDF_DECIMALTODATE ( UDF_SHIPDATEDECIMAL ( PHPDCR , PHPTCR , PHSOCN , CHAR ( 'E' ) ) ) THEN 'LATE'
ELSE '' END "LATE STATUS"


FROM PhOrder00 INNER JOIN PdOrder00 ON ( ( PHCO = PDCO ) AND ( PHDIV = PDDIV ) ) AND ( PHPCTL = PDPCTL )
WHERE PHCO = 'ZZ' AND PHDIV = '999' AND PHPSTF < '99' AND ( PHPDCR >= 20100816 AND PHPDCR <= 20100817 ) AND PHORDS NOT IN ( '66' , '77' , '88' ) AND PDCO = 'ZZ' AND PDDIV = '999' ;

OPEN C1;

Return;


END ;

[Oralloy]

Ok, I don't see anything obvious, but a few small things. I am ignorant of a number of things with db2, so if I make a mistake or state something stupid, let me know, please.

One, the header states "MODIFIES SQL DATA". That may have some affect.

There are three declares for SQL_SMT variables. Unless these are necessary, they can be removed.

Have you tried temporarily removing "LATE STATUS" and/or "Required Ship Date" to see if they affect the query processing?

Did you double check that the join condition is on indexed columns?

[tomaszp]

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 in same thread. Since I’m calling several UDFs and stored procedures, these setting have made a big difference. Processing time has dropped to 30 seconds. This is slower than running this query in iNavigator, but it is acceptable. I don’t have the time to test each one of these settings individually to determine which setting contributes the most to the much improved processing time, but I will be playing with this more over time. Thanks for your response.

[Oralloy]

@tomasazp,

Thanks for letting us know how things turned out. I learned something.

Cheers!

[tomaszp]

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 in less than 10 seconds. When I take out all references to UDFs result set is returned as expected.
Any ideas?

[Oralloy]

@tomasazp,

Well, drat.

Unfortunately I am out of ideas. Hopefully I can pass the batton to one of the other experts here who is more qualified with DB2.

Is it possible to compute a preliminary result set, then walk that cursor and apply the UDFs?

Or maybe use a temporary table to hold the basic result and then return the UDF processed version of that table?

[tomaszp]

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.

[Oralloy]

@tomaszp,

When you get it figured out, please let me know.

Cheers!