/*
DB2 9 增加了一個提升 decimal 的資料型態，叫做 decfloat
除了增加數值資料運算時的精確性之外，運算的效能據說非常好
但是，必須搭配 IBM 的處理器 POWER6，才能感受它的效能
由於 decfloat 有遵照 IEEE 754 浮點表示法，
在一般處理器上進行運算，資料並不會有問題，
只是效能跟 decimal 沒什麼差別
*/
/* 建立測試 table */
create table datatype_test
(
tbcreator varchar(16),
amt1 decimal(19,2),
amt2 decfloat(16),
amt3 decfloat(34)
) in userspace1
DB20000I SQL 指令已順利完成。
/*
看一下在 system table 裡 table layout
decfloat 的欄位跟 float(doulbe) 在 SCALE 上都是給0
※ decfloat 的 LENGTH 是 IN BYTES
*/
select name,coltype,length,scale
from sysibm.syscolumns
where tbname = 'DATATYPE_TEST'
order by colno
NAME COLTYPE LENGTH SCALE
---------------- -------- ------ ------
TBCREATOR VARCHAR 16 0
AMT1 DECIMAL 19 2
AMT2 DECFLOAT 8 0
AMT3 DECFLOAT 16 0
已選取 4 個記錄。
/* 新增測試資料，金額欄位取亂數 */
insert into datatype_test
select tbcreator,decimal(rand() * 10000000,19,2),
cast(rand() * 10000000.00 as decfloat(16)),
cast(rand() * 10000000.00 as decfloat(34))
from sysibm.syscolumns
DB20000I SQL 指令已順利完成。
insert into datatype_test
select tbcreator,decimal(rand() * 10000000,19,2),
cast(rand() * 10000000.00 as decfloat(16)),
cast(rand() * 10000000.00 as decfloat(34))
from sysibm.syscolumns
DB20000I SQL 指令已順利完成。
insert into datatype_test
select tbcreator,decimal(rand() * 10000000,19,2),
cast(rand() * 10000000.00 as decfloat(16)),
cast(rand() * 10000000.00 as decfloat(34))
from sysibm.syscolumns
DB20000I SQL 指令已順利完成。
/* 檢查總筆數 */
select count(*) from datatype_test
1
-----------
16272
已選取 1 個記錄。
/*
選兩筆來看看
decfloat 雖然在 SCALE 上都是給0，小數點還是有
*/
select * from datatype_test fetch first 2 row only
TBCREATOR AMT1 AMT2 AMT3
--------- ------------- ------------------ -----------------
DB2EXT 1933042.39 5635853.144932401 12512.58888515885
DB2EXT 4798730.43 5850093.081453902 8087405.0111392559
已選取 2 個記錄。
/* 做一下reorgchk */
reorgchk update statistics on table orion.datatype_test
/* 使用 sql explain 分別對不同金額欄位做加總 */
C:\>db2expln -database SAMPLE -g -statement "select sum(amt1) from datatype_test" -o c:\stmtfile1.log
C:\>db2expln -database SAMPLE -g -statement "select sum(amt2) from datatype_test" -o c:\stmtfile2.log
C:\>db2expln -database SAMPLE -g -statement "select sum(amt3) from datatype_test" -o c:\stmtfile3.log
/* 發現無論對哪一個欄位運算效能都一樣，所以要搭配IBM POWER6處理器才看得出來 */
Estimated Cost = 138.325012
Estimated Cardinality = 1.000000
Access Table Name = ORION.DATATYPE_TEST ID = 2,33
| #Columns = 1
| Avoid Locking Committed Data
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Predicate Aggregation
| | | Column Function(s)
Aggregation Completion
| Column Function(s)
Return Data to Application
| #Columns = 1
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
1
RETURN
( 1)
138.325
|
1
GRPBY
( 2)
138.325
|
16272
TBSCAN
( 3)
136.964
|
16272
Table:
ORION
DATATYPE_TEST
/* 測試數值欄位 ALTER 成 DECFLOAT */
create table test
(
col1 smallint,
col2 integer,
col3 bigint,
col4 decimal(19,2),
col5 float,
col6 decfloat(16)
) in userspace1
DB20000I SQL 指令已順利完成。
/* SYSTEM TABLE 裡的 TABLE LAYOUT */
select name,coltype,length,scale
from sysibm.syscolumns
where tbname = 'TEST'
order by colno
NAME COLTYPE LENGTH SCALE
---------------- -------- ------ ------
COL1 SMALLINT 2 0
COL2 INTEGER 4 0
COL3 BIGINT 8 0
COL4 DECIMAL 19 2
COL5 DOUBLE 8 0
COL6 DECFLOAT 8 0
已選取 6 個記錄。
/* ALTER SMALLINT 欄位成 DECFLOAT */
alter table test alter column col1 set data type decfloat(16)
DB20000I SQL 指令已順利完成。
reorg table test
DB20000I REORG 指令已順利完成。
/* ALTER INTEGER 欄位成 DECFLOAT */
alter table test alter column col2 set data type decfloat(16)
DB20000I SQL 指令已順利完成。
reorg table test
DB20000I REORG 指令已順利完成。
/* ALTER BIGINT 欄位成 DECFLOAT(注意長度) */
alter table test alter column col3 set data type decfloat(34)
DB20000I SQL 指令已順利完成。
reorg table test
DB20000I REORG 指令已順利完成。
/* ALTER DECIMAL 欄位成 DECFLOAT(注意長度) */
alter table test alter column col4 set data type decfloat(34)
DB20000I SQL 指令已順利完成。
reorg table test
DB20000I REORG 指令已順利完成。
/* ALTER FLOAT 欄位成 DECFLOAT(注意長度) */
alter table test alter column col5 set data type decfloat(34)
DB20000I SQL 指令已順利完成。
reorg table test
DB20000I REORG 指令已順利完成。
/* ALTER DECFLOAT 長度 */
alter table test alter column col6 set data type decfloat(34)
DB20000I SQL 指令已順利完成。
reorg table test
DB20000I REORG 指令已順利完成。
/* 但是已經ALTER成DECFLOAT的欄位，似乎無法再變更為其它數值欄位 */
alter table test alter column col1 set data type decimal(31)
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0190N ALTER TABLE "ORION.TEST" 為直欄 "COL1"所指定的屬性與現存直欄不相容。 SQLSTATE=42837
alter table test alter column col1 set data type bigint
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0190N ALTER TABLE "ORION.TEST" 為直欄 "COL1"所指定的屬性與現存直欄不相容。 SQLSTATE=42837
alter table test alter column col1 set data type float
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0190N ALTER TABLE "ORION.TEST" 為直欄 "COL1"所指定的屬性與現存直欄不相容。 SQLSTATE=42837
