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 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";