DB2 DESCRIBEの使い方 結論：小数部を計算し小数部の桁数を決め、実数部の桁数が決まる
テーブル定義の表示
db2 => DESCRIBE TABLE SAKAGUCHI.TEST
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
AMOUNT SYSIBM DECIMAL 20 0 Yes
NUM SYSIBM INTEGER 4 0 Yes
NAME SYSIBM VARCHAR 10 0 Yes
3 record(s) selected.
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
AMOUNT SYSIBM DECIMAL 20 0 Yes
NUM SYSIBM INTEGER 4 0 Yes
NAME SYSIBM VARCHAR 10 0 Yes
3 record(s) selected.
入力データは、
db2 => SELECT AMOUNT,NUM FROM SAKAGUCHI.TEST;
AMOUNT NUM
----------------- -----------
12345678901234567890. 3
1 record(s) selected.
overflowのexception:
db2 => SELECT (1.0/3)*AMOUNT FROM SAKAGUCHI.TEST;
1
---------------------------------
SQL0802N Arithmetic overflow or other arithmetic exception occurred.
SQLSTATE=22003
ひとつの領域の計算途中でOverflowするから？
db2 => SELECT AMOUNT - 1.00/3 FROM SAKAGUCHI.TEST;
1
---------------------------------
SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003
AMOUNT：decimal(20,0)を1.00/3：decimal(31,30)に合わせるため？
サイズの確認
db2 => DESCRIBE SELECT (1.0/3) FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
484 DECIMAL 31,30 1 1
db2 => DESCRIBE SELECT (1.0/3)*AMOUNT FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,30 1 1
db2 => DESCRIBE SELECT AMOUNT*(1.0/3) FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,30 1 1
db2 => DESCRIBE SELECT AMOUNT - 1.00/3 FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,30 1 1
計算結果がDECIMAL(31,30)となり、それにAMOUNTの20桁の数値を代入するのでOverflowが発生する。
解決策
以下のように明示的に20桁の整数部が入るように型を指定する。
db2 => SELECT CAST(1.0/3 AS DECIMAL(31,11))*AMOUNT FROM SAKAGUCHI.TEST;
1
---------------------------------
4115226300370370366.99588477370
1 record(s) selected.
db2 => DESCRIBE SELECT CAST(1.0/3 AS DECIMAL(31,11))*AMOUNT FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,11 1 1
参考までに割り切れても結果は同じである。
db2 => DESCRIBE SELECT 1.0/2 FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
484 DECIMAL 31,30 1 1
足し算でも同じ。
db2 => DESCRIBE SELECT AMOUNT + 1.00/3 FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,30 1 1
引き算でも
db2 => DESCRIBE SELECT AMOUNT - 1.00/3 FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,30 1 1
数値の掛け算でも精度が下がる
db2 => DESCRIBE SELECT AMOUNT *(12 / CAST(13 AS DECIMAL(20,5) ) ) FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,15 1 1
db2 => DESCRIBE SELECT AMOUNT * CAST( 12 / 13 AS DECIMAL(20,5) ) FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31, 5 1 1
db2 => SELECT AMOUNT *(12 / CAST(13 AS DECIMAL(20,5) ) ) FROM SAKAGUCHI.TEST;
1
---------------------------------
SQL0802N Arithmetic overflow or other arithmetic exception occurred.
SQLSTATE=22003
解決策
db2 => DESCRIBE SELECT AMOUNT *(CAST(12 AS DECIMAL(20,5)) / CAST(13 AS DECIMAL(20,5) ) ) FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31,11 1 1
db2 => DESCRIBE SELECT AMOUNT * CAST(12/13 AS DECIMAL(20,5)) FROM SAKAGUCHI.TEST;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
485 DECIMAL 31, 5 1 1
db2 => SELECT AMOUNT *(CAST(12 AS DECIMAL(20,5)) / CAST(13 AS DECIMAL(20,5) ) ) FROM SAKAGUCHI.TEST;
1
---------------------------------
11396011293361823352.22222222230
1 record(s) selected.
結論：小数部を計算し小数部の桁数を決め、実数部の桁数が決まる。
db2 => SELECT CAST(1 AS decimal(21,20)) * CAST(123 AS DECIMAL(13,9)) FROM SYSIBM.DUAL;
1
---------------------------------
SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003
db2 => DESCRIBE SELECT CAST(1 AS decimal(21,20)) * CAST(123 AS DECIMAL(13,9)) FROM SYSIBM.DUAL;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
484 DECIMAL 31,29 1 1
123が3桁で入らないのでエラーとなる！
db2 => DESCRIBE SELECT CAST(1 AS decimal(21,20)) * CAST(123 AS DECIMAL(13,8)) FROM SYSIBM.DUAL;
Column Information
Number of columns: 1
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
484 DECIMAL 31,28 1 1
db2 => SELECT CAST(1 AS decimal(21,20)) * CAST(123 AS DECIMAL(13,8)) FROM SYSIBM.DUAL;
1
---------------------------------
123.0000000000000000000000000000
1 record(s) selected.
コメント
コメントを投稿