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.

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.

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!

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.

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.

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.

i see. Thanks all of you the detailed replies!

Hi All,
It is solved in DB2 v9.1 fixPak 5

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

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('')?

I think it should reutn 0

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.