https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0055399.html
如何确认高水位：https://www.cndba.cn/hbhe0316/article/4796
db2pd -db testdb -tablespaces
如上图所示，这些表空间中的UsedPgs如果等于HWM，则无需降低高水位，反之则需要。可以评估
HWM-UsedPgs的差值，来决定是否有必要降低高水位。
查看是否是自动存储表空间https://www.cndba.cn/hbhe0316/article/4796
db2 get snapshot for tablespaces on testdb1.DMS非自动存储表空间
查看有几个container
[db2inst1@db04 ~]$ db2 list tablespace containers for 4
Tablespace Containers for Tablespace 4
Container ID = 0
Name = /db2data/testdb/data/data4k1
Type = File非自动存储表空间在降低高水位之前，需要先标记高水位，然后再降低高水位。
db2 connect to testdb
db2 "alter tablespace <tablespace name> lower high water mark"
db2 "ALTER TABLESPACE TS1 REDUCE (ALL CONTAINERS 10M)"此步骤非必须https://www.cndba.cn/hbhe0316/article/4796https://www.cndba.cn/hbhe0316/article/4796
db2 "ALTER TABLESPACE TS1 extend (ALL CONTAINERS 10M)"执行命令查看，高水位已经降低
2.自动存储表空间降高水位
如果看到Using automatic storage = Yes
则是自动存储表空间。https://www.cndba.cn/hbhe0316/article/4796
db2 "CREATE STOGROUP sg ON '/db2data/testdb/automatic'"
db2 "create tablespace tbstest managed by automatic storage USING STOGROUP sg"创建表
db2 "create table TB11( id char(254), name char(254), sex char(254), addr char(254), seri char(254), node char(254), acct_no char(254), log1 char(254), regiid char(254), prod1 char(254), prod2 char(254), prod3 char(254), prod4 char(254)) in tbstest"
db2 "create table TB12( id char(254), name char(254), sex char(254), addr char(254), seri char(254), node char(254), acct_no char(254), log1 char(254), regiid char(254), prod1 char(254), prod2 char(254), prod3 char(254), prod4 char(254)) in tbstest"删除TB11
db2 drop table TB11
执行命令降低高水位
db2 connect to testdb
db2 "alter tablespace <tablespace name> reduce max"
db2 terminate查看高水位是否降低成功
3.查看和监视空间的移动状态
查看表空间高水位状态
[db2inst1@db04 ~]$ db2 "SELECT varchar(tbsp_name, 16) as tbsp_name, TBSP_USED_PAGES,TBSP_FREE_PAGES,TBSP_PAGE_TOP from TABLE (MON_GET_TABLESPACE('TS1',-2)) AS t"
TBSP_NAME TBSP_USED_PAGES TBSP_FREE_PAGES TBSP_PAGE_TOP
---------------- -------------------- -------------------- --------------------
TS1 384 27200 448监视表空间的移动
db2 "SELECT varchar(tbsp_name, 20) as tbsp_name, NUM_EXTENTS_MOVED, NUM_EXTENTS_LEFT, TOTAL_MOVE_TIME from TABLE(MON_GET_EXTENT_MOVEMENT_STATUS('TBS1',-2)) AS t".
TBSP_NAME NUM_EXTENTS_MOVED NUM_EXTENTS_LEFT TOTAL_MOVE_TIME
-------------------- ----------------- ---------------- --------------------
TS1 100 1744 1664显示的输出信息是在表空间 TS1 降低高水位标记，释放空闲空间的过程中获取的。当前显示已经移动了 100 个数据块，还剩下 1744 个数据块，共花费 1664ms 的时间。如果在非移动状态下，所有值为 -1.
https://www.cndba.cn/hbhe0316/article/4796
或者查看表空间状态
db2 get snapshot for tablespaces on testdb | grep -i state
如果看到0x'00080000'，则处于Move状态
[db2inst1@db04 ~]$ db2tbst 0x'00080000'
State = Move in ProgressDB2 DIAGLOG日志也有相关信息
2019-10-11-20.54.47.768557+480 E736925E518 LEVEL: Info
PID : 2498 TID : 139678317537024 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-38 APPID: *LOCAL.DB2.191011125447
AUTHID : DB2INST1 HOSTNAME: db04
EDUID : 64 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbExtentMovementEntryPoint, probe:5439
DATA #1 : <preformatted>
Extent Movement started on table space 4
2019-10-11-20.54.47.768830+480 E737444E556 LEVEL: Info
PID : 2498 TID : 139678317537024 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TESTDB
APPHDL : 0-38 APPID: *LOCAL.DB2.191011125447
AUTHID : DB2INST1 HOSTNAME: db04
EDUID : 64 EDUNAME: db2agent (TESTDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbLockAndMoveExtents, probe:4904
MESSAGE : ADM6008I Extents within table space "TS1" (ID "4") have been moved.Reason code = "0".版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle
