I found a interesting issue a NUMBER datatype on DB2.
When you also set up oracle mode (like db2_compatibility_set = ORA (or FFF)) in the DB2 9.7, in addition, you may happen to use NUMBER data type.
Especially you might use the default mode of "NUMBER" It can be automatically changed to decfloat type. Who cares?
I was about to find one funny thing when I use the default NUMBER type.
It would be divided into 0 !!
Look the flowing example!
1. check the version
select VERSIONNUMBER from sysibm.sysversions
VERSIONNUMBER
----------------
9070000
2. create a sample table and then try insert some numbers;
create table d1(c1 number) ;
insert into d1 values(10) ;
3. Divide them into 0!! Yes. it is working!
update d1 set c1=c1/0
Warnings: --->
W (1): DB2 SQL error: SQLCODE: 364, SQLSTATE: 0168F, SQLERRMC: DIVISION BY ZERO;DIVISION
DECFLOAT exception "DIVISION BY ZERO" has occurred during "DIVISION" operation.
<---
4. select the table and then you face with the funny result!
select * from d1
>[Error] Script lines: 1-2 --------------------------
DB2 SQL error: SQLCODE: -304, SQLSTATE: , SQLERRMC: null
Message: A value cannot be assigned to a host variable because the value is not within the range of the host variable's data type.
Yes. You can also test this fun situation, but I never hope this kinds of the result. Anyway, I also simply solve this problem using NUMBER (p,s) because it can convert decimal type !!
DB2 enthusiasts can join this group on MyDeveloperWorks and share your expertise and views. Query Quest contests and others may fetch you goodies if your post / question is the best one . So start posting !
https://www.ibm.com/developerworks/mydeveloperworks/groups/service/html/communityview?communityUuid=ff78a96f-bf23-457e-befa-77f266844cbb
The above article focusses on Oracle compatibilty introduced in DB2 9.7 , and portrays how easy it is to port your Oracle app in days to DB2 rather than months . Hope this helps you :)
Comment by RAVI GROVER on July 31, 2009 at 12:35am
Hi Anil!!
I have gone through your article on the latest IBM data management "SQL Compatibilty in DB2". It was a nice informative post that u had made. I wish you could explain more about the new isolation level(Currently Commited) in detail for 9.7. Cheers!!!
hi ! Anil...
Yes I came to know abt Idug 2009 India ...going to happen on september 24-26
can you tell me how much is it going to cost to attend?....
I really want to....
You need to be a member of DB2 IN INDIA to add comments!