--创建测试表空间
db2 connect to testdb
create bufferpool bp_reg_hot_8k immediate size 5000 automatic pagesize 8k
create tablespace testdb_data_8k_01 pagesize 8k managed by database using (file '/data/testdb/testdb_data_8k_01_001.DBF' 100M) autoresize yes increasesize 10M extentsize 32 prefetchsize automatic bufferpool bp_reg_hot_8k no file system caching
--创建测试数据
create table t_table like syscat.tables in bp_reg_hot_8k
insert into t_table select * from syscat.tables
insert into t_table select * from t_table
create table t_table_1 like syscat.tables in bp_reg_hot_8k
insert into t_table_1 select * from t_table
drop table t_table
--获得表空间HWM
db2 list tablespaces show detail
Tablespace ID = 3
Name = TESTDB_DATA_8K_01
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 12800
Useable pages = 12768
Used pages = 5248
Free pages = 7520
High water mark (pages) = 10400
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
--使用db2dart 确认无连接
db2 force application all
db2dart testdb /DHWM
tbspaceid : 3
--获得object ID : 5
db2dart testdb /LHWM
tbspaceid : 3 ; desired highwater mark : 0
--获得reorg 或 导出重建 建议
--PS:如果剩余表空间不足则建议导出重建表;反之推荐reorg
db2 connect to testdb
select * from syscat.tables where tableid=5 and tbspaceid=3
--获得表名 : t_table_1
--离线重组
db2 reorg table t_table_1
--查看重组后的HWM
db2 list tablespaces show detail
Tablespace ID = 3
Name = TESTDB_DATA_8K_01
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 12800
Useable pages = 12768
Used pages = 5248
Free pages = 7520
High water mark (pages) = 5440
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
PS : 表空间的高水位问题解决方法:一、reorg 二、导出数据+重建表+导入数据
添加新评论0 条评论