关于DB2除法的小数位问题

1、关于db2除法的小数位问题今日在做db2测试的时候发觉一个问题： select 1/3 from sysibm.sysmmy1; 结果得到： 0！烦闷，怎么会这样。 后来认真查了一下db2的资料，发觉db2的算术运算是遵循一下规章： argument1 argument2 relt decimal(a,b) decimal(c,d) decimal(p,s) p=max( max(b,d) max(a-b,c-d) s=max(b,d) 但除法的小数位计算遵循下面规章: 31-p s-s'(其中p为被除数的精度，s为被除数的小数位，s'为除数的小数位) 举例来讲： 1/3 因为

2、，因为两者数据类型皆为int，所以结果也为int 即结果为0 1.0/3，因为1.0为小数，根据小数除法的规章， describe select 1.0 from sysibm.sysdummy1; (2,1) describe select 3 from sysibm.sysdummy1; (4,0) 31-2 1-0=31-1=3031-2 1=30，故此保留30位小数。 select 1.0/3 from sysibm.sysdummy1; 结果为：- 0.333333333333333333333333333333 1/3.0,因为1为int，3.0为小数 describe selec

3、t 1 from sysibm.sysdummy1; (4)-4个字节，共占10位，其中符号位一位，共11位 describe select 3.0 from sysibm.sysdummy1; (2,1) 31-11 0-1=31-12=19，故此保留19位小数。 select 1/3.0 from sysibm.sysdummy1; 结果为：-0.3333333333333333333 另外，假如你的db2没有设置过的话，运行一下语句可能会出错： select dec(1,31,0)/dec(1,31,5) from sysibm.sysdummy1; 报错说除法运算无效。其实缘由就是小数

4、位的问题，根据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 up db cfg 来设置min_dec_div_3这个参数，但是事实上这个

5、参数在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这个参数可以用法db2 来设置：语句为 db2set db2_min_dec_div_6=yes 可以用法db2set -all 来查看

6、设完需要重启db2。 （ps:我用法的是db2 v9.5，服务器为aix，在我自己的平台上测试min_dec_div_3是可以的，但是无论我把db2_min_dec_div_6设置为yes或者no，都没看出有什么效果，不知道是我的设置办法不对还是怎么回事。） 假如想要最初的语句1/3得到非零 。可以用法如下办法： (1) select 1.0/3 from sysibm.sysdummy1; -得到小数 结果： -0.333333333333333333333333333333 (2) select 1/3.0 from sysibm.sysdummy1; -同样得到小数 结果： -0.333

7、3333333333333333 (3) select cast(1 as float)/3 from sysibm.sysdummy1; -用法cast将1转为float型，然后再才除以3. 结果：0.3333333333333333 (4) select dec(1,10,2)/3 from sysibm.sysdummy1; -用法dec函数将1转换为decimal(10,2)，然后除以3 结果：0.33333333333333333333333 其实假如想要把2个数的商四舍五入保存两位小数， oracle中可以挺直用法round函数即可： select round(a/b,2) fro

8、m dual; 而db2中却要绕几个弯才行：需要用法 select dec(cast(a as float)/b 0.005,10,2) from sysibm.sysdummy1; 先用cast转换a为float型，然后运算，再用法 0.005作为四舍五入，然后再用法dec截取2位小数。或者： select cast(round(cast(a as float)/b,2) as decimal(10,2) from sysibm.sysdummy1; 先用法cast转a为float，然后运算，再用法round四舍五入取2位小数，然后用法cast转换为decimal(10,2)型。 哎。可怜的

9、db2啊。 下边贴一个关于db2小数位的英文文献供参考： problem you are receiving a sql0419n message or inappropriate $ values against decimal divisions, or a uation of decimal values. cause negative or an inappropriate scale of decimal division. solution first, here are the detai of sql0419n, - sql0419n a decimal dive operat

10、ion is not valid because the result would have a negative scale. explanation: a specifi decimal division is not valid because it will result in a negative scale. the foula u internally to calculate the scale of the result for decimal division is: scale of result = 31 - np ns - ds where np is the pre

11、cision of the numerator, ns is the scale of the numerator, and ds is the scale of the denominator. federated system users: decimal division can occur at the federated server, at data sources, or both. the specified decimal division results in an invalid scale for that data source. the ement cannot b

12、e processed. - therefore, if (np ds) (31 ns) then sql0419n will be returned. to avoid this situation a database configuration parameter min_dec_div_3 could be set. the default value is no and it could be set to yes. when min_dec_div_3 is set to yes a decimal division will have a scale of max(3, 31-n

13、p ns-ds). this will guaran a minimum scale of 3, will avoid triggering the sql0419n message the min_dec_div_3 could be set as: db2 update db cfg for dbname using min_dec_div_3 yes db2 terminate db2 deactivate db dbname db2 activate db dbname min_dec_div_3 is a hien database parameter; that is, db2 g

14、et db cfg for db-name will not show this parameter. the only way it could be checked is by performing a decimal division. for ample, to check whether it's set to yes or no, do the following, db2 connect to dbname db2 create table test (1 int) db2 insert into test values (0) db2 insert into test

15、values (1) db2 select dec(1,31,0)/dec(1,31,5) from test if min_dec_div_3 is set to yes the output will be, 1 - 1.000 1.000 2 record(s) . if the min_dec_div_3 is set to no the output will be, sql0419n a decimal divide operation is not valid because the result would have a negative scale. sqlstate=429

16、11 (explanation : np = 31, ds = 5, ns = 0. hence, np ds 31 ns ) from the beginning of db2 v7 a new functionality to obtain a minimum scale of 6 for division is introduced (apariy15192). the min_dec_div_3 database configuration parameter ensures a minimum scale of 3, which is little short for some cu

17、stomer's calculations. so, this is enhanced and a registry variable db2_min_dec_div_6 is introduced. this might have two values, yes or no. db2_min_dec_div_6 overrides min_dec_div_3 to allow a minimum scale of 6 instead of 3. with this extra scale a correct dollar amount is ensured. db2_min_dec_

18、div_6 could be set as follow, db2set db2_min_dec_div_6=yes db2 terminate db2stop db2start to set db2_min_dec_div_6 to yes, a prior setting of min_dec_div_3 is a requirement. after setting of db2_min_dec_div_6 to yes, if the select query mentioned earlier in this document is run, the following will r

19、eturn, db2 connect to dbname db2 select dec(1,31,0)/dec(1,31,5) from test 1 - 1.000000 1.000000 2 record(s) . changing this database configuration parameter and/or the registry variable may cause changes to appliions for existing databases. this can occur when the resulting scale for decimal divisio

20、n would be impacted by changing this database configuration parameter and/or registry variable. listed below are some possible scenarios that may impact applications. these scenarios should be considered before changing the min_dec_div_3 and/or db2_min_dec_div_6 on a database server with existing da

21、tabases. - if the resulting scale of one of the view columns is changed, a view that is defined in an environment with one setting could fail with sqlcode -344 when referenced after the database configuration parameter is changed. the message sql0344n refers to recursive common table essions, however, if the object name (first token) is a view, then you will need to drop the view and create it again to avoid this error. - a static package will not change behavior until the package is rebound, either implicitly or explicitly. for exam