实验测试admin_move_table的机制（1）

最新推荐文章于 2024-06-10 23:55:29 发布
最新推荐文章于 2024-06-10 23:55:29 发布
阅读量1.5k 收藏
点赞数
分类专栏： DB2 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/nayanminxing/article/details/73998672
版权
本文通过实验详细分析了admin_move_table的机制，包括重建表空间中的数据，观察extent占用情况。实验涉及表的删除、重组，以及在线移动表操作，揭示了admin_move_table在不同阶段如何处理数据和索引，以及对空间的影响。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

  做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
4-1.此时表7共占据8个EXTENT，但是中间的EXTENT只有7个EXTENT哦，

此时使用admin_move_table不指定表空间

[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','INIT')"


  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-30-09.59.37.427887                                                                                                      
  INIT_START                       2017-06-30-09.59.35.606075                                                                                                      
  STAGING                          T4AAAAV0s                                                                                                                       
  STATUS                           COPY                                                                                                                            
  TARGET                           T4AAAAV0t                                                                                                                       
  UTILITY_INVOCATION_ID            0100000003000000080000000000000000002017063009593742951100000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  10 record(s) selected.

  Return Status = 0

[db2inst1@db22 tmp]$ db2 "select tabname,tableid,tbspaceid from syscat.tables where tabschema='DB2INST1'"
TABNAME                                                                                                                          TABLEID TBSPACEID
-------------------------------------------------------------------------------------------------------------------------------- ------- ---------
T1                                                                                                                                     4         3
T2                                                                                                                                     5         3
T3                                                                                                                                     6         3
T4                                                                                                                                     7         3
T4AAAAV0t                                                                                                                              8         3
T4AAAAV0s                                                                                                                              9         3

  6 record(s) selected.

db2 disconnect all
db2 deactivate db testorg
db2dart testorg /DHWM /tsi 3 /rptn testorg-4.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]     8 0x40*  [0023]     8 0x00* 
 [0024]     9 0x40*  [0025]     9 0x00*  [0026]     9 0x41*  [0027]     9 0x01* 
 [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

可以看到：
INIT阶段
- 先根据表7创建了目标表8，建表语句一样，且没有索引，所以8占据两个extent；
- 创建了staging 表9，初始化有索引所以占据4个extent，

下面进行拷贝阶段：

[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','COPY')"


  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  COPY_END                         2017-06-30-10.24.47.490292                                                                                                      
  COPY_OPTS                        ARRAY_INSERT,NON_CLUSTER                                                                                                        
  COPY_START                       2017-06-30-10.24.46.793731                                                                                                      
  COPY_TOTAL_ROWS                  7000                                                                                                                            
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-30-09.59.37.427887                                                                                                      
  INIT_START                       2017-06-30-09.59.35.606075                                                                                                      
  REPLAY_START                     2017-06-30-10.24.47.490860                                                                                                      
  STAGING                          T4AAAAV0s                                                                                                                       
  STATUS                           REPLAY                                                                                                                          
  TARGET                           T4AAAAV0t                                                                                                                       
  UTILITY_INVOCATION_ID            0100000003000000080000000000000000002017063009593742951100000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  15 record(s) selected.

  Return Status = 0

来看一下此时数据块的分布：

High water mark:  2464 pages, 77 extents (extents #0 - 76)


 [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]     8 0x40*  [0023]     8 0x00* 
 [0024]     9 0x40*  [0025]     9 0x00*  [0026]     9 0x41*  [0027]     9 0x01* 
 [0028]     8 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]     8 0x00   [0055]     8 0x00  
 [0056]     8 0x00   [0057]     8 0x00   [0058]     8 0x00   [0059]     8 0x41* 
 [0060]     8 0x01*  [0061]     8 0x01   [0062]     8 0x01   [0063]     8 0x01  
 [0064]     8 0x01   [0065]     8 0x01   [0066]     8 0x01   [0067]     8 0x01  
 [0068]     7 0x41*  [0069]     7 0x01*  [0070]     7 0x01   [0071]     7 0x01  
 [0072]     7 0x01   [0073]     7 0x01   [0074]     7 0x01   [0075]     7 0x01  
 [0076]     7 0x01

来看一下第二个步骤：
表7除第一个数据块的数据块占据了48,49,50,51,52,53，

表8除第一个数据块的数据块填充了28,54，55,56,57,57,58，

复制的表8多一个数据块呢。

然后第59个extent表8建了索引， 索引占据60*,61,62,63,64,65,66,67这几个extent，

原表7在68extent建了索引，索引占据extent69*,70,71,72,73,74,75,76。

那看到表8是比表7多一个数据块的，然后索引块是一样的。

表8为什么比表7多一个数据块呢？
表8和表7建的这个索引是什么呢？

我们看一下数据块的一些构造，用db2dart来审查表数据：

 db2dart testorg /DEMP /oi 7 /tsi 3 /rptn t4-2.dart

Action option: DEMP 
Table-object-ID: 7; Tablespace-ID: 3

DART formatted EMP page dump:
-----------------------------


   Traversing extent map for:
   Object ID: 7
   Table space: 3

   Object specific mapping info:
   -----------------------------
   DAT extent anchor: 416   416/32=13: extent和pages都是从0开始，所以page416是第14个块也就是extent13的第一个页面，这里存放7的表的EMP信息
   Traversing extent map for object type: 0
      Tablespace ID: 3, Tablespace Seed: 3, Object: 7 EMP page class: 64,
      EMP pool page: 416, # entries: 7  
      Page LSN = 000000000006E900
      Pool relative page #'s :
        448   1536   1568   1600   1632 
       1664   1696 
      Tablespace ID: 3, Tablespace Seed: 3, Object: 7 EMP page class: 64,
      EMP pool page: 417, # entries: 0
     ...  ...   ... 省略  
      EMP pool page: 447, # entries: 0
      Page LSN = 000000000004180F
      Pool relative page #'s :

   INX extent anchor: 2176  extent68上是表7的索引的位图信息
   Traversing extent map for object type: 1
      Tablespace ID: 3, Tablespace Seed: 3, Object: 7 EMP page class: 65,
      EMP pool page: 2176, # entries: 8
      Page LSN = 0000000000070B98
      Pool relative page #'s :
       2208   2240   2272   2304   2336 
       2368   2400   2432 
      Tablespace ID: 3, Tablespace Seed: 3, Object: 7 EMP page class: 65,
      EMP pool page: 2177, # entries: 0
      ...  ...  ...省略
      EMP pool page: 2207, # entries: 0
      Page LSN = 0000000000070A94
      Pool relative page #'s :

查看表7上的数据


 db2dart testorg /DD /tsi 3 /oi 7 /ps 0 /np 0 /v y /rptn t7-1.dart
 db2dart testorg /DD /tsi 3 /oi 8 /ps 0 /np 0 /v y /rptn t8-1.dart

对导出的文件使用Beyond Compare对比，发现表7有194个数据页，最后一页还有10个Deleted Record；表8最后一页为195页，最后一页只有一个slot。

查看索引：

[db2inst1@db22 tmp]$ db2 "select indname,indschema,tabschema,tabname from syscat.indexes where tabschema='DB2INST1'"

INDNAME                   INDSCHEMA     TABSCHEMA   TABNAME      COLNAMES     
------------------------- ------------- ----------- ------------ -------------
SQL170630095420990        SYSIBM        DB2INST1    T1           +ID          
SQL170630095421750        SYSIBM        DB2INST1    T3           +ID          
T4AAAAV0sAFVT7zg          DB2INST1      DB2INST1    T4AAAAV0s    +ID+NAME     
T4AAAAV0tAFVT70g          DB2INST1      DB2INST1    T4AAAAV0t    +ID+NAME     
T4AAAAV0g                 DB2INST1      DB2INST1    T4           +ID+NAME     

  5 record(s) selected.

可以看到因为T4表上没有索引，所以在T4表上建了ID+NAME的索引。

最后一步SWAP:

[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','SWAP')"


  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  CLEANUP_END                      2017-06-30-14.54.49.307639                                                                                                      
  CLEANUP_START                    2017-06-30-14.54.47.409582                                                                                                      
  COPY_END                         2017-06-30-10.24.47.490292                                                                                                      
  COPY_OPTS                        ARRAY_INSERT,NON_CLUSTER                                                                                                        
  COPY_START                       2017-06-30-10.24.46.793731                                                                                                      
  COPY_TOTAL_ROWS                  7000                                                                                                                            
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-30-09.59.37.427887                                                                                                      
  INIT_START                       2017-06-30-09.59.35.606075                                                                                                      
  ORIGINAL_TBLSIZE                 2176                                                                                                                            
  REPLAY_END                       2017-06-30-14.54.46.517775                                                                                                      
  REPLAY_START                     2017-06-30-10.24.47.490860                                                                                                      
  REPLAY_TOTAL_ROWS                0                                                                                                                               
  REPLAY_TOTAL_TIME                1                                                                                                                               
  STATUS                           COMPLETE                                                                                                                        
  SWAP_END                         2017-06-30-14.54.47.326936                                                                                                      
  SWAP_RETRIES                     0                                                                                                                               
  SWAP_START                       2017-06-30-14.54.46.545552                                                                                                      
  UTILITY_INVOCATION_ID            0100000003000000080000000000000000002017063009593742951100000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  22 record(s) selected.

  Return Status = 0

High water mark:  2464 pages, 77 extents (extents #0 - 76)


 [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] == EMPTY ==  [0014] == EMPTY ==  [0015]     4 0x00  
 [0016]     4 0x01   [0017]     4 0x00   [0018]     4 0x00   [0019]     4 0x00  
 [0020]     4 0x01   [0021]     4 0x00   [0022]     8 0x40*  [0023]     8 0x00* 
 [0024] == EMPTY ==  [0025] == EMPTY ==  [0026] == EMPTY ==  [0027] == EMPTY == 
 [0028]     8 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] == EMPTY ==  [0049] == EMPTY ==  [0050] == EMPTY ==  [0051] == EMPTY == 
 [0052] == EMPTY ==  [0053] == EMPTY ==  [0054]     8 0x00   [0055]     8 0x00  
 [0056]     8 0x00   [0057]     8 0x00   [0058]     8 0x00   [0059] == EMPTY == 
 [0060] == EMPTY ==  [0061] == EMPTY ==  [0062] == EMPTY ==  [0063] == EMPTY == 
 [0064] == EMPTY ==  [0065] == EMPTY ==  [0066] == EMPTY ==  [0067] == EMPTY == 
 [0068] == EMPTY ==  [0069] == EMPTY ==  [0070] == EMPTY ==  [0071] == EMPTY == 
 [0072] == EMPTY ==  [0073] == EMPTY ==  [0074] == EMPTY ==  [0075] == EMPTY == 
 [0076] == EMPTY ==

清空了staging table，清空了建的索引，完成替换，表的id为8.
这些empty的重新连接数据库后就降下来了。

High water mark:  1888 pages, 59 extents (extents #0 - 58)


 [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] == EMPTY ==  [0014] == EMPTY ==  [0015]     4 0x00  
 [0016]     4 0x01   [0017]     4 0x00   [0018]     4 0x00   [0019]     4 0x00  
 [0020]     4 0x01   [0021]     4 0x00   [0022]     8 0x40*  [0023]     8 0x00* 
 [0024] == EMPTY ==  [0025] == EMPTY ==  [0026] == EMPTY ==  [0027] == EMPTY == 
 [0028]     8 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] == EMPTY ==  [0049] == EMPTY ==  [0050] == EMPTY ==  [0051] == EMPTY == 
 [0052] == EMPTY ==  [0053] == EMPTY ==  [0054]     8 0x00   [0055]     8 0x00  
 [0056]     8 0x00   [0057]     8 0x00   [0058]     8 0x00

从extent的变化来看一下都发生了什么？
- 原来的表id7变成了8：4.SWAP阶段：存储过程会迅速使源表脱机，并将源表名称和索引名称指定给影子副本及其索引，然后，使影子表联机，从而替换源表。缺省情况下，会删除源表，但可以使用 KEEP 选项来以另一个名称保留该源表。这个是替换阶段，跟脱机表重组的替换是不一样的，这个admin_move_table在重组过程中是create新的表对象，跟脱机表重组的copy不一样的。
- 原来表7占据的13（EMP）,14（第一个数据块）EMPTY了，这两个块的位置移动到22和23了，这个这样理解：1.INIT阶段：创建源表的影子副本（shadow table），初始化的EMP和第一个数据块利用了空出来的EXTENT22和23
- 原来占据的其他6个数据块，一个在t2空出来的extent28上，其他5个新申请了extent。这个怎么发生的呢？
- 原表7占据的48-53EMPTY了。SWAP阶段最后删除源表。

实验2

3-2.现在删除表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
4-2.此时表7共占据8个EXTENT，但是中间的EXTENT只有7个EXTENT哦，

此时使用admin_move_table第二种方式

(1).第一步：首先手工创建表：

db2 "CREATE TABLE T4_TARGET (id int not null,name varchar(200)) IN orgsp1"
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]     8 0x40*  [0023]     8 0x00* 
 [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

(2).然后调用存储过程传入目标表的名称：

CALL SYSPROC.ADMIN_MOVE_TABLE(
'DB2INST1',       --tabschema
'T4',             --tabname
'T4_TARGET',      --target_tabname
'',               --V.options
'INIT'          --operation
)

[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','T4_TARGET','', 'INIT')"


  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-29-19.12.38.556102                                                                                                      
  INIT_START                       2017-06-29-19.12.36.453520                                                                                                      
  PAR_COLDEF                       using a supplied target table so COLDEF could be different                                                                      
  STAGING                          T4AAAAV0s                                                                                                                       
  STATUS                           COPY                                                                                                                            
  TARGET                           T4_TARGET                                                                                                                       
  UTILITY_INVOCATION_ID            0100000003000000080000000000000000002017062919123857894000000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  11 record(s) selected.

  Return Status = 0


 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]     8 0x40*  [0023]     8 0x00* 
 [0024]     9 0x40*  [0025]     9 0x00*  [0026]     9 0x41*  [0027]     9 0x01* 
 [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

从这里看出表9占据了24,25,26,27这几个块，这是个有索引的表。

[db2inst1@db22 tmp]$ db2 "select tabname,tableid from syscat.tables where tabschema='DB2INST1'"

TABNAME                                                                                                                          TABLEID
-------------------------------------------------------------------------------------------------------------------------------- -------
T1                                                                                                                                     4
T2                                                                                                                                     5
T3                                                                                                                                     6
T4                                                                                                                                     7
T4_TARGET                                                                                                                              8
T4AAAAV0s                                                                                                                              9

  6 record(s) selected.

表9应该是staging table。


[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','T4_TARGET','', 'COPY')"


  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  COPY_END                         2017-06-29-19.20.56.885452                                                                                                      
  COPY_OPTS                        ARRAY_INSERT,NON_CLUSTER                                                                                                        
  COPY_START                       2017-06-29-19.20.56.195036                                                                                                      
  COPY_TOTAL_ROWS                  7000                                                                                                                            
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-29-19.12.38.556102                                                                                                      
  INIT_START                       2017-06-29-19.12.36.453520                                                                                                      
  PAR_COLDEF                       using a supplied target table so COLDEF could be different                                                                      
  REPLAY_START                     2017-06-29-19.20.56.885893                                                                                                      
  STAGING                          T4AAAAV0s                                                                                                                       
  STATUS                           REPLAY                                                                                                                          
  TARGET                           T4_TARGET                                                                                                                       
  UTILITY_INVOCATION_ID            0100000003000000080000000000000000002017062919123857894000000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  16 record(s) selected.

  Return Status = 0

 [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]     8 0x40*  [0023]     8 0x00* 
 [0024]     9 0x40*  [0025]     9 0x00*  [0026]     9 0x41*  [0027]     9 0x01* 
 [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] == EMPTY ==  [0055] == EMPTY == 
 [0056] == EMPTY ==  [0057] == EMPTY ==  [0058] == EMPTY ==  [0059]     8 0x41* 
 [0060]     8 0x01*  [0061]     8 0x01   [0062]     8 0x01   [0063]     8 0x01  
 [0064]     8 0x01   [0065]     8 0x01   [0066]     8 0x01   [0067]     8 0x01  
 [0068]     7 0x41*  [0069]     7 0x01*  [0070]     7 0x01   [0071]     7 0x01  
 [0072]     7 0x01   [0073]     7 0x01   [0074]     7 0x01   [0075]     7 0x01  
 [0076]     7 0x01

COPY阶段完成之后，可以看到54-58是EMPTY的，54-58为什么是EMPTY我们的原表7是没有索引的，在copy阶段，表8有了索引，索引位图和第一个索引块占据了59,60号EXTENT。然后61，62,63,64,65,66,67这几个EXTENT都是索引哎。然后为原表7创建了索引，索引位图和索引块占据了9个EXTENT，和表8是一致的，这一步到底干了啥？

失败了。。因为这个admin_move_table是只能移动到另一个表空间的。同一个表空间中被报SQL2104N
The ADMIN_MOVE_TABLE procedure could not be completed at this time by this user.
Explanation：从一个表空间移动到另一个表空间：You can use the SYSPROC.ADMIN_MOVE_TABLE procedure to move data from one table space to a another table space.

但联机移动表的一些规则可见一斑了。
先复原：
operation为CANCEL：

High water mark:  2464 pages, 77 extents (extents #0 - 76)


 [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]     8 0x40*  [0023]     8 0x00* 
 [0024] == EMPTY ==  [0025] == EMPTY ==  [0026] == EMPTY ==  [0027] == EMPTY == 
 [0028]     8 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]     8 0x00   [0055]     8 0x00  
 [0056]     8 0x00   [0057]     8 0x00   [0058]     8 0x00   [0059]     8 0x41* 
 [0060]     8 0x01*  [0061]     8 0x01   [0062]     8 0x01   [0063]     8 0x01  
 [0064]     8 0x01   [0065]     8 0x01   [0066]     8 0x01   [0067]     8 0x01  
 [0068] == EMPTY ==  [0069] == EMPTY ==  [0070] == EMPTY ==  [0071] == EMPTY == 
 [0072] == EMPTY ==  [0073] == EMPTY ==  [0074] == EMPTY ==  [0075] == EMPTY == 
 [0076] == EMPTY ==

然后调用cleanup： 额，SQL2104N The ADMIN_MOVE_TABLE procedure could not be completed at this time by this user. Reason code: “8”. SQLSTATE=5UA0M
原因码8表示：No online move table operation is in progress; therefore, the specified operation is not allowed.
删除drop table t4_target之后：

High water mark:  2176 pages, 68 extents (extents #0 - 67)


 [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] == EMPTY ==  [0055] == EMPTY == 
 [0056] == EMPTY ==  [0057] == EMPTY ==  [0058] == EMPTY ==  [0059] == EMPTY == 
 [0060] == EMPTY ==  [0061] == EMPTY ==  [0062] == EMPTY ==  [0063] == EMPTY == 
 [0064] == EMPTY ==  [0065] == EMPTY ==  [0066] == EMPTY ==  [0067] == EMPTY ==

这些怎么截断？在线重组7试试：
额，不行哦。
后面就没有了，可能是需要等一段时间。

嗯，使用不同的表空间再来看一下：

#创建一个新的表空间
[root@db22 opt]# touch /opt/var/orgsp2
[root@db22 opt]# chown db2inst1 /opt/var/orgsp2

(1).第一步：首先手工创建表：

db2 "create tablespace orgsp2 managed by database using (FILE '/opt/var/orgsp2' 64M)"
db2 "CREATE TABLE T4_TARGET (id int not null,name varchar(200)) IN orgsp2"
tsi:5
High water mark:  160 pages, 5 extents (extents #0 - 4)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*

(2).然后调用存储过程传入目标表的名称：

CALL SYSPROC.ADMIN_MOVE_TABLE(
'DB2INST1',       --tabschema
'T4',             --tabname
'T4_TARGET',      --target_tabname
'',               --V.options
'INIT'          --operation
)

[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','T4_TARGET','', 'INIT')"
[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','T4_TARGET','', 'INIT')"


  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-29-20.18.39.280067                                                                                                      
  INIT_START                       2017-06-29-20.18.36.568798                                                                                                      
  PAR_COLDEF                       using a supplied target table so COLDEF could be different                                                                      
  STAGING                          T4AAAAV0s                                                                                                                       
  STATUS                           COPY                                                                                                                            
  TARGET                           T4_TARGET                                                                                                                       
  UTILITY_INVOCATION_ID            0100000005000000080000000000000000002017062920183928830600000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  11 record(s) selected.

  Return Status = 0

tsi:5

High water mark:  288 pages, 9 extents (extents #0 - 8)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005]     5 0x40*  [0006]     5 0x00*  [0007]     5 0x41* 
 [0008]     5 0x01*

tsi:3 的extent没什么变化，高水位到extent53了。

从这里看出表9占据了24,25,26,27这几个块，这是个有索引的表。


[db2inst1@db22 tmp]$ db2 "select tabname,tableid,tbspaceid from syscat.tables where tabschema='DB2INST1'"

TABNAME                                                                                                                    TABLEID TBSPACEID
-------------------------------------------------------------------------------------------------------------------------------- ------- ---------
T1                                                                                                                                     4         3
T2                                                                                                                                     5         3
T3                                                                                                                                     6         3
T4                                                                                                                                     7         3
T4_TARGET                                                                                                                              4         5
T4AAAAV0s                                                                                                                              5         5

  6 record(s) selected.



[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','T4_TARGET','', 'COPY')"

  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  COPY_END                         2017-06-29-20.27.57.617520                                                                                                      
  COPY_OPTS                        ARRAY_INSERT,NON_CLUSTER                                                                                                        
  COPY_START                       2017-06-29-20.27.56.872079                                                                                                      
  COPY_TOTAL_ROWS                  7000                                                                                                                            
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-29-20.18.39.280067                                                                                                      
  INIT_START                       2017-06-29-20.18.36.568798                                                                                                      
  PAR_COLDEF                       using a supplied target table so COLDEF could be different                                                                      
  REPLAY_START                     2017-06-29-20.27.57.618341                                                                                                      
  STAGING                          T4AAAAV0s                                                                                                                       
  STATUS                           REPLAY                                                                                                                          
  TARGET                           T4_TARGET                                                                                                                       
  UTILITY_INVOCATION_ID            0100000005000000080000000000000000002017062920183928830600000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  16 record(s) selected.

  Return Status = 0

tsi:5

High water mark:  768 pages, 24 extents (extents #0 - 23)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005]     5 0x40*  [0006]     5 0x00*  [0007]     5 0x41* 
 [0008]     5 0x01*  [0009]     4 0x00   [0010]     4 0x00   [0011]     4 0x00  
 [0012]     4 0x00   [0013]     4 0x00   [0014]     4 0x00   [0015]     4 0x41* 
 [0016]     4 0x01*  [0017]     4 0x01   [0018]     4 0x01   [0019]     4 0x01  
 [0020]     4 0x01   [0021]     4 0x01   [0022]     4 0x01   [0023]     4 0x01

tsi:3

High water mark:  1792 pages, 56 extents (extents #0 - 55)


 [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]     7 0x41*  [0023]     7 0x01* 
 [0024]     7 0x01   [0025]     7 0x01   [0026]     7 0x01   [0027]     7 0x01  
 [0028]     7 0x01   [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 0x01   [0055]     7 0x01

大发现：可以看到22-28的位置建了表7的索引，然后索引占用了8个EXTENT，然后还又新申请了两个。

然后进行move截断：

额，还是不行啊。。。。。。。。。。。。

SQL2104N The ADMIN_MOVE_TABLE procedure could not be completed at this time
by this user. Reason code: "4". SQLSTATE=5UA0M

明天再来看吧。

------おはよう～宝宝来上班了---------------------
~~今天就是先测试把move table能跑通吧。然后看了下文档，发现最后一步是SWAP。。。。。。。。。。。~~

[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','T4_TARGET','', 'SWAP')"


  Result set 1
  --------------

  KEY                              VALUE                                                                                                                           
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  CLEANUP_END                      2017-06-30-09.18.00.486982                                                                                                      
  CLEANUP_START                    2017-06-30-09.17.58.933167                                                                                                      
  COPY_END                         2017-06-29-20.27.57.617520                                                                                                      
  COPY_OPTS                        ARRAY_INSERT,NON_CLUSTER                                                                                                        
  COPY_START                       2017-06-29-20.27.56.872079                                                                                                      
  COPY_TOTAL_ROWS                  7000                                                                                                                            
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2017-06-29-20.18.39.280067                                                                                                      
  INIT_START                       2017-06-29-20.18.36.568798                                                                                                      
  ORIGINAL_TBLSIZE                 2176                                                                                                                            
  PAR_COLDEF                       using a supplied target table so COLDEF could be different                                                                      
  REPLAY_END                       2017-06-30-09.17.58.161682                                                                                                      
  REPLAY_START                     2017-06-29-20.27.57.618341                                                                                                      
  REPLAY_TOTAL_ROWS                0                                                                                                                               
  REPLAY_TOTAL_TIME                2                                                                                                                               
  STATUS                           COMPLETE                                                                                                                        
  SWAP_END                         2017-06-30-09.17.58.836838                                                                                                      
  SWAP_RETRIES                     0                                                                                                                               
  SWAP_START                       2017-06-30-09.17.58.203611                                                                                                      
  UTILITY_INVOCATION_ID            0100000005000000080000000000000000002017062920183928830600000000                                                                
  VERSION                          11.01.0101                                                                                                                      

  23 record(s) selected.

  Return Status = 0

tsi：5

High water mark:  768 pages, 24 extents (extents #0 - 23)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005] == EMPTY ==  [0006] == EMPTY ==  [0007] == EMPTY == 
 [0008] == EMPTY ==  [0009]     4 0x00   [0010]     4 0x00   [0011]     4 0x00  
 [0012]     4 0x00   [0013]     4 0x00   [0014]     4 0x00   [0015] == EMPTY == 
 [0016] == EMPTY ==  [0017] == EMPTY ==  [0018] == EMPTY ==  [0019] == EMPTY == 
 [0020] == EMPTY ==  [0021] == EMPTY ==  [0022] == EMPTY ==  [0023] == EMPTY ==

可以看到删除了staging table；删除了在表t4上创建的索引。

tsi：3

High water mark:  1792 pages, 56 extents (extents #0 - 55)


 [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] == EMPTY ==  [0014] == EMPTY ==  [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] == EMPTY ==  [0049] == EMPTY ==  [0050] == EMPTY ==  [0051] == EMPTY == 
 [0052] == EMPTY ==  [0053] == EMPTY ==  [0054] == EMPTY ==  [0055] == EMPTY ==

可以看到删除了索引数据。

alter table move和 shrink space
lijingkuan的博客
04-21 7452
欢迎使用Markdown编辑器写博客本Markdown编辑器使用StackEdit修改而来，用它写博客，将会带来全新的体验哦： Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和图片上传 LaTex数学公式 UML序列图和流程图 离线写博客 导入导出Markdown文件 丰富的快捷键 快捷键 加粗 Ctrl + B 斜体 Ctrl + I 引用 Ctrl
oracle move作用,Oracle move和shrink释放高水位空间
weixin_33564130的博客
04-08 768
move 和shrink 的共同点1、收缩段2、消除部分行迁移3、消除空间碎片4、使数据更紧密一、shrink语法：alter table TABLE_NAME shrink space [compact|cascate]segment shrink执行的两个阶段：1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即...
DB2 admin_move_table
nayanminxing的专栏
06-21 1696
REORG TABLE1.ADMIN_MOVE_TABLE的原理/机制admin_move_table过程ADMIN_MOVE_TABLE 过程-在线移动表ADMIN_MOVE_TABLE存储过程将数据从一个活动表 移动到一个相同名称的新表对象中，而被移动的这个表在这个过程中是联机且可以被访问的。这个存储过程创建一个协议表（protocol table），这个协议表由包含 与将被移动的表相关的
ADMIN_MOVE_TABLE总结
liujinwei2005的专栏
10-20 939
=========================================介绍: ADMIN_MOVE_TABLE 过程-在线移动表 ADMIN_MOVE_TABLE存储过程将数据从一个活动表 移动到一个相同名称的新表对象中，而被移动的这个表在这个过程中是联机且可以被访问的。这个存储过程创建一个协议表（protocol table）， 这个协议表由包含 与将被移动的表相关的 状态信息和配置选项 构成。这个过程的返回集是 行集合，该行集合 来自 与将要被移动的表相关的 协议表 。 这个存储过程使用
DB2在线移动表ADMIN_MOVE_TABLE
TUBER727的专栏
09-11 2877
目前大多企业 DB2 生产环境都要求高可用，不能承担片刻停机带来的损失。当 DBA 希望将表从一个表空间移动到另一个表空间中，在 DB2 V9.7 以前的版本中要实现这个目标比较复杂，总有一段时间表对外是不可用的。DB2 V9.7 提供了 ADMIN_MOVE_TABLE 存储过程将表中的数据移至名称相同的新表对象，而新表是存放在不同的表空间上具有不同的存储特征，同时数据保持联机可供访问。我们还可
DB2 ADMIN_MOVE_TABLE 表移动
cuiyong8723的博客
03-28 388
ADMIN_MOVE_TABLE 移动到其他表空间 db2 "call sysproc.admin_move_table( 'SCHEMA_1', 'TABLE_1', 'TS2', 'TS2' , 'TS...
admin_move_table的重组机制验证（失败了）
nayanminxing的专栏
07-01 1172
这次的表，字段多一些，建索引，然后表中行的顺序打乱一下，然后看看移动之后会否变的有顺序。首先我们创建测试数据库和测试表：[root@bogon tmp]# touch /opt/var/orgsp2 [root@bogon tmp]# chown db2inst1 /opt/var/orgsp2db2 "create database testmove"db2 connect to testmove
HBase：客户端API之Admin与Table类的操作
Jodness' Blogs
07-11 3313
目录 连接HBase Table类：CRUD put 单行put 客户端的写缓冲区 多行Put 原子性Put get 单行Get Result类 多行Get delete 单行delete 多行delete 原子性Delete 批量处理操作 扫描 Scan ResultScanner 缓存和批量处理 Admin类：管理类操作 连接HBase 在H...
Hbase遇到的问题Unable to move table from temp重新格式化hbase
young_so_nice的博客
05-14 3649
一，问题：Unable to move table from temp=hdfs://master:9000/hbase/.tmp/data/default/scores2 to hbase root=hdfs://master:9000/hbase/data/default/scores2 at sun.reflect.NativeConstructorAccessorImpl.newInstan
蓝桥杯软件测试第十五届蓝桥杯模拟赛1期题目解析
最新发布
m0_75269526的博客
06-10 1650
蓝桥杯软件测试，蓝桥杯​，软件测试，蓝桥杯模拟赛，模拟赛1期， 部署完毕后请熟悉【蓝桥后台管理系统】界面和功能需求，针对指定的功能模块进行测试用例的设计和缺陷的寻找，并把测试用例和缺陷报告填写到指定的 excel 文件中。对首页的【登录】功能进行测试用例的设计，请设计 6 条测试用例达到该方法的有效覆盖。路径 5 ：1 - 2
admin_move_table在新添加删除字段
04-23 233
admin_move_table在线添加删除字段.txt IBM Knowledge Center - 通过使用 ADMIN_MOVE_TABLE 过程来以联机方式移动表.pdf https://www.ibm....
admin_move_table在线更改分区键
07-02 225
db2 "call sysproc.admin_move_table( 'RECV', 'TRANS_SERIAL_TABLE', '', '', '', '', '', '(CREATE_TIME) (PART...
admin_move_table的重组机制验证（cluster index）
nayanminxing的专栏
07-05 607
最开始是发现文档中示例中：COPY_OPTS BY_KEY,OVER_INDEX而我的实验结果却是：COPY_OPTS OVER_INDEX,ARRAY_INSERT,NON_CLUSTER就是没有用到索引了哦，为什么？最后查看了一下syscat.indexes中的indextype: Type of index.
DB2 的REORG_学习（2）_表重组
nayanminxing的专栏
06-27 8934
学习完命令之后来看一下表重组，然后再看索引重组哈。 表重组的方法DB2 V8之后有两种不同的表重组的方法： 脱机REORG 联机REORG REORG命令的INPLACE选项指定联机重组。如果未指定此选项，那么将运行脱机REORG。可以通过两种方法来重组表：传统重组（脱机）和原位置重组（联机）。1).缺省行为是脱机重组。 2).要指定联机重组操作，请使用 REORG TABLE 命令的 INPLA
关于解决The Operation Couldn't be Completed
热门推荐
haiooh的博客
05-14 4万+
运行的时候报错如图 运行编译代码成功，但是登陆模拟器失败，显示错误:The Operation couldn't be completed.(LaunchServicesError error 0.) 我上网查了一下，我试过第一1，2个方法 解决办法有：第1种方法.点击当前的模拟器，点击IOS Simulator->Reset Content and Settings
BeyondAdmin、Bootstrap样式的权限树选择结构（JAVA版）
qq_39035314的博客
04-10 2697
1.前台HTML&lt;div class="form-group"&gt; &lt;label for="rules" class="col-sm-2 control-label no-padding-right"&gt;模块&lt;/label&gt; &lt;div class="col-sm-6"&
页面上实现表格数据排序
BEYOND的博客
03-21 2867
源码：&lt;!DOCTYPE html&gt;&lt;html&gt;&lt;head&gt; &lt;style&gt; .table{ width: 400px; height: 180px; margin: auto; text-align: center; } .table th, .table td { width: 50px; height: 60p...
{"requestId":"reqId-2ed0a031f2159b208dbb2-4c4de07b-2","state":"EXCEPTION","errorCode":"USERMGT_USER_BIZ_ILLEGAL_ADMIN"}
07-28
引用\[1\]和\[2\]提供了关于七牛云上传图片报错的信息，错误提示为"incorrect region, please use up-z2.qiniup.com"。这个错误提示意味着使用了错误的上传区域。根据引用\[3\]提供的信息，七牛云有不同的上传区域，包括华东、华北、华南、北美等。正确的上传区域应该是up-z2.qiniup.com。因此，你需要将上传区域设置为up-z2.qiniup.com来解决这个问题。 至于你提到的{"requestId":"reqId-2ed0a031f2159b208dbb2-4c4de07b-2","state":"EXCEPTION","errorCode":"USERMGT_USER_BIZ_ILLEGAL_ADMIN"}错误，这是一个用户管理相关的错误，可能是由于非法的管理员操作引起的。你可能需要检查你的管理员权限和操作是否合法来解决这个问题。 #### 引用[.reference_title] - *1* [{ResponseInfo:com.qiniu.http.Response@6b4a4e18,status:400, reqId:B_QAAAAcV57ASXEW, xlog:X-Log, xvia:](https://blog.youkuaiyun.com/qq_37095305/article/details/115342843)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [{ResponseInfo:com.qiniu.http.Response@62bd765,status:400, reqId:d4kAAACMt2hWMSEW, xlog:X-Log, xvia:,](https://blog.youkuaiyun.com/Fat0Mouse/article/details/107313719)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值