DB2中除法的小数位数遵循下面规则:
31-p+s1-s2(其中p为被除数的精度,s1为被除数的小数位,s2为除数的小数位)
describe select 1.0 from sysibm.sysdummy1;
SQLTYPE DECIMAL 精度(2,1)
describe select 1 from sysibm.sysdummy1;
SQLTYPE INTEGER 精度(4,0)
SELECT 1.0/1 FROM SYSIBM.DUAL;
结果:1.000000000000000000000000000000
SELECT 1.0/3 FROM SYSIBM.DUAL;
结果:0.333333333333333333333333333333
都是保留30位 (31-2+1-0=30)
反过来:
SELECT 1/3.0 FROM SYSIBM.DUAL;
结果:0.3333333333333333333
保留19位 (31-11+0-1=19) INTEGER4个字符占用10位符号位1位共11位
==============================================================================================================================
另外,如果你的db2没有设置过的话,运行一下语句可能会出错:
select DEC(1,31,0)/DEC(1,31,5) from sysibm.sysdummy1;
报错说除法运算无效。其实原因就是小数位的问题,按照31-p+s-s'的算法,31-31+0-5=-5,也即小数位数为-5,小数位又怎么能是负值呢?所以就报错了。
此时需要设置一个参数:MIN_DEC_DIV_3. 执行以下语句即可
DB2 UPDATE DB CFG FOR db_name USING MIN_DEC_DIV_3 YES
即将MIN_DEC_DIV_3的值设置为YES,意思是小数位数取3和按照31-p+s-s' 计算出的小数位两者的较大值。即是说最小也有3位小数,这样自然就不会再报错了。
需要注意的是,虽然可以使用db2 update db cfg 命令来设置MIN_DEC_DIV_3这个参数,但是实际上这个参数在db cfg 中是不可见的。
也就是说不要指望使用db2 get db cfg for db_name 可以找到它,这是一个隐藏的参数(搞不懂db2是什么用意。。。。。。)。
从db2v7版本以上又引入了一个DB2_MIN_DEC_DIV_6这么一个参数,可以将小数位至少保存6位,如果MIN_DEC_DIV_3和DB2_MIN_DEC_DIV_6同时为YES,则DB2_MIN_DEC_DIV_6覆盖MIN_DEC_DIV_3。
DB2_MIN_DEC_DIV_6这个参数可以使用db2set 来设置:语句为
db2set DB2_MIN_DEC_DIV_6=YES 可以使用DB2SET -ALL 来查看
设完需要重启db2。
========================================================================================================================
再实际运用中我们经常遇到两字段相除,如果要保证在INT的情况下系统不直接舍弃小数有如下几种方法:
(1) select cast(1 as float)/3 from sysibm.sysdummy1; --使用cast将1转为float型,然后再才除以3. 结果:0.3333333333333333
(2) select dec(1,10,2)/3 from sysibm.sysdummy1; ---使用dec函数将1转换为decimal(10,2),然后除以3 结果:0.33333333333333333333333
(3) select 1/3.0 from sysibm.sysdummy1; ----同样得到小数值 结果: --0.3333333333333333333
(4) select 1.0/3 from sysibm.sysdummy1; ---得到小数值 结果: --0.333333333333333333333333333333
如果再此基础上 保留固定位数可以用以下两个方法:
(1)dec(xxxx,10,2)
(2)cast(xxxx as decimal(10,2) )
如果要四舍五入:
(1)dec(xxxx+0.005,10,2)
31-p+s1-s2(其中p为被除数的精度,s1为被除数的小数位,s2为除数的小数位)
describe select 1.0 from sysibm.sysdummy1;
SQLTYPE DECIMAL 精度(2,1)
describe select 1 from sysibm.sysdummy1;
SQLTYPE INTEGER 精度(4,0)
SELECT 1.0/1 FROM SYSIBM.DUAL;
结果:1.000000000000000000000000000000
SELECT 1.0/3 FROM SYSIBM.DUAL;
结果:0.333333333333333333333333333333
都是保留30位 (31-2+1-0=30)
反过来:
SELECT 1/3.0 FROM SYSIBM.DUAL;
结果:0.3333333333333333333
保留19位 (31-11+0-1=19) INTEGER4个字符占用10位符号位1位共11位
==============================================================================================================================
另外,如果你的db2没有设置过的话,运行一下语句可能会出错:
select DEC(1,31,0)/DEC(1,31,5) from sysibm.sysdummy1;
报错说除法运算无效。其实原因就是小数位的问题,按照31-p+s-s'的算法,31-31+0-5=-5,也即小数位数为-5,小数位又怎么能是负值呢?所以就报错了。
此时需要设置一个参数:MIN_DEC_DIV_3. 执行以下语句即可
DB2 UPDATE DB CFG FOR db_name USING MIN_DEC_DIV_3 YES
即将MIN_DEC_DIV_3的值设置为YES,意思是小数位数取3和按照31-p+s-s' 计算出的小数位两者的较大值。即是说最小也有3位小数,这样自然就不会再报错了。
需要注意的是,虽然可以使用db2 update db cfg 命令来设置MIN_DEC_DIV_3这个参数,但是实际上这个参数在db cfg 中是不可见的。
也就是说不要指望使用db2 get db cfg for db_name 可以找到它,这是一个隐藏的参数(搞不懂db2是什么用意。。。。。。)。
从db2v7版本以上又引入了一个DB2_MIN_DEC_DIV_6这么一个参数,可以将小数位至少保存6位,如果MIN_DEC_DIV_3和DB2_MIN_DEC_DIV_6同时为YES,则DB2_MIN_DEC_DIV_6覆盖MIN_DEC_DIV_3。
DB2_MIN_DEC_DIV_6这个参数可以使用db2set 来设置:语句为
db2set DB2_MIN_DEC_DIV_6=YES 可以使用DB2SET -ALL 来查看
设完需要重启db2。
========================================================================================================================
再实际运用中我们经常遇到两字段相除,如果要保证在INT的情况下系统不直接舍弃小数有如下几种方法:
(1) select cast(1 as float)/3 from sysibm.sysdummy1; --使用cast将1转为float型,然后再才除以3. 结果:0.3333333333333333
(2) select dec(1,10,2)/3 from sysibm.sysdummy1; ---使用dec函数将1转换为decimal(10,2),然后除以3 结果:0.33333333333333333333333
(3) select 1/3.0 from sysibm.sysdummy1; ----同样得到小数值 结果: --0.3333333333333333333
(4) select 1.0/3 from sysibm.sysdummy1; ---得到小数值 结果: --0.333333333333333333333333333333
如果再此基础上 保留固定位数可以用以下两个方法:
(1)dec(xxxx,10,2)
(2)cast(xxxx as decimal(10,2) )
如果要四舍五入:
(1)dec(xxxx+0.005,10,2)
(2)cast(round(xxxx,)3 as decimal(10,2) )
=====================================================================================================================