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 7 AND DAYS 14 AND DAYS 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 > @ENDDATEN

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

StackExchange Database Administrators Q#98616, answer score: 4