本文详细介绍了在DB2环境下如何创建测试数据库及表，并通过不同的数据插入方式使表中的数据顺序变得杂乱。随后，利用DB2的admin_move_table过程进行表重组实验，探讨其是否能按主键排序。最终发现，若要实现排序效果，需建立集群索引。
这次的表，字段多一些，建索引，然后表中行的顺序打乱一下，然后看看移动之后会否变的有顺序。
首先我们创建测试数据库和测试表：
[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 primary key,name varchar(20),sex smallint,address varchar(200),education char(4)) IN orgsp2"
db2 "insert into t4 with c1(col1) as (values(3000) union all select c1.col1 +1 from c1 where c1.col1<3800) 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(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"
对表4做一些处理使表4id乱七八糟的哈：
Used pages = 11008
Free pages = 5344
High water mark (pages) = 11008
High water mark: 11008 pages, 344 extents (extents #0 - 343)
[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] 7 0x00
[0028] 7 0x00 [0029] 7 0x00 [0030] 7 0x00 [0031] 7 0x00
[0032] 7 0x00 [0033] 7 0x00 [0034] 7 0x01 [0035] 7 0x00
[0036] 7 0x00 [0037] 7 0x00 [0038] 7 0x00 [0039] 7 0x00
[0040] 7 0x00 [0041] 7 0x00 [0042] 7 0x00 [0043] 7 0x01
[0044] 7 0x00 [0045] 7 0x00 [0046] 7 0x00 [0047] 7 0x00
[0048] 7 0x00 [0049] 7 0x00 [0050] 7 0x00 [0051] 7 0x00
[0052] 7 0x01 [0053] 4 0x00 [0054] 4 0x00 [0055] 4 0x01
[0056] 4 0x00 [0057] 4 0x00 [0058] 4 0x00 [0059] 4 0x01
[0060] 4 0x00 [0061] 5 0x00 [0062] 5 0x01 [0063] 5 0x00
[0064] 5 0x00 [0065] 5 0x00 [0066] 5 0x01 [0067] 5 0x00
[0068] 5 0x00 [0069] 5 0x01 [0070] 5 0x00 [0071] 6 0x00
[0072] 6 0x00 [0073] 6 0x00 [0074] 6 0x00 [0075] 6 0x00
[0076] 6 0x00 [0077] 6 0x01 [0078] 6 0x00 [0079] 6 0x00
[0080] 6 0x00 [0081] 6 0x00 [0082] 6 0x00 [0083] 6 0x00
[0084] 6 0x01 [0085] 6 0x00 [0086] 6 0x00 [0087] 6 0x00
[0088] 6 0x00 [0089] 6 0x00 [0090] 7 0x00 [0091] 7 0x00
[0092] 7 0x00 [0093] 7 0x00 [0094] 7 0x00 [0095] 7 0x00
[0096] 7 0x00 [0097] 7 0x01 [0098] 7 0x00 [0099] 7 0x00
[0100] 7 0x00 [0101] 7 0x00 [0102] 7 0x00 [0103] 7 0x00
[0104] 7 0x00 [0105] 7 0x00 [0106] 7 0x01 [0107] 7 0x00
[0108] 7 0x00 [0109] 7 0x00 [0110] 7 0x00 [0111] 7 0x00
[0112] 7 0x00 [0113] 7 0x00 [0114] 7 0x01 [0115] 7 0x00
[0116] 7 0x00 [0117] 7 0x00 [0118] 7 0x00 [0119] 7 0x00
[0120] 7 0x00 [0121] 7 0x00 [0122] 7 0x01 [0123] 7 0x00
[0124] 7 0x00 [0125] 7 0x00 [0126] 7 0x00 [0127] 7 0x00
[0128] 7 0x00 [0129] 7 0x00 [0130] 7 0x00 [0131] 7 0x01
[0132] 7 0x00 [0133] 7 0x00 [0134] 7 0x00 [0135] 7 0x00
[0136] 7 0x00 [0137] 7 0x00 [0138] 7 0x00 [0139] 7 0x01
[0140] 7 0x00 [0141] 7 0x00 [0142] 7 0x00 [0143] 7 0x00
[0144] 7 0x00 [0145] 7 0x00 [0146] 7 0x00 [0147] 7 0x00
[0148] 7 0x01 [0149] 7 0x00 [0150] 7 0x00 [0151] 7 0x00
[0152] 7 0x00 [0153] 7 0x00 [0154] 7 0x00 [0155] 7 0x00
[0156] 7 0x01 [0157] 7 0x00 [0158] 7 0x00 [0159] 7 0x00
[0160] 7 0x00 [0161] 7 0x00 [0162] 7 0x00 [0163] 7 0x00
[0164] 7 0x00 [0165] 7 0x01 [0166] 7 0x00 [0167] 7 0x00
[0168] 7 0x00 [0169] 7 0x00 [0170] 7 0x00 [0171] 7 0x00
[0172] 7 0x00 [0173] 7 0x01 [0174] 7 0x00 [0175] 7 0x00
[0176] 7 0x00 [0177] 7 0x00 [0178] 7 0x00 [0179] 7 0x00
[0180] 7 0x00 [0181] 7 0x01 [0182] 7 0x00 [0183] 7 0x00
[0184] 7 0x00 [0185] 7 0x00 [0186] 7 0x00 [0187] 7 0x00
[0188] 7 0x00 [0189] 7 0x00 [0190] 7 0x01 [0191] 7 0x00
[0192] 7 0x00 [0193] 7 0x00 [0194] 7 0x00 [0195] 7 0x00
[0196] 7 0x00 [0197] 7 0x00 [0198] 7 0x01 [0199] 7 0x00
[0200] 7 0x00 [0201] 7 0x00 [0202] 7 0x00 [0203] 7 0x00
[0204] 7 0x00 [0205] 7 0x00 [0206] 7 0x00 [0207] 7 0x01
[0208] 7 0x00 [0209] 7 0x00 [0210] 7 0x00 [0211] 7 0x00
[0212] 7 0x00 [0213] 7 0x00 [0214] 7 0x00 [0215] 7 0x01
[0216] 7 0x00 [0217] 7 0x00 [0218] 7 0x00 [0219] 7 0x00
[0220] 7 0x00 [0221] 7 0x00 [0222] 7 0x00 [0223] 7 0x00
[0224] 7 0x01 [0225] 7 0x00 [0226] 7 0x00 [0227] 7 0x00
[0228] 7 0x00 [0229] 7 0x00 [0230] 7 0x00 [0231] 7 0x00
[0232] 7 0x01 [0233] 7 0x00 [0234] 7 0x00 [0235] 7 0x00
[0236] 7 0x00 [0237] 7 0x00 [0238] 7 0x00 [0239] 7 0x00
[0240] 7 0x00 [0241] 7 0x01 [0242] 7 0x00 [0243] 7 0x00
[0244] 7 0x00 [0245] 7 0x00 [0246] 7 0x00 [0247] 7 0x00
[0248] 7 0x00 [0249] 7 0x01 [0250] 7 0x00 [0251] 7 0x00
[0252] 7 0x00 [0253] 7 0x00 [0254] 7 0x00 [0255] 7 0x00
[0256] 7 0x00 [0257] 7 0x00 [0258] 7 0x01 [0259] 7 0x00
[0260] 7 0x00 [0261] 7 0x00 [0262] 7 0x00 [0263] 7 0x00
[0264] 7 0x00 [0265] 7 0x00 [0266] 7 0x01 [0267] 7 0x00
[0268] 7 0x00 [0269] 7 0x00 [0270] 7 0x00 [0271] 7 0x00
[0272] 7 0x00 [0273] 7 0x00 [0274] 7 0x01 [0275] 7 0x00
[0276] 7 0x00 [0277] 7 0x00 [0278] 7 0x00 [0279] 7 0x00
[0280] 7 0x00 [0281] 7 0x00 [0282] 7 0x00 [0283] 7 0x01
[0284] 7 0x00 [0285] 7 0x00 [0286] 7 0x00 [0287] 7 0x00
[0288] 7 0x00 [0289] 7 0x00 [0290] 7 0x00 [0291] 7 0x01
[0292] 7 0x00 [0293] 7 0x00 [0294] 7 0x00 [0295] 7 0x00
[0296] 7 0x00 [0297] 7 0x00 [0298] 7 0x00 [0299] 7 0x00
[0300] 7 0x01 [0301] 7 0x00 [0302] 7 0x00 [0303] 7 0x00
[0304] 7 0x00 [0305] 7 0x00 [0306] 7 0x00 [0307] 7 0x00
[0308] 7 0x01 [0309] 7 0x00 [0310] 7 0x00 [0311] 7 0x00
[0312] 7 0x00 [0313] 7 0x00 [0314] 7 0x00 [0315] 7 0x00
[0316] 7 0x00 [0317] 7 0x01 [0318] 7 0x00 [0319] 7 0x00
[0320] 7 0x00 [0321] 7 0x00 [0322] 7 0x00 [0323] 7 0x00
[0324] 7 0x00 [0325] 7 0x01 [0326] 7 0x00 [0327] 7 0x00
[0328] 7 0x00 [0329] 7 0x00 [0330] 7 0x00 [0331] 7 0x00
[0332] 7 0x00 [0333] 7 0x00 [0334] 7 0x01 [0335] 7 0x01
[0336] 7 0x01 [0337] 7 0x01 [0338] 7 0x01 [0339] 7 0x01
[0340] 7 0x01 [0341] 7 0x01 [0342] 7 0x00 [0343] 7 0x00 表7占据了15-52,90-343 这么多个EXTENT；
额，只剩下5000个页了，看看能不能admin_move_table成功吧。
INIT
db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','INIT')"
[0340] 7 0x01 [0341] 7 0x01 [0342] 7 0x00 [0343] 7 0x00
[0344] 8 0x40* [0345] 8 0x00* [0346] 9 0x40* [0347] 9 0x00*
[0348] 9 0x41* [0349] 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 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','COPY')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
COPY_END 2017-06-30-16.23.34.926709
COPY_OPTS OVER_INDEX,ARRAY_INSERT,NON_CLUSTER
COPY_START 2017-06-30-16.23.31.108700
COPY_TOTAL_ROWS 24492
INDEXNAME SQL170630153755800
INDEXSCHEMA SYSIBM
INDEX_CREATION_TOTAL_TIME 1
INIT_END 2017-06-30-16.16.33.144915
INIT_START 2017-06-30-16.16.30.992954
REPLAY_START 2017-06-30-16.23.34.928100
STAGING T4AAAAV0s
STATUS REPLAY
TARGET T4AAAAV0t
UTILITY_INVOCATION_ID 0100000046000000080000000000000000002017063016163316262900000000
VERSION 11.01.0101
16 record(s) selected. [0340] 7 0x01 [0341] 7 0x01 [0342] 7 0x00 [0343] 7 0x00
[0344] 8 0x40* [0345] 8 0x00* [0346] 9 0x40* [0347] 9 0x00*
[0348] 9 0x41* [0349] 9 0x01* [0350] 8 0x41* [0351] 8 0x01*
[0352] 8 0x00 [0353] 8 0x00 [0354] 8 0x00 [0355] 8 0x00
[0356] 8 0x00 [0357] 8 0x00 [0358] 8 0x01 [0359] 8 0x00
[0360] 8 0x00 [0361] 8 0x00 [0362] 8 0x00 [0363] 8 0x00
[0364] 8 0x00 [0365] 8 0x01 [0366] 8 0x00 [0367] 8 0x00
[0368] 8 0x00 [0369] 8 0x00 [0370] 8 0x00 [0371] 8 0x00
[0372] 8 0x01 [0373] 8 0x00 [0374] 8 0x00 [0375] 8 0x00
[0376] 8 0x00 [0377] 8 0x00 [0378] 8 0x00 [0379] 8 0x01 可以看到，相比上一步，表8建了索引，随后表8占据了352-379这几个块。
现在审视一下表8的数据：
db2dart testmove /DD /tsi 3 /oi 8 /ps 0 /np 0 /v y /rptn t4td.dart
查看导出的数据，可以发现先是id：
Page 0 -> 50-> 150 -> 200 -> 242 -> 250 -> 253 -> 400 -> 600 -> 743 -> 750 -> 760 -> 777 -> 780 -> 785
id: 100->2140-> 6202 -> 8253 -> 9978-29521 -> 29807 -> 30000-51040 -> 59020 -> 65246 -> 71091-300012 ->300313 -> 13227 -> 13992 -> 11036 -> 21076 就是大致是这样的，但是不够有顺序，没有完全遵循顺序应该考虑指定order by子句吗？
重来
首先我们创建测试数据库和测试表：
[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 primary key,name varchar(20),sex smallint,address varchar(200),education char(4)) IN orgsp2"
db2 "insert into t4 with c1(col1) as (values(3000) union all select c1.col1 +1 from c1 where c1.col1<3800) 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(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"
对表4做一些处理使表4id乱七八糟的哈：
db2 "insert into t4 with c1(col1) as (values(100) union all select c1.col1 +1 from c1 where c1.col1<500) select c1.col1,'t4LTT4','0','T4t4T4t4T4t4T4t4T4t4T4t4T4t4t4t4t4t4t4t4t4asdfskdfafasfsfsdfasdfjsadfla s sfdasdfsdfadsfas','benk' from c1"
db2 "delete from t4 where id%11=10"
db2 "insert into t4 with c1(col1) as (values(8000) union all select c1.col1 +1 from c1 where c1.col1<8500) select c1.col1,'t4Charles','0','T4tCharles4t4CharlesT4t4Charlest4t4t4t4t4t4asdadfla s sfdasdfsdfadsfas','benk' from c1"
db2 "insert into t4 with c1(col1) as (values(20000) union all select c1.col1 +1 from c1 where c1.col1<20900) select c1.col1,'t4lofuli','0','lofuliTlofuli4t4T4t4T4t4T4t4Tlofuli4t4T4t4T4tlofuli4t4t4t4t4t4t4t4adfasdfjsfas','benk' from c1"
db2 "delete from t4 where id%13=10"
db2 "insert into t4 with c1(col1) as (values(6300) union all select c1.col1 +1 from c1 where c1.col1<6900) select c1.col1,'t4lofuli','0','lofuliTlofuli4t4T4t4T4t4T4t4Tlofuli4t4T4t4T4tlofuli4t4t4t4t4t4t4t4adfasdfjsfas','benk' from c1"
db2 "insert into t4 with c1(col1) as (values(4880) union all select c1.col1 +1 from c1 where c1.col1<4990) select c1.col1,'t4krief','0','T4t kriefT4 tkrief4t4T4t4krief4t4t4t4t4t4asdkriefasfkrief asdfsdfadsfas','benk' from c1"
db2 "delete from t4 where id%23=10"
db2 "insert into t4 with c1(col1) as (values(5100) union all select c1.col1 +1 from c1 where c1.col1<5700) select c1.col1,'t4Laosi','0','LaosiT4t4T4tLaosi4T4t4T4tLaosi4T4t4TLaosiLaosisfas','benk' from c1"
db2 "delete from t4 where id%17=10"
Used pages = 1888
Free pages = 14464
High water mark (pages) = 1888
#查看表7的数据
db2dart testmove /DD /tsi 4 /oi 7 /ps 0 /np 0 /v y /rptn t4-01.dart
#查看数据块占用
db2dart testmove /DHWM /tsi 4 /rptn testmove-03.dart
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] 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] 4 0x00
[0020] 4 0x00 [0021] 4 0x01 [0022] 4 0x00 [0023] 4 0x00
[0024] 4 0x00 [0025] 4 0x01 [0026] 4 0x00 [0027] 5 0x00
[0028] 5 0x01 [0029] 5 0x00 [0030] 5 0x00 [0031] 5 0x00
[0032] 5 0x01 [0033] 5 0x00 [0034] 5 0x00 [0035] 5 0x01
[0036] 5 0x00 [0037] 6 0x00 [0038] 6 0x00 [0039] 6 0x00
[0040] 6 0x00 [0041] 6 0x00 [0042] 6 0x00 [0043] 6 0x01
[0044] 6 0x00 [0045] 6 0x00 [0046] 6 0x00 [0047] 6 0x00
[0048] 6 0x00 [0049] 6 0x00 [0050] 6 0x01 [0051] 6 0x00
[0052] 6 0x00 [0053] 6 0x00 [0054] 6 0x00 [0055] 6 0x00
[0056] 7 0x00 [0057] 7 0x00 [0058] 7 0x00 如果按照插入顺序存储，应该是这样的一个存储顺序：
3000-3800；
100-500：
删除
8000-8500：
20000-20900：
删除
6300-6900：
4880-4990：
删除
5100-5700：
删除
通过导出的文件查看,是符合我们插入的顺序的，然后看看admin_move_table之后它会不会有序呢？
Page 0：slot cnt26;... ... ...:slot 4:deleted(3000%13=10);slot 5:3001;slot 6 deleted(3002%17=10);slot 7 3003 ... ... ...3012,8001(3013%11=10),3014
... ... ...
Page 26:slot cnt31;slot 0:3797;slot 1:3798; slot 2:3799;slot 3:3800;slot 4:100;slot 5:6539(101%13=10);slot 6:Deleted;slot 7:103;slot 8:104; ... ... ...
... ... ...
Page 39:slot cnt35;slot 0:499;slot 1:500; (前面这一些插入了删除模11为10的位置)slot 2:8140;slot 3:8141;slot 4:8142;slot 5:6539(101%13=10);slot 6:Deleted;slot 7:103;slot 8:104; ... ... ...
... ... ...
Page 49:slot cnt35;slot 0:deleted record(8497%23=10);slot 1:8498; slot 2:6415(8499%13=0);slot 3:8500;slot 4:20000 ... ... ...
... ... ...
Page 75:slot cnt35;slot 0:20880;... ...;slot 19:20899; slot 20:20900;slot 21: 6483;slot 22:6484;slot 23:6485;... ... ...
... ... ...
Page 87:slot cnt34;slot 0:6871;... ...;slot 28:6899; slot 29:6900;slot 30:4880;slot 31 :4881; ... ... ...
... ... ...
Page 90:slot cnt38;... ...;slot 33:4989;slot 34:4990; slot 35:5100; slot 36:5101; ... ... ...
... ... ...
Page 104:slot cnt13;... ...;slot 11:5699;slot 12:5700;
开始进行试验：
db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','INIT')"
[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','COPY')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
COPY_END 2017-07-01-19.46.24.084896
COPY_OPTS OVER_INDEX,ARRAY_INSERT,NON_CLUSTER
COPY_START 2017-07-01-19.46.22.799171
COPY_TOTAL_ROWS 3278
INDEXNAME SQL170630173143820
INDEXSCHEMA SYSIBM
INDEX_CREATION_TOTAL_TIME 0
INIT_END 2017-07-01-19.46.12.912345
INIT_START 2017-07-01-19.46.10.176713
REPLAY_START 2017-07-01-19.46.24.085398
STAGING T4AAAAV0s
STATUS REPLAY
TARGET T4AAAAV0t
UTILITY_INVOCATION_ID 0100000047000000080000000000000000002017070119461296875400000000
VERSION 11.01.0101
16 record(s) selected.
Return Status = 0 db2dart testmove /DD /tsi 4 /oi 8 /ps 0 /np 0 /v n /rptn t4-02notorgnize.dart看一下数据的顺序：
Page 0：slot cnt26;... ... ...:slot 4:3001;slot 5:3003;slot 6 3004;... ... ...
... ... ...
Page 23:slot cnt1;slot 10:3797;slot 11:3798; slot 12:3799;slot 13:3800;slot 14:100;slot 15:6539;slot 16::103; ... ... ...
可以发现只是把Deleted Record 的记录删除了，并没有排序的。
[db2inst1@db22 tmp]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','ORDER BY id','','','','','COPY')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
COPY_END 2017-07-01-20.23.01.318382
COPY_OPTS OVER_INDEX,ARRAY_INSERT,NON_CLUSTER
COPY_START 2017-07-01-20.23.00.604331
COPY_TOTAL_ROWS 3278
INDEXNAME SQL170630173143820
INDEXSCHEMA SYSIBM
INDEX_CREATION_TOTAL_TIME 1
INIT_END 2017-07-01-20.21.11.753354
INIT_START 2017-07-01-20.21.10.308867
REPLAY_START 2017-07-01-20.23.01.318995
STAGING T4AAAAV0s
STATUS REPLAY
TARGET T4AAAAV0t
UTILITY_INVOCATION_ID 0100000048000000080000000000000000002017070120211175661600000000
VERSION 11.01.0101
16 record(s) selected.
Return Status = 0发现并没有按照id的顺序排序啊。
那是不是说admin_move_table它的重点还是在移动而不是在重组哦。。。
关于MDC索引的事情？
最后找到原因了，因为primary key产生的索引就是一个常规索引，不是集群索引。admin_move_table是需要使用一个集群索引的。
引以为戒啊！
扫一扫
7239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
