/*
用 free download 下來的 DB2 EXPRESS-C v9.5 如果沒有購買有效的 DB2 Enterprise Server Edition 授權，在 create partition table 時，則會出現以下錯誤訊息：
SQL8027N 正在使用表格分割功能，但沒有 DB2 Enterprise Server Edition 授權。 DB2 偵測到已使用表格分割功能，但沒有 DB2 Enterprise Server Edition 授權。請確定已向 IBM 業務代表或授權經銷商購買有效的 DB2 Enterprise Server Edition 授權，並已使用「DB2 授權中心」或 db2licm 指令行公用程式更新授權。如需更新授權的相關資訊，請參閱您的平台的「快速入門」手冊
真是 Orz ......
好在上回拿到一個 VMware 裡的 DB2 是經過授權的，可以拿來測試看看
*/
/* 先建一個測試 Table */
create table day_list
(
dt date
) partition by range(dt)
(starting '2008-01-01' ending '2008-12-31' every 3 months)
/* 從 db2cc 看建立結果 */
/* insert 2008 一整年的日期 */
INSERT INTO DAY_LIST
SELECT DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))||'-01-01') + N DAY
FROM (SELECT (ROW_NUMBER() OVER() - 1) N
FROM SYSIBM.SYSTABLES FETCH FIRST 366 ROW ONLY) D
WHERE YEAR(DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))||'-01-01')+N DAY) =
YEAR(CURRENT DATE)
/* 檢查一下總筆數 */
select count(*) from day_list
/* 傳回 366 筆 */
1
-----------
366
1 record(s) selected.
/** 這裡的HIGHVALUE 很奇怪，之後還要再測試看看 **/
/* 從 SYSTEM TABLE 看 Partition */
select datapartitionname,datapartitionid,lowvalue,highvalue
from sysibm.sysdatapartitions
where tabname = 'DAY_LIST'
PARTITIONNAME LOWVALUE HIGHVALUE
---------------------- ------------------------ -------------------------
PART0 '2008-01-01' '2008-04-01'
PART1 '2008-04-01' '2008-07-01'
PART2 '2008-07-01' '2008-10-01'
PART3 '2008-10-01' '2008-12-31'
4 record(s) selected.
/* 把 partition part0 從 day_list 中 detach 掉 */
alter table day_list detach partition part0 into tab_reuse0
DB20000I The SQL command completed successfully.
/* detach 的資料建到 tab_reuse0 去 */
/* 再從 SYSTEM TABLE 看 Partition */
select datapartitionname,datapartitionid,lowvalue,highvalue
from sysibm.sysdatapartitions
where tabname = 'DAY_LIST'
PARTITIONNAME LOWVALUE HIGHVALUE
---------------------- ------------------------ -------------------------
PART1 '2008-04-01' '2008-07-01'
PART2 '2008-07-01' '2008-10-01'
PART3 '2008-10-01' '2008-12-31'
3 record(s) selected.
/* 清空 tab_reuse0 裡的資料 */
alter table tab_reuse0 activate not logged initially with empty table
DB20000I The SQL command completed successfully.
/* 也不做完整性檢查 */
set integrity for tab_reuse0 off
DB20000I The SQL command completed successfully.
/* 將 tab_reuse0 attach 回去，雖然有 warning 但是 partition 已經加回去了 */
alter table day_list attach partition part0 starting '2008-01-01' ending '2008-03-31' from tab_reuse0
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
/* 從 SYSTEM TABLE 看 Partition */
select datapartitionname,datapartitionid,lowvalue,highvalue
from sysibm.sysdatapartitions
where tabname = 'DAY_LIST'
PARTITIONNAME LOWVALUE HIGHVALUE
---------------------- ------------------------ -------------------------
PART1 '2008-04-01' '2008-07-01'
PART2 '2008-07-01' '2008-10-01'
PART3 '2008-10-01' '2008-12-31'
PART0 '2008-01-01' '2008-03-31'
4 record(s) selected.
/* 檢查完整性 */
set integrity for day_list immediate checked
DB20000I The SQL command completed successfully.
/* 檢查總筆數，因為 part0 的資料被清掉了，所以已經不存在第一季的日期 */
select count(*) from day_list
1
-----------
275
1 record(s) selected.
/* attach 之後 tab_reuse0 馬上被 drop 掉 */
select * from tab_reuse0
SQL0204N "DB2ADMIN.TAB_REUSE0" is an undefined name. SQLSTATE=42704
/* 測試使用一般的 table 是不是也能 attach 上去 */
create table day_list_2007Q4 ( dt date ) in userspace1
DB20000I The SQL command completed successfully.
/* 新增一筆日期資料 */
insert into day_list_2007q4 values ('2007-12-31')
DB20000I The SQL command completed successfully.
/* 把剛建立的 table attach 上去並檢查完整性 */
alter table day_list attach partition part2007q4 starting '2007-10-01' ending '2007-12-31' from day_list_2007q4
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
set integrity for day_list immediate checked
DB20000I The SQL command completed successfully.
/* attach 之後 day_list_2007q4 就被drop掉了 */
select * from day_list_2007q4
SQL0204N "DB2ADMIN.DAY_LIST_2007Q4" is an undefined name. SQLSTATE=42704
/* 檢查資料是否已加入 */
select count(*) from day_list
1
-----------
276
1 record(s) selected.
/* 從 SYSTEM TABLE 看 Partition */
select datapartitionname,datapartitionid,lowvalue,highvalue
from sysibm.sysdatapartitions
where tabname = 'DAY_LIST'
PARTITIONNAME LOWVALUE HIGHVALUE
---------------------- ------------------------ -------------------------
PART1 '2008-04-01' '2008-07-01'
PART2 '2008-07-01' '2008-10-01'
PART3 '2008-10-01' '2008-12-31'
PART2007Q4 '2007-10-01' '2007-12-31'
PART0 '2008-01-01' '2008-03-31'
5 record(s) selected.
