实验测试admin_move_table的机制（1）

  做reorg的目的主要是为了对数据重新排列以节省不必要的空间使用，同时可以减少刷写缓冲区的次数，提高性能。降低空间占用通过降低表空间高水位来实现。为了对各种reorg的情况进行分析，特别进行了一个实验测试。

1.首先，建立一个测试用的数据库，并建立1个表空间进行测试分析,表空间内建4个表，并插入一些数据。然后查看extent占用。

[root@bogon tmp]# touch /opt/var/orgsp1
[root@bogon tmp]# chown db2inst1 /opt/var/orgsp1

db2 "create database testorg"

db2 connect to testorg

db2 "create tablespace orgsp1 managed by database using (FILE '/opt/var/orgsp1' 64M)"

db2 "create table t1 (id int not null primary key,name varchar(20)) IN orgsp1"

db2 "create table t2 (id int not null,name varchar(20)) IN orgsp1"

db2 "create table t3 (id int not null primary key,name varchar(200)) IN orgsp1"

db2 "create table t4 (id int not null,name varchar(200)) IN orgsp1"
DB20000I  The SQL command completed successfully.

db2 "insert into t1 with c1(col1) as (values(1) union all select c1.col1 +1 from c1 where c1.col1<18000) select c1.col1,'this is table t1 ' from c1"

db2 "insert into t2 with c1(col1) as (values(1) union all select c1.col1 +1 from c1 where c1.col1<25000) select c1.col1,'this is table t2 ' from c1"

db2 "insert into t3 with c1(col1) as (values(1) union all select c1.col1 +1 from c1 where c1.col1<20000) select c1.col1,'T3t3T3t3T3t3T3t3T3t3T3t3T3t3T3t3t3t3t3t3t3t3t3 sdflksadfsa dfjdslkfjsdfjaslkdfsdfsadlfkjsdlkfdsf' from c1"

db2 "insert into t4 with c1(col1) as (values(1) union all select c1.col1 +1 from c1 where c1.col1<30000) select c1.col1,'T4t4T4t4T4t4T4t4T4t4T4t4T4t4t4t4t4t4t4t4t4asdfskdfafasfsfsdfasdfjsadfla s sfdasdfsdfadsfas' from c1"

db2 list tablespaces show detail
……
Tablespace ID                        = 3
 Name                                 = ORGSP1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 16384
 Useable pages                        = 16352
 Used pages                           = 2368
 Free pages                           = 13984
 High water mark (pages)              = 2368
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

现在，已用页2368，高水位2368，现在来看一下它的占用的区块：

db2 disconnect all
db2 deactivate db testorg
db2dart testorg /DHWM /tsi 3 /rptn testorg-1.dart

High water mark:  2368 pages, 74 extents (extents #0 - 73)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005]     4 0x41*  [0006]     4 0x01*  [0007]     5 0x40* 
 [0008]     5 0x00*  [0009]     6 0x40*  [0010]     6 0x00*  [0011]     6 0x41* 
 [0012]     6 0x01*  [0013]     7 0x40*  [0014]     7 0x00*  [0015]     4 0x00  
 [0016]     4 0x01   [0017]     4 0x00   [0018]     4 0x00   [0019]     4 0x00  
 [0020]     4 0x01   [0021]     4 0x00   [0022]     5 0x00   [0023]     5 0x00  
 [0024]     5 0x00   [0025]     5 0x00   [0026]     5 0x00   [0027]     5 0x00  
 [0028]     5 0x00   [0029]     6 0x00   [0030]     6 0x00   [0031]     6 0x00  
 [0032]     6 0x00   [0033]     6 0x00   [0034]     6 0x00   [0035]     6 0x01  
 [0036]     6 0x00   [0037]     6 0x00   [0038]     6 0x00   [0039]     6 0x00  
 [0040]     6 0x00   [0041]     6 0x00   [0042]     6 0x01   [0043]     6 0x00  
 [0044]     6 0x00   [0045]     6 0x00   [0046]     6 0x00   [0047]     6 0x00  
 [0048]     7 0x00   [0049]     7 0x00   [0050]     7 0x00   [0051]     7 0x00  
 [0052]     7 0x00   [0053]     7 0x00   [0054]     7 0x00   [0055]     7 0x00  
 [0056]     7 0x00   [0057]     7 0x00   [0058]     7 0x00   [0059]     7 0x00  
 [0060]     7 0x00   [0061]     7 0x00   [0062]     7 0x00   [0063]     7 0x00  
 [0064]     7 0x00   [0065]     7 0x00   [0066]     7 0x00   [0067]     7 0x00  
 [0068]     7 0x00   [0069]     7 0x00   [0070]     7 0x00   [0071]     7 0x00  
 [0072]     7 0x00   [0073]     7 0x00  

2.现在我们删除t2的数据，对t2进行使用系统临时表空间重组或联机重组，使t2的extent空余出来。

db2 connect to testorg
db2 "delete from t2"
db2 "reorg table  t2 inplace"
db2 disconnect all
db2 deactivate db testorg
db2dart testorg /DHWM /tsi 3 /rptn testorg-2.dart

结果如下：

High water mark:  2368 pages, 74 extents (extents #0 - 73)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005]     4 0x41*  [0006]     4 0x01*  [0007]     5 0x40* 
 [0008]     5 0x00*  [0009]     6 0x40*  [0010]     6 0x00*  [0011]     6 0x41* 
 [0012]     6 0x01*  [0013]     7 0x40*  [0014]     7 0x00*  [0015]     4 0x00  
 [0016]     4 0x01   [0017]     4 0x00   [0018]     4 0x00   [0019]     4 0x00  
 [0020]     4 0x01   [0021]     4 0x00   [0022] == EMPTY ==  [0023] == EMPTY == 
 [0024] == EMPTY ==  [0025] == EMPTY ==  [0026] == EMPTY ==  [0027] == EMPTY == 
 [0028] == EMPTY ==  [0029]     6 0x00   [0030]     6 0x00   [0031]     6 0x00  
 [0032]     6 0x00   [0033]     6 0x00   [0034]     6 0x00   [0035]     6 0x01  
 [0036]     6 0x00   [0037]     6 0x00   [0038]     6 0x00   [0039]     6 0x00  
 [0040]     6 0x00   [0041]     6 0x00   [0042]     6 0x01   [0043]     6 0x00  
 [0044]     6 0x00   [0045]     6 0x00   [0046]     6 0x00   [0047]     6 0x00  
 [0048]     7 0x00   [0049]     7 0x00   [0050]     7 0x00   [0051]     7 0x00  
 [0052]     7 0x00   [0053]     7 0x00   [0054]     7 0x00   [0055]     7 0x00  
 [0056]     7 0x00   [0057]     7 0x00   [0058]     7 0x00   [0059]     7 0x00  
 [0060]     7 0x00   [0061]     7 0x00   [0062]     7 0x00   [0063]     7 0x00  
 [0064]     7 0x00   [0065]     7 0x00   [0066]     7 0x00   [0067]     7 0x00  
 [0068]     7 0x00   [0069]     7 0x00   [0070]     7 0x00   [0071]     7 0x00  
 [0072]     7 0x00   [0073]     7 0x00  

实验1

3-1.现在删除表7的部分数据，id > 7000

db2 connect to testorg
db2 "delete from t4 where id > 7000"
db2 "reorg table  t4 inplace"
db2 list tablespaces show detail
... ...
 Used pages                           = 1504
 High water mark (pages)              = 1728
... ...

db2 disconnect all
db2 deactivate db testorg
db2dart testorg /DHWM /tsi 3 /rptn testorg-3.dart

High water mark:  1728 pages, 54 extents (extents #0 - 53)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005]     4 0x41*  [0006]     4 0x01*  [0007]     5 0x40* 
 [0008]     5 0x00*  [0009]     6 0x40*  [0010]     6 0x00*  [0011]     6 0x41* 
 [0012]     6 0x01*  [0013]     7 0x40*  [0014]     7 0x00*  [0015]     4 0x00  
 [0016]     4 0x01   [0017]     4 0x00   [0018]     4 0x00   [0019]     4 0x00  
 [0020]     4 0x01   [0021]     4 0x00   [0022] == EMPTY ==  [0023] == EMPTY == 
 [0024] == EMPTY ==  [0025] == EMPTY ==  [0026] == EMPTY ==  [0027] == EMPTY == 
 [0028] == EMPTY ==  [0029]     6 0x00   [0030]     6 0x00   [0031]     6 0x00  
 [0032]     6 0x00   [0033]     6 0x00   [0034]     6 0x00   [0035]     6 0x01  
 [0036]     6 0x00   [0037]     6 0x00   [0038]     6 0x00   [0039]     6 0x00  
 [0040]     6 0x00   [0041]     6 0x00   [0042]     6 0x01   [0043]     6 0x00  
 [0044]     6 0x00   [0045]     6 0x00   [0046]     6 0x00   [0047]     6 0x00  
 [0048]     7 0x00   [0049]     7 0x00   [0050]     7 0x00   [0051]     7 0x00  
 [0052]     7 0x00   [0053]     7 0x00