Data Replication 2
Results 1 to 5 of 5

Thread: calculations under attunity are returning biased results

  1. #1
    CedrikBoussier is offline Junior Member
    Join Date
    Apr 2010
    Age
    36
    Posts
    7
    Rep Power
    0

    calculations under attunity are returning biased results

    Hi, is there a way to get rid of these number format problems ? I am using dbvisualizer in a financial context where attention is payed to these kind of details, and JDBC introduces errors in dbvisualizer, which prevents my customer from making his acceptance tests.

    Thanks in advance for any answer
    Cedrik.


    1/ 1st try directly under nav_util
    -bash-3.00$ nav_util execute POAORA_ADMIN
    NavSQL > select '5.12' - '5.1132' from gl_sets_of_books;
    $$CALC_1
    0.0068
    1 rows returned

    2/2nd try under dbvisualizer
    select '5.12' - '5.1132' from gl_sets_of_books ;
    $$CALC_1
    0.006800000000000139
    So it seems like the error comes from dbvisualizer and not from
    attunity itself
    -same thing under a 5.3 datasource..

    I tried the ODBC link instead of the JDBC one : still the same.
    Attunity Connect Driver
    05.3.0000
    JDBC-ODBC Bridge (ThinOdNav32.dll)
    2.0001 (05.3.0000)
    select '5.12' - '5.1132' from gl_sets_of_books;
    0.006800000000000139
    .

    The thing that the odbc connector is not natively supported under
    dbvisualizer, it's something like jdbc:odbc so jdbc gets involved at
    some point : I tried to query the datasource using BusinessObject which is natively
    ODBC compliant and I dont have the rounding problem anymore.
    I found some interesting links about java as well (the 1st one is the
    best, and in french, sorry)
    -http://download.oracle.com/javase/1.3/docs/guide/jdbc/getstart/mapping.
    html#table1
    -http://msdn.microsoft.com/en-us/library/ms378599.aspx
    -http://www.developpez.net/forums/d612542/java/general-java/probleme-
    darrondi-operations-simples-soustraction-daddition/



  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10

    Results are not biased - their are in floating point.

    The problem you are seeing where 5.12 - 5.1132 becomes 0.006800000000000139 is because representing fragments in double floating point numbers is imprecise by definition of the floating point format.

    You did not see it with nav_util because it is formatting its output with less digits, and the same may occur with other tools.

    If your additions/subtractions typically involve numbers with 4-digit decimal fractions then you can set the environment/queryProcessor/arithmeticFixedPrecision parameter to 4 to ensure this imprecision of the floating point format does not contribute bias over long set of calculations (e.g. summations). The total number of precise digits is still dependent on the double floating point format.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    CedrikBoussier is offline Junior Member
    Join Date
    Apr 2010
    Age
    36
    Posts
    7
    Rep Power
    0
    Thank you very much DrorHarari
    I had given up actually and had not seen your answer.
    The IBM support told us that the problem could be due to a mapping problem in attunity and created a patch in the meantime "This fix should ensure that attunity correctly maps to a decimal precision datatype rather than binary"
    They also said that the problem should be solved in attunity 5.3.1.

    Anyways I'm going to try your solution as a workaround in the current version of attunity that we have (attunity 4.8.2), that will be upgraded to either 5.3 or 5.3.1 soon.

    Regards
    Cedrik

  4. #4
    CedrikBoussier is offline Junior Member
    Join Date
    Apr 2010
    Age
    36
    Posts
    7
    Rep Power
    0
    precision : we use attunity in conjunction with IBM Optim and their PST_GDB driver

  5. #5
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    The environment setting I mentioned is a 'productization' of the feature that was supplied as a patch for version 4.8. It is available in versions 5.1+
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Tags for this Thread

Posting Permissions

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