DB2 CASE statement faster with parentheses around entire WHEN Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc:

CASE 
  WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM 
    AND RECTYPE = 'RO' 
    THEN 1 
    ELSE 0 
END AS MYRESULT


runs ~10 seconds slower than having this:

CASE 
  WHEN (MYCOL BETWEEN @STARTNUM AND @ENDNUM 
    AND RECTYPE = 'RO') 
    THEN 1 
    ELSE 0 
END AS MYRESULT


The only difference is the parentheses around the entire WHEN clause. We're at a loss as to what is going on. This with DB2 on an iSeries.

What could be causing this performance difference, and how can we take advantage of it to speed things up? As a programmer, those extra parentheses mean nothing. As a DbA, what could the optimizer be doing here to have such a drastic effect on performance?



Edit: Here's the query; it's pretty nasty, but that's the data we have to work with. I've run just a single sub-select (before the union) and still had a significant performance difference, so the union and final select aren't causing the bulk of the performance hit.
I realize some of the date manipulation can be fixed to remove the function calls; someone will get to that eventually.

SELECT
ID ,
ABBREVIATION ,
METRICS.NAME ,
METRICS.TYPE AS TYPE_ID ,
JOBTYPE.NAME AS TYPE_NAME ,
SUM ( CREATEDORDUE ) AS CREATEDORDUE ,
SUM ( COMPLETED ) AS COMPLETED ,
SUM ( COMPLETEDINSIDE ) AS COMPLETEDINSIDE ,
SUM ( CASE WHEN DAYS &lt;= 7 THEN 1 ELSE 0 END ) AS ONTIME ,
SUM ( CASE WHEN DAYS &gt; 7 AND DAYS &lt;= 14 THEN 1 ELSE 0 END ) AS ONEWEEKLATE ,
SUM ( CASE WHEN DAYS &gt; 14 AND DAYS &lt;= 21 THEN 1 ELSE 0 END ) AS TWOWEEKSLATE ,
SUM ( CASE WHEN DAYS &gt; 21 THEN 1 ELSE 0 END ) AS MORETHANTWOWEEKSLATE

FROM (

-- Active File -- 

SELECT EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JBCTL ,
JBWKA AS TYPE ,
CASE 
    WHEN JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1 
    ELSE 0 
END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE 
    WHEN ( JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JBDTC = 'RO' ) THEN 1
    ELSE 0 
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE ( 
    CASE WHEN JBCDT = 0 THEN @ENDDATENUM WHEN JBCDT &gt; @ENDDATENUM THEN @ENDDATENUM ELSE JBCDT END , 
    CAST ( MASTER.DATETOCYMDDATE ( DATE ( JBCRT ) ) AS DEC ( 7 , 0 ) ) , 
    CAST ( 'DAYS  ' AS CHAR ( 6 ) ) 
    ) AS DAYS

FROM EMJOB
JOIN EMEQM ON JBUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM --CSVTABLE function separates a csv list into a usable table
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE JBSYS = 'PM'
AND ( JBCDT &gt;= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JBCDT = 0 )
AND JBCRT &lt;= @ENDDATE 
AND JBCRT &gt; '2012-01-01-00.00.00.000000'  -- bad data before 2012
AND JBSTS &lt;&gt; 'D'

UNION

-- History File -- 

SELECT
EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JHCTL ,
JHWKA AS TYPE ,
CASE WHEN JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1 ELSE 0 END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE 
    WHEN ( JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JHDTC = 'RO' ) THEN 1 
    ELSE 0 
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE ( 
    CASE WHEN JHCDT = 0 THEN @ENDDATENUM WHEN JHCDT &gt; @ENDDATENUM THEN @ENDDATENUM ELSE JHCDT END , 
    CAST ( MASTER.DATETOCYMDDATE ( DATE ( JHCRT ) ) AS DEC ( 7 , 0 ) ) , 
    CAST ( 'DAYS  ' AS CHAR ( 6 ) ) 
    ) AS DAYS

FROM EMJOBH
JOIN EMEQM ON JHUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE
JHSYS = 'PM'
AND ( JHCDT &gt;= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JHCDT = 0 )
AND JHCRT &lt;= @ENDDATE 
AND JHCRT &gt; '2012-01-01-00.00.00.000000'
AND JHSTS &lt;&gt; 'D'

)
AS METRICS
JOIN JOBTYPES AS JOBTYPE
    ON METRICS.TYPE = JOBTYPE.TYPE
GROUP BY METRICS.ID , METRICS.ABBREVIATION , METRICS.NAME , METRICS.TYPE , JOBTYPE.NAME ;
In situations where one static sql (procedure) executes better than an equivalent one, I would examine:

a) whether this depends on the order of execution, i.e. does the second one benefit from the first one reading data from disk into the bufferpool? If one runs better than the other regardless of the order they are run in this is not the case.

b) are the plans equal? If one procedure is compiled with one set of statistics, and the other compiled with another set of statistics this could result in different plans. Try rebinding (or recreating) both procedures with the same statistics and see if they still differ