Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2008
    Posts
    7

    Unhappy Unanswered: Double('') behave differently within DB2 v8 & v9

    I have migrated a DB2 v8 to v9.

    when i try this sql statement:
    select double('') from sysibm.sysdummy1;

    DB2 v8: returns value 0

    DB2 v9 returns zero result or
    SQL0443N Routine "SYSFUN.DOUBLE" (specific name "CHARTODOUBLE") has returned
    an error SQLSTATE with diagnostic text "SYSFUN:11". SQLSTATE=38552

    Actually, I will convert a '' to 0 in DB by using double('').
    e.g. select double(field1) from sysparameter

    Do any one know how to solve it? I hope I dont need to replace the field '' with '0' because some of the fields will be used as ''.

    Moreover, does anyone any other functions will behave differently in v8 & v9?

    Thanks a lot.
    Last edited by lung4; 09-08-08 at 08:53.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You seem to have been using the function incorrectly. The manual, which you probably didn't read, says that "the argument can be of type CHAR or VARCHAR in the form of a numeric constant." '' is not a numeric constant.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by n_i
    The manual says that "the argument can be of type CHAR or VARCHAR in the form of a numeric constant.
    " '' is not a numeric constant.
    Still, it's a reasonable complaint about a non-documented change in behaviour, even with regard to a non-documented feature of v8 which apparently treated " " as zero is this context!
    --_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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would call this a bug in V8, which has been fixed in V9. So the complaint would have been on V8 where DB2 didn't behave correctly.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Peter.Vanroose
    it's a reasonable complaint about a non-documented change in behaviour, even with regard to a non-documented feature
    Changes in the undocumented behaviour should not be documented by definition, otherwise the behaviour itself would become post-documented. Clearly, the fact that passing an empty string as a parameter to the function is not defined indicates that the function behaviour in such circumstances will be undefined, which we all have an opportunity to observe. In my opinion this is neither a bug nor a documentation error.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You can try opening an ETR with IBM, but I think you will recieve the same info that you have already recieved here on the forum. It was broken in V8 and has been fixed in V9. They might go back and update the documentation, but that's probably as far as it will go.

  7. #7
    Join Date
    Sep 2008
    Posts
    7
    i see. Thanks all of you the detailed replies!

  8. #8
    Join Date
    Sep 2008
    Posts
    7
    Hi All,
    It is solved in DB2 v9.1 fixPak 5

    http://www-01.ibm.com/support/docvie...id=swg1IZ13701

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by lung4
    It is solved in DB2 v9.1 fixPak 5
    I've looked at that page, and it indeed says "fixed in v9.1 fp 5".
    But no details, so how is this problem fixed? What is the outcome of double(' ') and/or of double('')?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Sep 2008
    Posts
    7
    I think it should reutn 0

    Quote Originally Posted by Peter.Vanroose
    I've looked at that page, and it indeed says "fixed in v9.1 fp 5".
    But no details, so how is this problem fixed? What is the outcome of double(' ') and/or of double('')?

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by n_i
    Changes in the undocumented behaviour should not be documented by definition, otherwise the behaviour itself would become post-documented. Clearly, the fact that passing an empty string as a parameter to the function is not defined indicates that the function behaviour in such circumstances will be undefined, which we all have an opportunity to observe. In my opinion this is neither a bug nor a documentation error.
    The question is what is a "bug" for you. Personally, I think that accepting invalid input and not raising an error is simply a bug.
    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
  •