Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to get 15 decimal precision in SQL statement?

    Hi,
    using db2 10.1 fixpack 1 on Linux I am trying to divide two numbers. The first test produces correct values. All other tests reduces precision. Why? I would like to have 15 decimal places after each of calculation bellow.
    Is this data type conversion problem? How to write SQL to have exactly 15 decimal point precision?
    Thanks

    Code:
    values (dec(1,16,15) / dec(1.298314285714280,16,15))
    
    1
    ---------------------------------
                    0,770229528399466
    
    
    values (dec(1,17,15) / dec(1.298314285714280,17,15))
    
    1
    ---------------------------------
                     0,77022952839946
    
    
    
    values (dec(1,18,15) / dec(1.298314285714280,18,15))
    
    1
    ---------------------------------
                      0,7702295283994
    
    
    
    values (dec(1,19,15) / dec(1.298314285714280,19,15))
    
    1
    ---------------------------------
                       0,770229528399
    
    
    
    values (dec(1,20,15) / dec(1.298314285714280,20,15))
    
    1
    ---------------------------------
                        0,77022952839
    
    
    values (dec(1,30,15) / dec(1.298314285714280,30,15))
    
    1
    ---------------------------------
                                  0,7

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have a look at this article
    Expressions - IBM DB2 9.7 for Linux, UNIX, and Windows
    and at cast
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    @przytula_guy, I have looked at cast. As I understand the following have the same meaning:
    Code:
    values (dec(1.123456789012345, 16, 15) )
    values (cast (1.123456789012345 as dec(16, 15)) )
    and this does not solve my problem.

    What I am asking is why the following command always return 15 decimal points:
    Code:
    values ( dec(1.12345678901234567890, 16,15) );
    values ( dec(1.12345678901234567890, 17,15) );
    values ( dec(1.12345678901234567890, 18,15) );
    values ( dec(1.12345678901234567890, 19,15) );
    values ( dec(1.12345678901234567890, 20,15) );
    values ( dec(1.12345678901234567890, 21,15) );
    values ( dec(1.12345678901234567890, 22,15) );
    values ( dec(1.12345678901234567890, 23,15) );
    values ( dec(1.12345678901234567890, 24,15) );
    values ( dec(1.12345678901234567890, 25,15) );
    values ( dec(1.12345678901234567890, 26,15) );
    values ( dec(1.12345678901234567890, 27,15) );
    values ( dec(1.12345678901234567890, 28,15) );
    values ( dec(1.12345678901234567890, 29,15) );
    values ( dec(1.12345678901234567890, 30,15) );
    values ( dec(1.12345678901234567890, 31,15) );
    ... result:
    Code:
    1
    ------------------
     1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    -------------------
      1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    --------------------
       1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    ---------------------
        1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    ----------------------
         1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    -----------------------
          1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    ------------------------
           1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    -------------------------
            1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    --------------------------
             1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------
              1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    ----------------------------
               1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    -----------------------------
                1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    ------------------------------
                 1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    -------------------------------
                  1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    --------------------------------
                   1,123456789012345
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                    1,123456789012345
    
      1 record(s) selected.
    So all of the results have exactly 15 decimal points like expected.

    And why when dividing two numbers like:
    Code:
    values ( dec(1, 16,15) / dec(1.12345678901234567890, 16,15) );
    values ( dec(1, 17,15) / dec(1.12345678901234567890, 17,15) );
    values ( dec(1, 18,15) / dec(1.12345678901234567890, 18,15) );
    values ( dec(1, 19,15) / dec(1.12345678901234567890, 19,15) );
    values ( dec(1, 20,15) / dec(1.12345678901234567890, 20,15) );
    values ( dec(1, 21,15) / dec(1.12345678901234567890, 21,15) );
    values ( dec(1, 22,15) / dec(1.12345678901234567890, 22,15) );
    values ( dec(1, 23,15) / dec(1.12345678901234567890, 23,15) );
    values ( dec(1, 24,15) / dec(1.12345678901234567890, 24,15) );
    values ( dec(1, 25,15) / dec(1.12345678901234567890, 25,15) );
    values ( dec(1, 26,15) / dec(1.12345678901234567890, 26,15) );
    values ( dec(1, 27,15) / dec(1.12345678901234567890, 27,15) );
    values ( dec(1, 28,15) / dec(1.12345678901234567890, 28,15) );
    values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
    values ( dec(1, 30,15) / dec(1.12345678901234567890, 30,15) );
    values ( dec(1, 31,15) / dec(1.12345678901234567890, 31,15) );
    I get eats-up of the decimal points:
    Code:
    1
    ---------------------------------
                    0,890109890990219
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                     0,89010989099021
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                      0,8901098909902
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                       0,890109890990
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                        0,89010989099
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                         0,8901098909
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                          0,890109890
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                           0,89010989
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                            0,8901098
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                             0,890109
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                              0,89010
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                               0,8901
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                                0,890
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                                 0,89
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                                  0,8
    
      1 record(s) selected.
    
    
    
    1
    ---------------------------------
                                   0,
    
      1 record(s) selected.
    Decimal points gets eaten-up. I am expecting to have 15 decimal points.

    Thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    And why when dividing two numbers like:
    Code:
    ...
    values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
    ...
    I get eats-up of the decimal points:
    Code:
    ...
    
    1
    ---------------------------------
                                 0,89
    
      1 record(s) selected.
    
    ...
    Decimal points gets eaten-up. I am expecting to have 15 decimal points.
    Please read the aricle carefully.
    Quote Originally Posted by przytula_guy View Post
    You would see the following descriptions in that.
    Decimal arithmetic in SQL

    The following formulas define the precision and scale of the result of decimal operations in SQL.
    The symbols p and s denote the precision and scale of the first operand,
    and the symbols p' and s' denote the precision and scale of the second operand.

    Addition and subtraction
    ...

    Multiplication
    ...

    Division
    The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative.

    Note: ...
    ...
    For example:
    Code:
    values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
    ------------------------------------------------------------------------------
    
    1                                
    ---------------------------------
                                 0.89
    
      1 record(s) selected.
    Code:
    DESCRIBE
    values ( dec(1, 29,15) / dec(1.12345678901234567890, 29,15) );
    ------------------------------------------------------------------------------
    
     Column Information
    
     Number of columns: 1
    
     SQL type              Type length  Column name                     Name length
     --------------------  -----------  ------------------------------  -----------
     484   DECIMAL               31, 2  1                                         1
    p = 29, s = 15, p' = 29, s' = 15
    The precision of the result of division is 31.
    The scale is 31-p+s-s' = 31-29+15-15 = 2.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    I am astonished! Can't beleve that DB2 is working in this way. Is there any reason why? How to overcome this problem? I know I can make this calculation in my program, but is there any way to calculate this simple two numbers division in SQL and not getting some trunkated result?

    I compared DB2 10.1 on Linux:
    Code:
    SELECT
    CAST(1 AS DEC(29,15))
    /
    CAST(1.12345678901234567890 AS DEC(29,15))
    AS RESULT
    FROM test;
    
    RESULT
    ---------------------------------
                                 0,89
    with MySQL 5.5 on Linux:
    Code:
    SELECT
    CAST(1 AS DEC(29,15)) 
    / 
    CAST(1.123456789012345467890 AS DEC(29,15)) 
    AS RESULT
    FROM test;
    
    +-----------------------+
    | RESULT                |
    +-----------------------+
    | 0.8901098909902191115 |
    +-----------------------+
    with PostgreSQL 9.1 on Linux:
    Code:
    SELECT
    CAST(1 AS DEC(29,15))
    /
    CAST(1.12345678901234567890 AS DEC(29,15))
    AS RESULT
    FROM test;
             result        
    ------------------------
     0.89010989099021831919
    P.S. I thought this is a bug in DB2, but now it looks like a limitation of DB2.
    Regards
    Last edited by grofaty; 06-19-13 at 10:08.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    SELECT
    CAST(1 AS DEC(29,15))
    /
    CAST(1.12345678901234567890 AS DEC(29,15))
    AS RESULT
    FROM test;
    Why do you need to CAST(1 AS DEC(29,15))?
    Please try by using CAST(1 AS DEC(16,15)) or CAST(1 AS DEC(2,1)).

    I thought this is a bug in DB2, but now it looks like a limitation of DB2.
    I supposed the reason might be to keep enough place of integer part of the result(in other words, to avoid OVERFLOW),
    even if dividened was largest absolute value of that datatype and divisor was smallest absolute value of that datatype.

    For example: Though the dividened was not largest absolute value...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           CAST(-12345678901234.0 AS DEC(29,15))
           /
           CAST(0.000000000000001 AS DEC(29,15))
           AS RESULT
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    RESULT                           
    ---------------------------------
    -12345678901234000000000000000.00
    
      1 record(s) selected.
    Last edited by tonkuma; 06-20-13 at 06:31. Reason: Add "(in other words, to avoid OVERFLOW)"

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by grofaty View Post
    ...
    ...

    with MySQL 5.5 on Linux:
    Code:
    SELECT
    CAST(1 AS DEC(29,15)) 
    / 
    CAST(1.123456789012345467890 AS DEC(29,15)) 
    AS RESULT
    FROM test;
    
    +-----------------------+
    | RESULT                |
    +-----------------------+
    | 0.8901098909902191115 |
    +-----------------------+
    with PostgreSQL 9.1 on Linux:
    Code:
    SELECT
    CAST(1 AS DEC(29,15))
    /
    CAST(1.12345678901234567890 AS DEC(29,15))
    AS RESULT
    FROM test;
             result        
    ------------------------
     0.89010989099021831919
    P.S. I thought this is a bug in DB2, but now it looks like a limitation of DB2.
    Regards
    The result of MySQL 5.5 was accurate until 14 decimal places, like 0.8901098909902191115
    or, if exact result was rounded off to the 15th decimal place, the result of MySQL was accurate until 15 decimal places.
    The result of PostgreSQL 9.1 was accurate until 15 decimal places, like 0.89010989099021831919

    Compared with calculaion by DECFLOAT...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           CAST( 1 AS DECFLOAT)
           /
           CAST(1.12345678901234567890 AS DECFLOAT)
           AS RESULT
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    RESULT                                    
    ------------------------------------------
          0.8901098909902185735958205673806026
    
      1 record(s) selected.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by tonkuma View Post
    Compared with calculaion by DECFLOAT...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           CAST( 1 AS DECFLOAT)
           /
           CAST(1.12345678901234567890 AS DECFLOAT)
           AS RESULT
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    RESULT                                    
    ------------------------------------------
          0.8901098909902185735958205673806026
    
      1 record(s) selected.
    Your result is 100% correct. You can check this out with Windows Calculator and I get the same number. But executing your SQL and I get: +8,90109890990219E-001

    And now my one million dollar question is how did you convert number: +8,90109890990219E-001 to get: 0.8901098909902185735958205673806026?

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But executing your SQL and I get: +8,90109890990219E-001
    It is very strange, if you executed exactly same as my SQL staement.
    Because, I didn't use float datatype. I used DECFLOAT(or DECIMAL) datatype.
    So, result datatype must be DECFLOAT(or DECIMAL).

    I already showed DECFLOAT division returned DECFLOAT result.
    I didn't saw any float intermediate results.

    Although, I don't know DB2 inernal,
    I guessed that DB2 would use DECIMAL operations inside of DB2, for DECIMAL dataype.
    If underlying hardware didn't support DECIMAL machine instructions, DB2 would simulate them.
    So might be for DECFLOAT.

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by tonkuma View Post
    It is very strange, if you executed exactly same as my SQL staement.
    Now I have found out what is the problem. I was accessing DB2 v10 on Linux from my DB2 v8.2 Windows client and the result was as described a float. So it looks like some old client new server problem. I need this DB2 v8.2 client because newer versions of DB2 client is not compatible with one software that is must in my case.

    Now I have tried to access DB2 v10 server from DB2 v10 client and the result is as you described. So problem solved.

    Thanks a lot for help.
    Last edited by grofaty; 06-24-13 at 09:37.

Posting Permissions

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