使用begin new stripe set选项扩容表空间避免数据重新平衡（rebalance）

使用begin new stripe set选项扩容表空间避免数据重新平衡

一、表空间扩容的基本方法

表空间的扩容一般有两种方式：

一是增加容器的容量，resize或extend方式

（1）ALTER TABLESPACE 表空间名 RESIZE(FILE '已有容器名'   更改后容器的大小)

Example : resize the table space TS1 containers so that all of the
containers have 2000 pages.

ALTER TABLESPACE TS1
RESIZE (FILE '/conts/cont0' 2000,
DEVICE '/dev/rcont1' 2000,
FILE 'cont2' 2000)

（2）ALTER TABLESPACE 表空间名 EXTEND(FILE ''已有容器名'  准备增加的大小)
Example : Extend all of the containers in the DATA_TS table space by 1000 pages.
ALTER TABLESPACE DATA_TS
EXTEND (ALL 1000)

 

二是直接添加新容器方式

1，使用add

ADD
Specifies that one or more new containers are to be added to the table space.

Example 1: Add a device to the PAYROLL table space.
ALTER TABLESPACE PAYROLL
ADD (DEVICE '/dev/rhdisk9' 10000)

使用add扩容表空间后，数据库会自动对表空间中的数据在各容器之间进行重新平衡，这样数据分散在表空间所有容器中，包括新加的容器；但是reblance的时间和表空间数据量的大小，机器硬件的情况等不同，速度会有不同。reblance可能对机器，数据库，IO等影响，扩容表空间最好业务空闲时间做。

 

2，使用begin new stripe set

BEGIN NEW STRIPE SET
Specifies that a new stripe set is to be created in the table space, and that one
or more containers are to be added to this new stripe set. Containers that are
subsequently added using the ADD option will be added to this new stripe set
unless TO STRIPE SET is specified

如果想直接扩容表空间而不希望数据库自动对表空间进行reblance的话，就需要使用BEGIN NEW STRIPE SET选项来新增容器，这样的缺点是数据不会自动平衡到新增加的容器上。也就是说读取原有老数据可能会只访问老的容器，写数据时只往新增的容器写，不能在多容器直接进行IO的平衡。

 

二、使用ADD和BEGIN NEW STRIPE SET选项的扩容实例

以下就扩容一个分布在7个节点上的表空间TBS_AHLT，通过实例说明ADD和BEGIN NEW STRIPE SET选项的用法：

 

扩容前表空间其中一个节点空闲就剩下3M，随便一个操作及导致表空间撑满，导致日常操作无法正常进行。

2011-05-24-10.59.17.748134+480 I51062635A419      LEVEL: Error
PID     : 1532868              TID  : 1           PROC : db2agntp (BSSDB) 3
INSTANCE: db2inst1             NODE : 003
APPHDL  : 0-86                 APPID: 130.30.0.68.27653.110524020348
AUTHID  : PAPP    
FUNCTION: DB2 UDB, Common Trace API, sqlbfix, probe:2
MESSAGE : ZRC=0x85020021=-2063466463=SQLB_END_OF_CONTAINER
          "DMS Container space full"

很明显，这个表空间上表的分布键不合理，导致数据分布不均匀，这个具体调整不在本次讨论过程中。

TABLESPACE_ID        TBSPC_NAME           TOTAL(MB)            USED(MB)             FREE(MB)            
-------------------- -------------------- -------------------- -------------------- --------------------
                  14 TBS_AHLT                             8190                 4590                 3599
                  14 TBS_AHLT                             8190                 3835                 4354
                  14 TBS_AHLT                             8190                 8186                    3
                  14 TBS_AHLT                             8190                 8028                  161
                  14 TBS_AHLT                             8190                 6959                 1230
                  14 TBS_AHLT                             8190                 8006                  183
                  14 TBS_AHLT                             8190                 5257                 2932
                 

先对第3分区扩容一个容器，如下
[DWE3:/db2home/db2inst1/fengsh]db2 "alter tablespace tbs_ahlt ADD ( Device '/dev/rlv_8g_308' 8190M ) on dbpartitionnum(3)"    
DB20000I  The SQL command completed successfully.

扩容后，可以查询到3节点总空间已经16G，但是free的空间却是0，新增的容器空间暂时还无法使用
TABLESPACE_ID        TBSPC_NAME           TOTAL(MB)            USED(MB)             FREE(MB)            
-------------------- -------------------- -------------------- -------------------- --------------------
                  14 TBS_AHLT                             8190                 4593                 3596
                  14 TBS_AHLT                             8190                 3839                 4350
                  14 TBS_AHLT                            16380                 8189                    0
                  14 TBS_AHLT                             8190                 8032                  157
                  14 TBS_AHLT                             8190                 6963                 1226
                  14 TBS_AHLT                             8190                 8010                  179
                  14 TBS_AHLT                             8190                 5260                 2928

查看db2diag.log日志会发现新增容器后，数据库自动对表空间执行reblance操作
2011-05-24-12.22.18.201475+480 E51065844A641      LEVEL: Warning
PID     : 492028               TID  : 1           PROC : db2agntp (BSSDB) 3
INSTANCE: db2inst1             NODE : 003         DB   : BSSDB
APPHDL  : 0-1171               APPID: *N0.db2inst1.110524022609
AUTHID  : DB2INST1
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSAddContainerRequest, probe:105
MESSAGE : ADM6037W  Container "/dev/rlv_8g_308" was created to be "8386560" KB 
          in size on a device that is "8388608" KB in size.  Extra storage will 
          be wasted.  The container can be extended to use the wasted space by 
          using ALTER TABLESPACE.

2011-05-24-12.22.18.712560+480 E51067779A339      LEVEL: Warning
PID     : 1631182              TID  : 1           PROC : db2rebal 3
INSTANCE: db2inst1             NODE : 003
FUNCTION: DB2 UDB, buffer pool services, sqlb_fwd_rebalance, probe:2204
MESSAGE : ADM6058I  Rebalancer for table space "TBS_AHLT" (ID "14") was 
          started.

2011-05-24-12.32.26.331038+480 E51074949A341      LEVEL: Warning
PID     : 1631182              TID  : 1           PROC : db2rebal 3
INSTANCE: db2inst1             NODE : 003
FUNCTION: DB2 UDB, buffer pool services, sqlb_rebalance, probe:2876
MESSAGE : ADM6062I  Rebalance for table space "TBS_AHLT" (ID "14") has been 
          completed.

2011-05-24-12.32.26.335943+480 I51075291A310      LEVEL: Warning
PID     : 1631182              TID  : 1           PROC : db2rebal 3
INSTANCE: db2inst1             NODE : 003
FUNCTION: DB2 UDB, buffer pool services, sqlb_rebalance, probe:2876
DATA #1 : String, 39 bytes
PoolID 14: Last extent moved was #65518

 

 

使用begin new stripe set选项对tbs_ahlt表空间的其他几个节点扩容，为方便，可以将语句直接写到文件中执行
[DWE3:/db2home/db2inst1/fengsh/alter_tbs]cat alttbs_ahlt.sql     
alter tablespace TBS_AHLT  
        begin new stripe set ( Device '/dev/rlv_8g_108' 8190M)        ON DBPARTITIONNUM (1)
        begin new stripe set ( Device '/dev/rlv_8g_208' 8190M)        ON DBPARTITIONNUM (2)
        begin new stripe set ( Device '/dev/rlv_8g_608' 8190M)        ON DBPARTITIONNUM (6)
        begin new stripe set ( Device '/dev/rlv_8g_708' 8190M)        ON DBPARTITIONNUM (7);
        
执行扩容语句
[DWE3:/db2home/db2inst1/fengsh/alter_tbs]db2 -tvf alttbs_ahlt.sql
alter tablespace TBS_AHLT begin new stripe set ( Device '/dev/rlv_8g_108' 8190M)        ON DBPARTITIONNUM (1) begin new stripe set ( Device '/dev/rlv_8g_208' 8190M)          ON DBPARTITIONNUM (2) begin new stripe set ( Device '/dev/rlv_8g_608' 8190M)        ON DBPARTITIONNUM (6) begin new stripe set ( Device '/dev/rlv_8g_708' 8190M)        ON DBPARTITIONNUM (7)
DB20000I  The SQL command completed successfully.

查看表空间，可以看到表空间完成扩容，刚才新增容器容量也已经体现在空闲的空间上
[DWE3:/db2home/db2inst1/fengsh]db2 -tvf tbs.sh
.............

TABLESPACE_ID        TBSPC_NAME           TOTAL(MB)            USED(MB)             FREE(MB)            
-------------------- -------------------- -------------------- -------------------- --------------------
                  14 TBS_AHLT                            16380                 4593                11785
                  14 TBS_AHLT                            16380                 3839                12540
                  14 TBS_AHLT                            16380                 8189                 8189
                  14 TBS_AHLT                            16380                 8032                 8346
                  14 TBS_AHLT                            16380                 6963                 9416
                  14 TBS_AHLT                            16380                 8010                 8369
                  14 TBS_AHLT                            16380                 5260                11118
                 

诊断日志中可以看出数据库表空间的扩容，但是数据库没有对其执行reblance操作                  
2011-05-24-12.37.18.393126+480 E51076895A641      LEVEL: Warning
PID     : 1696702              TID  : 1           PROC : db2agntp (BSSDB) 2
INSTANCE: db2inst1             NODE : 002         DB   : BSSDB
APPHDL  : 0-1545               APPID: *N0.db2inst1.110524042409
AUTHID  : DB2INST1
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSAddContainerRequest, probe:105
MESSAGE : ADM6037W  Container "/dev/rlv_8g_208" was created to be "8386560" KB 
          in size on a device that is "8388608" KB in size.  Extra storage will 
          be wasted.  The container can be extended to use the wasted space by 
          using ALTER TABLESPACE.

2011-05-24-12.37.18.293263+480 E51077537A641      LEVEL: Warning
PID     : 790924               TID  : 1           PROC : db2agntp (BSSDB) 1
INSTANCE: db2inst1             NODE : 001         DB   : BSSDB
APPHDL  : 0-1545               APPID: *N0.db2inst1.110524042409
AUTHID  : DB2INST1
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSAddContainerRequest, probe:105
MESSAGE : ADM6037W  Container "/dev/rlv_8g_108" was created to be "8386560" KB 
          in size on a device that is "8388608" KB in size.  Extra storage will 
          be wasted.  The container can be extended to use the wasted space by 
          using ALTER TABLESPACE.

2011-05-24-12.37.18.759607+480 E51078179A641      LEVEL: Warning
PID     : 680196               TID  : 1           PROC : db2agntp (BSSDB) 7
INSTANCE: db2inst1             NODE : 007         DB   : BSSDB
APPHDL  : 0-1545               APPID: *N0.db2inst1.110524042409
AUTHID  : DB2INST1
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSAddContainerRequest, probe:105
MESSAGE : ADM6037W  Container "/dev/rlv_8g_708" was created to be "8386560" KB 
          in size on a device that is "8388608" KB in size.  Extra storage will 
          be wasted.  The container can be extended to use the wasted space by 
          using ALTER TABLESPACE.

2011-05-24-12.37.18.774371+480 E51078821A641      LEVEL: Warning
PID     : 832084               TID  : 1           PROC : db2agntp (BSSDB) 6
INSTANCE: db2inst1             NODE : 006         DB   : BSSDB
APPHDL  : 0-1545               APPID: *N0.db2inst1.110524042409
AUTHID  : DB2INST1
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSAddContainerRequest, probe:105
MESSAGE : ADM6037W  Container "/dev/rlv_8g_608" was created to be "8386560" KB 
          in size on a device that is "8388608" KB in size.  Extra storage will 
          be wasted.  The container can be extended to use the wasted space by 
          using ALTER TABLESPACE.