关于DB2除法的小数位问题

今天在做db2测试的时候发现一个问题：

select 1/3 from sysibm.sysdummy1;    

结果得到： 0！！！！郁闷，怎么会这样。

后来仔细查了一下db2的资料，发现db2的算术运算是遵循一下规则：

argument1                argument2               result

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 由于，由于两者数据类型皆为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 select 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; 

报错说除法运算无效。其实原因就是小数位的问题，按照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。

（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.3333333333333333333

(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) from 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)型。

哎。。。可怜的db2啊。

 

 

下边贴一个关于db2小数位的英文文献供参考：

 

Problem
You are receiving a SQL0419N message or inappropriate $ values against decimal divisions, or a truncation of decimal values.  
  
Cause  
Negative or an inappropriate scale of decimal division.  
  
Solution
First, here are the details of SQL0419N,

-------------------------------------------------------------------------------------------------
SQL0419N A decimal divide operation is not valid because the result would have a negative scale.

Explanation: A specified decimal division is not valid because it will result in a negative scale.

The formula used internally to calculate the scale of the result for decimal division is:

Scale of result = 31 - np + ns - ds

where np is the precision 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 statement cannot be 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-np+ns-ds). This will guarantee a minimum scale of 3, which 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 hidden database parameter; that is, "db2 get db cfg for <db-name>" will not show this parameter. The only way it could be checked is by performing a decimal division. For example, to check whether it's set to YES or NO, do the following,

db2 connect to <dbname>
db2 "create table test (col1 int)"
db2 "insert into test values (0)"
db2 "insert into test 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=42911

(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 (APAR#IY15192).

The MIN_DEC_DIV_3 database configuration parameter ensures a minimum scale of 3, which is little short for some customer'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_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 return,

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 applications for existing databases. This can occur when the resulting scale for decimal division 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 databases.

- 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 expressions, 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 example, after changing the value from NO to YES, the additional scale digits may not be included in the results until rebind occurs. For any changed static packages, an explicit rebind command can be used to force a rebind.

- A check constraint involving decimal division may restrict some values that were previously accepted. Such rows now violate the constraint but will not be detected until the one of the columns involved in the check constraint row is updated or the SET INTEGRITY command with the IMMEDIATE CHECKED option is processed. To force checking of such a constraint, perform an ALTER TABLE command in order to drop the check constraint and then perform an ALTER TABLE command to add the constraint again.