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.
"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)
fbody: BEGIN ATOMIC
DECLARE stdvcount, lastvcount int;
IF nmonths <= 0 or inbetweendate > current date
SET stdvcount = nviews/nmonths;
IF YEAR(inbetweendate) = YEAR(current date) and MONTH(inbetweendate) = MONTH(current date)
SET lastvcount = stdvcount + MOD(nviews,nmonths);
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)
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.