The following code in SP stopped working when database upgrade to FP11 happened:

IF(IPI_LOW_BRNG_TMP <> 0) THEN SET LD_TEMP_RATIO = (IPI_HIGH_BRNG_TMP * 1.0)/(IPI_LOW_BRNG_TMP * 1.0)

It worked fine on FP5. Any idea?

Dollar

Hi,
if the code was working fine on fixpack 5 and after upgrade to fixpack 11 it doesn't work any more and you have NOT changed the code at all, you need to report PMR to IBM.
Hope this helps,
Grofaty

Have your data changed since then, by any chance?

Data has not changed. As a matter of fact our development environment is still at FP5 and it works fine there. Seems like optimizer is not going by the order of query but trying division by zero first.

I'm wondering if IPI_LOW_BRNG_TMP may be null in your case. It wouldn't be equal to 0 then but might still generate a division by zero exception...

n_i,
I think division by null should not return division by zero error. Formula: X / null returns: null.
Grofaty

"e. Seems like optimizer is not going by the order of query but trying division by zero first.
Reply With Quote"

I am using DB2 UDB, and i can totally tell you that the optimizer of DB2 is thinking way too much for my liking.

Here is a function that results in a similar DB2 error:

-------------------CODE EXAMPLE -------------------
DROP FUNCTION calculate_vcount@
CREATE FUNCTION calculate_vcount (inbetweendate date, nmonths int, nviews int)
RETURNS int
fbody: BEGIN ATOMIC
DECLARE stdvcount, lastvcount int;

IF nmonths <= 0 or inbetweendate > current date
THEN
RETURN 0;
END IF;

SET stdvcount = nviews/nmonths;
IF YEAR(inbetweendate) = YEAR(current date) and MONTH(inbetweendate) = MONTH(current date)
THEN
SET lastvcount = stdvcount + MOD(nviews,nmonths);
RETURN lastvcount;
END IF;
RETURN stdvcount;
END@
--------------------------

This function starts by checking if a date is bigger than it should be when it is used or if i have got a 0 input variable.
So i make sure i never divide by zero.
No matter, if the input to the function is Zero the damn DB2 evaluator summons up the conclusion that the division by zero will happen.
I have tried sorround the division statement inside of an IF clause but the result keeps on chaning.

So the only way this will not be a problem is if you make the if statement before perfoming the function invoking.

--1) USE AN INVALID DATE (expected result 0)
SELECT calculate_vcount(current date + 1 day, 0, 100)
FROM PROFILE@

This test fails no matter how well you try to program the function. You can not have a function have a division where the denomitor is zero, even if it is never called. IT is ridiculous but it seems to work that way.

Something similar happens with DB2 recursive functions. The optimizer tries to act smart and warns me that a recursive function that WILL never enter in an infinite loop may do so. Sometimes the people that code the DB2 should try to be less smart and more efficient.

... But well, whatever.