Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: SQL0801 - Division by zero was attempted

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dollar489
    It worked fine on FP5. Any idea?
    Have your data changed since then, by any chance?

  4. #4
    Join Date
    Sep 2002
    Posts
    456
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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...

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    n_i,
    I think division by null should not return division by zero error. Formula: X / null returns: null.
    Grofaty

  7. #7
    Join Date
    Jun 2009
    Posts
    14

    Yup I am totally with you

    "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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •