本文通过实例探讨了在数据库操作中，admin_move_table如何利用cluster index进行表重组。初始实验发现索引未被使用，经研究发现primary key只能为REG类型。通过创建CLUSTER索引，成功使admin_move_table在移动表后按id顺序排列。详细步骤包括创建测试表，插入数据，模拟插入、更新和删除操作，分析数据存储顺序，并观察INIT和COPY阶段的索引变化，最终在SWAP阶段实现id顺序排序。
最开始是发现文档中示例中：
COPY_OPTS BY_KEY,OVER_INDEX而我的实验结果却是：
COPY_OPTS OVER_INDEX,ARRAY_INSERT,NON_CLUSTER就是没有用到索引了哦，为什么？最后查看了一下syscat.indexes中的indextype:
Type of index.
- BLOK = Block index
- CLUS = Clustering index (controls the physical placement of newly inserted rows)
- REG = Regular index
应该id列是CLUS的索引才对，但是primary key只能是REG的，所以重新定义了CLUSTER索引，然后admin_move_table就能重组了，会把打乱的id顺序排序好。
修正版过程：
这次的表，字段多一些，建索引，然后表中行的顺序打乱一下，然后看看移动之后会否变的有顺序。
首先我们创建测试数据库和测试表：
[root@bogon tmp]# touch /opt/var/orgsp2
[root@bogon tmp]# chown db2inst1 /opt/var/orgsp2
db2 "create database testmove"
db2 connect to testmove
db2 "create tablespace orgsp2 managed by database using (FILE '/opt/var/orgsp2' 64M)"
db2 "create table t1 (id int not null primary key,name varchar(20), desc varchar(20)) IN orgsp2"
db2 "create table t2 (id int not null primary key,name varchar(20)) IN orgsp2"
db2 "create table t3 (id int not null primary key,name varchar(200)) IN orgsp2"
db2 "create table t4 (id int not null,name varchar(20),sex smallint,address varchar(200),education char(4)) IN orgsp2"
db2 "CREATE UNIQUE INDEX t4_pk_index ON db2inst1.t4(id) CLUSTER"
[db2inst1@db22 opt]$ db2 "select indname,indschema,tabname,tabschema,indextype,clusterratio from syscat.indexes where TABNAME IN ('T1','T2','T3','T4','T5')"
INDNAME INDSCHEMA TABNAME TABSCHEMA INDEXTYPE CLUSTERRATIO
------------------- ----------- --------- ---------- --------- ------------
SQL170705200828370 SYSIBM T1 DB2INST1 REG -1
SQL170705200828640 SYSIBM T2 DB2INST1 REG -1
SQL170705200828840 SYSIBM T3 DB2INST1 REG -1
T4_PK_INDEX DB2INST1 T4 DB2INST1 CLUS -1
4 record(s) selected.
插入初始化测试数据
db2 "insert into t4 with c1(col1) as (values(3000) union all select c1.col1 +1 from c1 where c1.col1<10800) select c1.col1,'t4LTT4','0','T4t4T4t4T4t4T4t4T4t4T4t4T4t4t4t4t4t4t4t4t4asdfskdfafasfsfsdfasdfjsadfla s sfdasdfsdfadsfas','benk' from c1"
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,'ltt1','this is table t1 ' from c1"
db2 "insert into t2 with c1(col1) as (values(10000) union all select c1.col1 +1 from c1 where c1.col1<21000) 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<13000) select c1.col1,'T3t3T3t3T3t3T3t3T3t3T3t3T3t3T3t3t3t3t3t3t3t3t3 sdflksadfsa dfjdslkfjsdfjaslkdfsdfsadlfkjsdlkfdsf' from c1"
... ... ...
Used pages = 1632
Free pages = 14720
High water mark (pages) = 1632
然后模拟t4表的插入更新和删除：
... ... ...
Used pages = 1920
Free pages = 14432
High water mark (pages) = 1920
这样：数据的存储顺序大体上是：
→3000~10800
→100~2000
→21000~23000
→16000~19600
→14400~15600
High water mark: 1920 pages, 60 extents (extents #0 - 59)
[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] 5 0x41* [0010] 5 0x01* [0011] 6 0x40*
[0012] 6 0x00* [0013] 6 0x41* [0014] 6 0x01* [0015] 7 0x40*
[0016] 7 0x00* [0017] 7 0x41* [0018] 7 0x01* [0019] 7 0x00
[0020] 7 0x00 [0021] 7 0x00 [0022] 7 0x00 [0023] 7 0x00
[0024] 7 0x00 [0025] 7 0x01 [0026] 7 0x00 [0027] 4 0x00
[0028] 4 0x00 [0029] 4 0x01 [0030] 4 0x00 [0031] 4 0x00
[0032] 4 0x00 [0033] 4 0x01 [0034] 4 0x00 [0035] 5 0x00
[0036] 5 0x01 [0037] 5 0x00 [0038] 5 0x00 [0039] 6 0x00
[0040] 6 0x00 [0041] 6 0x00 [0042] 6 0x00 [0043] 6 0x00
[0044] 6 0x00 [0045] 6 0x01 [0046] 6 0x00 [0047] 6 0x00
[0048] 6 0x00 [0049] 6 0x00 [0050] 6 0x00 [0051] 7 0x00
[0052] 7 0x00 [0053] 7 0x00 [0054] 7 0x00 [0055] 7 0x00
[0056] 7 0x01 [0057] 7 0x00 [0058] 7 0x00 [0059] 7 0x00
表7占据了15-26,51-59 这么多个EXTENT；
导一下表7的数据，然后看一下顺序：
db2dart testmove /DD /tsi 3 /oi 7 /ps 0 /np 0 /v y /rptn t4-initdata.dart
less t4-initdata.dart | grep 'Table Data Record' -A 10|grep 'Value ='根据表的导出顺序，可以看出插入记录的顺序就是存储的顺序
INIT
db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','INIT')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
STATUS COPY
AUTHID DB2INST1
VERSION 11.01.0101
INIT_START 2017-07-05-17.17.19.596661
INDEXSCHEMA SYSIBM
INDEXNAME SQL170705170714560
TARGET T4AAAAV0t
STAGING T4AAAAV0s
INIT_END 2017-07-05-17.17.23.134999
UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070517172319853800000000
10 record(s) selected.
Return Status = 0利用了集群索引：
[db2inst1@db22 opt]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','INIT')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
COPY_INDEXNAME T4_PK_INDEX
COPY_INDEXSCHEMA DB2INST1
INDEXNAME T4_PK_INDEX
INDEXSCHEMA DB2INST1
INIT_END 2017-07-05-20.16.56.562890
INIT_START 2017-07-05-20.16.54.655873
STAGING T4AAAAV0s
STATUS COPY
TARGET T4AAAAV0t
UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070520165660158000000000
VERSION 11.01.0101
12 record(s) selected.
Return Status = 0 [0060] 8 0x40* [0061] 8 0x00* [0062] 9 0x40* [0063] 9 0x00*
[0064] 9 0x41* [0065] 9 0x01*
只看最后几行可以看到INIT阶段初始化了目标表，staging table:
注意：初始化的目标表没有索引哦。staging table是有索引的。
[db2inst1@db22 tmp]$ db2 "select indname,indschema,tabschema,tabname,colnames from syscat.indexes where tabschema='DB2INST1'"
INDNAME INDSCHEMA TABSCHEMA TABNAME COLNAMES
------------------------ ----------- ----------- ----------- ----------
SQL170630153727970 SYSIBM DB2INST1 T1 +ID
T4AAAAV0sAFVT7zg DB2INST1 DB2INST1 T4AAAAV0s +ID
SQL170630153728320 SYSIBM DB2INST1 T2 +ID
SQL170630153728600 SYSIBM DB2INST1 T3 +ID
SQL170630153755800 SYSIBM DB2INST1 T4 +ID 可以看到登台表的索引列是id列。
COPY
使用了索引之后的：
[db2inst1@db22 opt]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','COPY')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
COPY_END 2017-07-05-20.20.48.713486
COPY_INDEXNAME T4_PK_INDEX
COPY_INDEXSCHEMA DB2INST1
COPY_OPTS OVER_INDEX,ARRAY_INSERT
COPY_START 2017-07-05-20.20.46.914488
COPY_TOTAL_ROWS 12468
INDEXNAME T4_PK_INDEX
INDEXSCHEMA DB2INST1
INDEX_CREATION_TOTAL_TIME 1
INIT_END 2017-07-05-20.16.56.562890
INIT_START 2017-07-05-20.16.54.655873
REPLAY_START 2017-07-05-20.20.48.714188
STAGING T4AAAAV0s
STATUS REPLAY
TARGET T4AAAAV0t
UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070520165660158000000000
VERSION 11.01.0101
18 record(s) selected.
Return Status = 0 [0060] 8 0x40* [0061] 8 0x00* [0062] 9 0x40* [0063] 9 0x00*
[0064] 9 0x41* [0065] 9 0x01* [0066] 8 0x41* [0067] 8 0x01*
[0068] 8 0x00 [0069] 8 0x00 [0070] 8 0x00 [0071] 8 0x00
[0072] 8 0x00 [0073] 8 0x00 [0074] 8 0x01 [0075] 8 0x00
[0076] 8 0x00 [0077] 8 0x00 [0078] 8 0x00 [0079] 8 0x00
可以看到，相比上一步，表8建了索引，随后表8占据了66-79这几个块。COPY阶段先建索引1。
现在审视一下表8的数据：
db2dart testmove /DD /tsi 3 /oi 8 /ps 0 /np 0 /v y /rptn t4td.dart
less t4td.dart | grep 'Table Data Record' -A 10|grep 'Value =' > orderornot.orderless less less orderornot.order
查看导出的数据，可以发现id是顺序的了。
执行SWAP:
[db2inst1@db22 opt]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','SWAP')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
CLEANUP_END 2017-07-05-20.26.22.800814
CLEANUP_START 2017-07-05-20.26.22.272712
COPY_END 2017-07-05-20.20.48.713486
COPY_INDEXNAME T4_PK_INDEX
COPY_INDEXSCHEMA DB2INST1
COPY_OPTS OVER_INDEX,ARRAY_INSERT
COPY_START 2017-07-05-20.20.46.914488
COPY_TOTAL_ROWS 12468
INDEXNAME T4_PK_INDEX
INDEXSCHEMA DB2INST1
INDEX_CREATION_TOTAL_TIME 1
INIT_END 2017-07-05-20.16.56.562890
INIT_START 2017-07-05-20.16.54.655873
ORIGINAL_TBLSIZE 2688
REPLAY_END 2017-07-05-20.26.21.776445
REPLAY_START 2017-07-05-20.20.48.714188
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 1
STATUS COMPLETE
SWAP_END 2017-07-05-20.26.22.189495
SWAP_RETRIES 0
SWAP_START 2017-07-05-20.26.21.854931
UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070520165660158000000000
VERSION 11.01.0101
25 record(s) selected.
Return Status = 0
High water mark: 2560 pages, 80 extents (extents #0 - 79)
[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] 5 0x41* [0010] 5 0x01* [0011] 6 0x40*
[0012] 6 0x00* [0013] 6 0x41* [0014] 6 0x01* [0015] == EMPTY ==
[0016] == EMPTY == [0017] == EMPTY == [0018] == EMPTY == [0019] == EMPTY ==
[0020] == EMPTY == [0021] == EMPTY == [0022] == EMPTY == [0023] == EMPTY ==
[0024] == EMPTY == [0025] == EMPTY == [0026] == EMPTY == [0027] 4 0x00
[0028] 4 0x00 [0029] 4 0x01 [0030] 4 0x00 [0031] 4 0x00
[0032] 4 0x00 [0033] 4 0x01 [0034] 4 0x00 [0035] 5 0x00
[0036] 5 0x01 [0037] 5 0x00 [0038] 5 0x00 [0039] 6 0x00
[0040] 6 0x00 [0041] 6 0x00 [0042] 6 0x00 [0043] 6 0x00
[0044] 6 0x00 [0045] 6 0x01 [0046] 6 0x00 [0047] 6 0x00
[0048] 6 0x00 [0049] 6 0x00 [0050] 6 0x00 [0051] == EMPTY ==
[0052] == EMPTY == [0053] == EMPTY == [0054] == EMPTY == [0055] == EMPTY ==
[0056] == EMPTY == [0057] == EMPTY == [0058] == EMPTY == [0059] == EMPTY ==
[0060] 8 0x40* [0061] 8 0x00* [0062] == EMPTY == [0063] == EMPTY ==
[0064] == EMPTY == [0065] == EMPTY == [0066] 8 0x41* [0067] 8 0x01*
[0068] 8 0x00 [0069] 8 0x00 [0070] 8 0x00 [0071] 8 0x00
[0072] 8 0x00 [0073] 8 0x00 [0074] 8 0x01 [0075] 8 0x00
[0076] 8 0x00 [0077] 8 0x00 [0078] 8 0x00 [0079] 8 0x00 
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
