执行降高水位前，最好先执行重组，重组类似于windows系统的碎片整理，碎片整理后，空闲空间块连在一起，才能体现出高水位。
碎片状态下是基本没有高水位的，因为中间不连续的空闲空间是不计算在高水位里的。$su - db2inst1
$ df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 10.00 9.61 4% 19254 1% /
/dev/hd2 10.00 6.15 39% 61741 5% /usr
/dev/hd9var 10.00 6.94 31% 7091 1% /var
/dev/hd3 10.00 6.22 38% 665 1% /tmp
/dev/fwdump 2.00 2.00 1% 9 1% /var/adm/ras/platform
/dev/hd1 20.00 6.74 67% 3535 1% /home
/dev/hd11admin 1.00 1.00 1% 7 1% /admin
/proc - - - - - /proc
/dev/hd10opt 20.00 17.23 14% 12783 1% /opt
/dev/livedump 1.00 1.00 1% 4 1% /var/adm/ras/livedump
/dev/mqmlv02 5.00 4.86 3% 2319 1% /usr/mqm
/dev/mqmlv01 5.00 5.00 1% 75 1% /var/mqm
/dev/db2datalv 3000.00 593.19 81% 123 1% /db2data
/dev/db2loglv 20.00 18.43 8% 29 1% /db2log
/dev/commlv 40.00 35.58 12% 61037 1% /home/communicate
/dev/databaselv 600.00 567.85 6% 430198 1% /home/database
/dev/db2fenc1lv 10.00 10.00 1% 6 1% /home/db2fenc1
/dev/db2inst1lv 10.00 8.92 11% 2036 1% /home/db2inst1
/dev/mhweblv 20.00 12.80 37% 2350 1% /home/mhweb
/dev/mqloglv 5.00 2.50 51% 17 1% /var/mqm/DB18/log
/dev/qmgrslv 50.00 49.97 1% 153 1% /var/mqm/DB18/qmgrs db2 connect to zbbb
Database Connection Information
Database server = DB2/AIX64 10.5.2
SQL authorization ID = DB2INST1
Local database alias = ZBBB 1. 查看DB2数据库表空间信息:
db2 list tablespaces show detail: 确认表空间是系统管理SMS（只要磁盘空间有空闲，就能自动扩展），还是数据库管理DMS的。
Tablespace ID = 13
Name = FCTTEMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1 Tablespace ID = 14
Name = MOTTEMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1 Tablespace ID = 15
Name = MHAPPLOBTEMP
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1 Tablespace ID = 16
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 12. 查看DMS类表空间是否为自动存储属性，db2 get snapshot for tablespaces on DB_NAME ##Using automatic storage:
3.降低高水位
一、查询表空间使用情况：db2 "select char(TBSP_NAME,20) as TBSP_NAME, TBSP_USABLE_PAGES, TBSP_USED_PAGES, rtrim(100*TBSP_USED_PAGES/TBSP_USABLE_PAGES)||'%' as USED_RATE from sysibmadm.SNAPTBSP_PART where TBSP_USABLE_PAGES>0 order by TBSP_NAME,DBPARTITIONNUM"二、查询筛选出所有高水位标记大于已使用页的表空间：
db2 "select char(TBSP_NAME,20) as TBSP_NAME,RECLAIMABLE_SPACE_ENABLED, TBSP_USED_PAGES, TBSP_FREE_PAGES, TBSP_PAGE_TOP from TABLE (MON_GET_TABLESPACE('',-2)) AS t where t.TBSP_PAGE_TOP > t.TBSP_USED_PAGES"TBSP_NAME RECLAIMABLE_SPACE_ENABLED TBSP_USED_PAGES TBSP_FREE_PAGES TBSP_PAGE_TOP
-------------------- ------------------------- -------------------- -------------------- --------------------
MHAPPGEN 1 169792 485552 337360
MHAPPLOB 1 8519456 20672 8530240
MHTEMPGEN 1 1200 654144 1232RECLAIMABLE_SPACE_ENABLED 是表空间的 可回收存储器 属性；如果值是1，表示空间可回收。
三.DB2降低表空间高水位HWM
a> DMS自动存储表空间(DMS with AutoStorage) 直接运行 REDUSE MAX 来最多的释放空闲空间 ：
db2 alter tablespace MHHISTORYLOB reduce max
这个语句执行后就提示成功了，但是表空间水位并没有立刻降低，有个表空间数据块移动的过程（几个小时或以上）
对MHHISTORYLOB执行降高水位操作过程中，可能消耗的时间比较长，但是再查询块移动结果的时候，发现已经移动完毕，但是高水位依然有。
可以继续执行reduce max。b> 据说DMS非自动存储表空间(需要先运行 LOWER HIGH WATER MARK 子句降低高水位标记，再运行 REDUCE 子句释放表空间)：先执行
ALTER TABLESPACE USERSPACE1 LOWER HIGH WATER MARK；然后ALTER TABLESPACE USERSPACE1 REDUCE (ALL CONTAINERS 100 M)。四、监视表空间USERSPACE1移动情况：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('USERSPACE1',-2)) AS t"
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('MHHISTORYLOB',-2)) AS t"
db2 alter tablespace MHHISTORYGEN reduce max
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('MHHISTORYGEN',-2)) AS t" db2 alter tablespace MHAPPGEN reduce max
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('MHAPPGEN',-2)) AS t"db2 alter tablespace MHAPPLOB reduce max
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('MHAPPLOB',-2)) AS t"例如：MHAPPGEN表空间 降低高水位标记，释放空闲空间；当前显示已经移动了 268 个数据块，还剩下3504个数据块，共花费 15060ms 的时间。如果在非移动状态下，所有值为 -1。
$ 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('MHAPPGEN',-2)) AS t"TBSP_NAME NUM_EXTENTS_MOVED NUM_EXTENTS_LEFT TOTAL_MOVE_TIME
-------------------- ----------------- ---------------- --------------------
MHAPPGEN 268 3504 15060 1 record(s) selected.
$ 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('MHHISTORYGEN',-2)) AS t"
TBSP_NAME NUM_EXTENTS_MOVED NUM_EXTENTS_LEFT TOTAL_MOVE_TIME
-------------------- ----------------- ---------------- --------------------
MHHISTORYGEN -1 -1 -1 1 record(s) selected.
查看文件系统磁盘空间使用情况df -g ，可以发现磁盘空间使用率降低了
$ df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 10.00 9.61 4% 19254 1% /
/dev/hd2 10.00 6.15 39% 61741 5% /usr
/dev/hd9var 10.00 6.94 31% 7091 1% /var
/dev/hd3 10.00 6.22 38% 665 1% /tmp
/dev/fwdump 2.00 2.00 1% 9 1% /var/adm/ras/platform
/dev/hd1 20.00 6.74 67% 3535 1% /home
/dev/hd11admin 1.00 1.00 1% 7 1% /admin
/proc - - - - - /proc
/dev/hd10opt 20.00 17.23 14% 12783 1% /opt
/dev/livedump 1.00 1.00 1% 4 1% /var/adm/ras/livedump
/dev/mqmlv02 5.00 4.86 3% 2319 1% /usr/mqm
/dev/mqmlv01 5.00 5.00 1% 75 1% /var/mqm
/dev/db2datalv 3000.00 601.89 80% 123 1% /db2data
/dev/db2loglv 20.00 18.43 8% 29 1% /db2log
/dev/commlv 40.00 35.58 12% 61037 1% /home/communicate
/dev/databaselv 600.00 567.85 6% 430554 1% /home/database
/dev/db2fenc1lv 10.00 10.00 1% 6 1% /home/db2fenc1
/dev/db2inst1lv 10.00 8.92 11% 2036 1% /home/db2inst1
/dev/mhweblv 20.00 12.80 37% 2366 1% /home/mhweb
/dev/mqloglv 5.00 2.50 51% 17 1% /var/mqm/DB18/log
/dev/qmgrslv 50.00 49.99 1% 153 1% /var/mqm/DB18/qmgrs执行完重组、降高水位后，需要执行一下runstats，更新执行计划，以便于提高查询效率。本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
