Technical Blog Post
Abstract
Decimal data type conversion differences between db2 and oracle mode
Body
When float/double data type number is converted to decimal data type, some digits might be truncated by the precision and scale parameter in decimal data type definition.
In this situation, db2 mode and oracle mode use different policies like as follows.
1. db2 mode : digits are just truncated from the end of the decimal number if the number of digits to the right of the decimal separator character is greater than the scale scale.
2. oracle mode : digits are truncated based on the DECFLT_ROUNDING in db cfg.
For better understanding, let me compare the same example between two mode.
1. db2 mode
When we need the conversion from a float/double to decimal, db2 mode just discard the digits after scale 6 in the following example.
-------------------------------------
(woongc@machine1) /home/machine1/woongc
$ db2 -x "values(decimal(0.813148571428571,12,6))"
0.813148
(woongc@machine1) /home/machine1/woongc
$ db2 -x "values(decimal(0.0000015,12,6))"
0.000001
(woongc@machine1) /home/machine1/woongc
-------------------------------------
2. oracle mode
With oracle compatibility(oracle mode), this conversion follows the DECFLT_ROUNDING method, ROUND_HALF_EVEN.
-------------------------------------
(woongc@machine2) /home/woongc
$ db2set
DB2_COMPATIBILITY_VECTOR=ORA
...
(woongc@machine2) /home/woongc
$ db2 get db cfg | grep -i decflt_rounding
Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN
(woongc@machine2) /home/woongc
$ db2 -x "values(decimal(0.813148571428571,12,6))"
0.813149
(woongc@machine2) /home/woongc
$ db2 -x "values(decimal(0.0000015,12,6))"
0.000002
-------------------------------------
For more information about this issue, please refer to the following links.
DECIMAL or DEC scalar function
NUMBER data type
decflt_rounding - Decimal floating point rounding configuration parameter
[{"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
ibm13286299