DB2数据库降低高水位方案

​​https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0055399.html​​​

如何确认高水位：​

db2pd -db testdb -tablespaces

如上图所示，这些表空间中的UsedPgs如果等于HWM，则无需降低高水位，反之则需要。可以评估
HWM-UsedPgs的差值，来决定是否有必要降低高水位。

查看是否是自动存储表空间

db2 get snapshot for tablespaces on testdb

1.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)"

此步骤非必须​

db2 "ALTER TABLESPACE TS1  extend (ALL CONTAINERS 10M)"

执行命令查看，高水位已经降低

2.自动存储表空间降高水位
如果看到Using automatic storage = Yes则是自动存储表空间。​

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.

或者查看表空间状态

db2 get snapshot for tablespaces on testdb | grep -i state

如果看到0x'00080000'，则处于Move状态
[db2inst1@db04 ~]$ db2tbst 0x'00080000'
State = Move in Progress

DB2 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