db2中模擬ORACLE中的truncate操作

關於在DB2中模擬truncate操作,檢視空間釋放和HWM情況:
1.load/import from /dev/null of del replace into wm.test_truncate_table nonrecoverable;
2.delete from wm.test_truncate_table ;
3.alter table wm.test_truncate_table activate not logged initially with empty table;


--DB2資料庫版本
[431/app/etl/]db2level
DB21085I Instance "db2root" uses "64" bits and DB2 code release "SQL09018"
with level identifier "02090107".
Informational tokens are "DB2 v9.1.0.8", "s090823", "U823514", and Fix Pack
"8".
Product is installed at "/opt/IBM/db2/V9.1".

--db2 list tablespace show detail;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767360
Free pages = 1198688
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--建測試表,插入資料
--create table wm.test_truncate_table like syscat.tables in fn_tw6_01 ;
--insert into wm.test_truncate_table select * from syscat.tables with ur ;

--再次檢視錶空間使用
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016 --使用了672 Pages
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--使用load null清除表中資料
--db2 load from /dev/null of del replace into table_name;

--db2 tablespaces show detail ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496 --清除表中資料佔用的480Pages
High water mark (pages) = 864320 --HWM未降
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--結論load null立即釋放了表佔用的空間,但沒有修改高水位標記

--drop table wm.test_truncte_table;
--db2 list tablespaces show detail ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767360
Free pages = 1198688 --釋放了表結構分配的空間192 Pages
High water mark (pages) = 864320 --HWM未降
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--上面一步測試說明,對於load null後drop表,表佔用的空間立即釋放了

--測試如果直接的drop表的情況
--db2 "create table wm.test_truncate_table like syscat.tables in fn_tw6_01 "
--db2 list tablespaces show detail;

Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--db2 "insert into wm.test_truncate_table select * from syscat.tables with ur "
--db2 list tablespaces show detail;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--drop table wm.test_truncate_table;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767360
Free pages = 1198688
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--直接drop掉表後空間也立即釋放了.

--下面演示delete資料後不會立即釋放表空間
--db2 "create table wm.test_truncate_table like syscat.tables in fn_tw6_01 "
--db2 list tablespaces show detail;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--db2 "insert into wm.test_truncate_table select * from syscat.tables "
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--db2 delete from wm.test_truncate_table ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016 --delete後空間沒有釋放,HWM也沒有降
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

--使用alter table wm.test_truncate_table activate not logged initially with empty table;清除表資料,空間立即釋放
db2 "insert into wm.test_truncate_table select * from syscat.tables with ur "
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

db2 alter table table_name activate not logged initially with empty table ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496 --表中資料佔用的空間被釋放,HWM沒有降
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1


DB2 V9.7可以執行;V9.1不行:
db2 "truncate table table_name
ignore delete trigger --truncate本身不會啟用觸發器,此設定可以指定在有關聯觸發器時如何處理,忽略或者配置為報錯.
reuse storage
immediate"

==收縮表空間
db2 reorgchk update statistics on table wm.test_truncate_table ;
db2 reorg table wm.test_truncate_table ;

DB2 V9.7可以執行如下;V9.1不行:
db2 "alter tablespace fn_tw6_01 reduce";
db2 "alter tablespace fn_tw6_01 lower high water mark";

[@more@]