Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: Issue with FLOAT datatype

    Hi DB2 Guru,
    I want to share DB2 issue with you. The issue is with FLOAT & Real datatype. When we use CHAR function with FLOAT datatype, the Char function suppress’ zeros in the values and when convert the CHAR value to float, the data loose precision.

    Now my problem is, when we convert CHAR value to float, data should not loose it’s precision. I am unable to figure out why this problem happening with FLOAT & Real datatype only. Is there any wayout to resolve the cryptic problem.

    SELECT CARDF FLOAT,CHAR(CARDF) FLOAT_CHAR FROM
    SYSIBM.SYSTABLES FETCH FIRST 10 ROWS ONLY;
    ---------+---------+---------+---------+---------+------
    FLOAT FLOAT_CHAR
    ---------+---------+---------+---------+---------+------
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0
    -0.1000000000000000E+01 -1.0E0

    Cheers
    Vikas

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vikas kumar
    When we use CHAR function with FLOAT datatype, the Char function suppress’ zeros in the values and when convert the CHAR value to float, the data loose precision.
    It's almost impossible to not loose precision when converting beween the (internal) float representation and a decimal expansion. Or one would have to (in some cases) write much more digits than is useful. So indeed the CHAR() function does not guarantee to keep full precision.
    With your example on the other hand, that's not the case: the floating point number -0.1000e1 is the integer -1, so the textual representation "-1.0E0" did not loose any precision, it only displayed that integer value more compactly than did the "float" display. Converting back to float would result in exactly the same internal representation as before (which would not be the case in general).
    So I don't understand your problem; could you explain what you need those conversions for, and why you can't do what you want to?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Peter.Vanroose
    It's almost impossible to not loose precision when converting beween the (internal) float representation and a decimal expansion. Or one would have to (in some cases) write much more digits than is useful. So indeed the CHAR() function does not guarantee to keep full precision.
    With your example on the other hand, that's not the case: the floating point number -0.1000e1 is the integer -1, so the textual representation "-1.0E0" did not loose any precision, it only displayed that integer value more compactly than did the "float" display. Converting back to float would result in exactly the same internal representation as before (which would not be the case in general).
    So I don't understand your problem; could you explain what you need those conversions for, and why you can't do what you want to?
    Taking a simple example:
    Code:
    SELECT float(1)/100000/100000/100000,
           char(float(1)/100000/100000/100000)
           from sysibm.sysdummy1
    returns
    +0.9999999999999999E-15 1.0E-15
    so the internal representation (the binary "approximation" in 53 bits of 10^-15) returned back to decimal is represented with 16 decimal digits, while the CHAR() function displays 15 digits at most, possibly rounding (up or down) the 16-digit output of the float itself. This will "almost always" loose the 16th decimal, unless that one is zero (as in your case).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jun 2008
    Posts
    2

    Foat datatype

    Let me explain my problem point wise:
    - We have to unload data from DB2 table in external format, hence we have to use CHAR function against float, real & double datatype.
    - When we use Char function zero get suppressed.
    - Now I have to transfer the unloaded file to linux box.
    The linux box will use unloaded file to create XML file for further use.
    - When we try to convert the data which we unloaded on mainframe to FLOAT datatype, sometime few data loose it's precision, for example:
    SELECT FLOAT(+0.9999999999999999E+49) ,
    FLOAT(CHAR(FLOAT(+0.9999999999999999E+49)))
    FROM SYSIBM.SYSDUMMY1;
    ---------+---------+---------+---------+---------+---------+---------+---

    ---------+---------+---------+---------+---------+---------+---------+---
    +0.9999999999999999E+49 +0.1000000000000000E+50
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100



    My requirement is:
    When we use CHAR function zero should not get suppressed.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vikas kumar
    FLOAT(CHAR(FLOAT(+0.9999999999999999E+49)))
    +0.1000000000000000E+50
    Exactly.
    There is no such float value as +0.9999999999999999E+49 (16 digits), since the precision of a float is only 53 bits, i.e., a little bit less than 16 digits.
    So the decimal representations +0.9999999999999999E+49 and +0.1E+50 are equally well representing that internal representation of the floating point number.

    If you want to transfer the 8 bytes of a float exactly, you need two systems with the same internal floating point data representation. You could use the "FLOAT IEEE" option of the UNLOAD utility (mainframe) to be compatible with what the LOAD command expects on DB2 for linux.

    If the data has to be placed in an XML file, which is textual by design, this is not an option of course. You will have to represent your (binary float) data as decimal (either on the mainframe or on linux), a process by which you loose precision (at least for some numeric values), unavoidably. This problem is not DB2-related or mainframe-related, it's "binary to decimal"-related.

    But then, if you are so concerned about exact (decimal) numeric values, why use float in your database in the first place? "DECIMAL" is much more appropriate for that purpose! (You'll just have to store the exponent +49 differently, but if all numbers are about the same magnitude then the exponent is not relevant for the database.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vikas kumar
    We have to unload data from DB2 table in external format.
    Now I have to transfer the unloaded file to linux box.
    The linux box will use unloaded file to create XML file for further use.
    Why don't you produce XML directly on the mainframe? (Either from DB2, or from an application program running on z/OS and reading the data from DB2?)
    Or, alternatively, use DB2 9 on Linux (with its great XML support), and DRDA to get the mainframe data to Linux.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I can only second Peter's statements. You should understand how floating point numbers are stored internally and why it is (generally) impossible to transfer such values without loosing precision if you stick to a text-based format and convert the values to a decimal representation.

    Also have a look at DECFLOAT. It combines the advantages of DECIMAL and floating point data types.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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