Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: exponential value

    Could you please explain why the value is not stored/displayed correctly?
    Last edited by db2girl; 11-30-11 at 17:56.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Floating point numbers (FLOAT and DOUBLE) are approximate. In the second example you convert the BIGINT literal twice - from BIGINT to DOUBLE, then from DOUBLE to DECIMAL, so I'd think some loss of precision might be expected.

    It would be good to know what the customer think are "correct" and "incorrect" results.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    In this example, customer thinks scale !=0 is incorrect:

    "Correct" result:
    444444444444444.00

    "Incorrect" results:
    444444444444444.50
    444444444444444.10


    In their case, the column is defined as dec (31,2) - 1st example.


    I'm attaching another doc with some additional info provided by the customer.

    Do you think what they're seeing is expected (but zOS results are different from LUW), db2 bug, jdbc bug...?
    Last edited by db2girl; 11-30-11 at 17:55.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    In this example, customer thinks scale !=0 is incorrect:

    "Correct" result:
    444444444444444.00

    "Incorrect" results:
    444444444444444.50
    444444444444444.10


    In their case, the column is defined as dec (31,2) - 1st example.

    I might be wrong as I've had a couple of beers now, but the FLOAT literal 4.4444444444444444E14 seems to be the same as 444444444444444 plus something around 0.44 (floating point numbers are approximate), which nicely fits into DECIMAL(31,2), so 444444444444444.00 would definitely be incorrect, z/OS or not.

    Now, JDBC Double supports only 15 digits for the mantissa, which does not mean that it would truncate the extra digits - rather, if you supply more than 15 digits for the mantissa (which they do) only the first 15 will be defined.

    Can your customer explain why they think 444444444444444.00 is the correct decimal representation of 444444444444444.44?

    Normally you would never use floating point data type for currency (money) variables - only decimals.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You're correct - 4.4444444444444444E14 is 444444444444444.44 so I'm not sure why they think 444444444444444.00 is correct.


    Using their second example of inserting
    444444444444444.00


    Why does select return?
    444444444444444.10 - LUW
    444444444444444.00 - zOS


    I'm not sure I completely understand about "JDBC Double supports only 15 digits for the mantissa..."

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Mantissa holds the significant digits of a number, so if you try to store in a Double something that needs more than 15 digits, it will be approximated to a number that has 15 significant digits. "4.4444444444444444E14" has 17 significant digits, and if you were to assign it to a Double, it would turn into ".444444444444444E15" (15 digits).

    As for the difference between the LUW and z/OS representation of the DOUBLE 444444444444444.00, I guess it might be attributed to the different floating point implementation that depends on the hardware; both numbers are approximately the same, and that is correct by the definition of the DOUBLE: "A double-precision floating-point number is a 64-bit approximation of a real number. "

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Thanks a lot, Nick. Very helpful.

Posting Permissions

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