Technical Blog Post
Abstract
Decimal data type conversion differences between DB2 ORACLE mode and ORACLE
Body
When you convert a value to decimal data type in DB2 ORACLE mode, you will see that the conversion follows different rule from ORACLE.
1. DB2 ORACLE mode : decflt_rounding in database cfg
2. ORACLE : ROUND_HALF_UP
And default value for decflt_rounding is ROUND_HALF_EVEN in DB2. So you will see different results if you just set DB2_COMPATIBILITY_VECTOR to ORA in DB2.
For better understanding, let me compare the same examples between two.
1. DB2 ORACLE mode
DB2 ORACLE mode will follow the decflt_rounding. I used default value(ROUND_HALF_EVEN) so the result is like as follows.
-------------------------------------
(woongc@machine6) /home/woongc
$ db2 -x "values(decimal(12.345,5,2))"
12.34
-------------------------------------
2. ORACLE
ORACLE uses ROUND_HALF_UP. Different from simple ROUND, both data type uses ROUND_HALF_UP for decimal conversion.
(For ROUND, please check the following link attached below)
-------------------------------------
SQL> select cast (12.345 as decimal(5,2)) from dual;
CAST(12.345ASDECIMAL(5,2))
--------------------------
12.35
SQL> select cast (12.345f as decimal(5,2)) from dual;
CAST(12.345FASDECIMAL(5,2))
---------------------------
12.35
-------------------------------------
For more information about this issue, please refer to the following links.
DB2_COMPATIBILITY_VECTOR registry variable
decflt_rounding - Decimal floating point rounding configuration parameter
ORACLE ROUND function
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13286257