Reorg is mainly to rearrange the data to save unnecessary space use, at the same time, it can reduce the number of write buffers and improve performance. Reducing space occupancy can be achieved by reducing the high water level in surface space. In order to analyze various reorg cases, an experimental test was carried out in particular.
1. First, a database for testing is established, and a table space is established for testing and analysis. Four tables are built in the table space and some data are inserted. Then look at the extension occupation.
[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 = 1Now that page 2368 and high water level 2368 have been used, let's look at the blocks it occupies:
db2 disconnect all
db2 deactivate db testorg
db2dart testorg /DHWM /tsi 3 /rptn testorg-1.dartHigh 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. Now we delete the data of t2, and reorganize T2 using system temporary table space or online to make the extension of T2 free.
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.dartThe results are as follows:
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 Experiment 1
3-1. Now delete some of the data in Table 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.dartHigh 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. Table 7 occupies 8 EXTENTs, but only 7 EXTENTs in the middle.
Using admin_move_table at this point does not specify a table space
[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.dartHigh 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 As you can see:
INIT phase
- Firstly, the target table 8 is created according to table 7. The TABLE statement is the same, and there is no index, so 8 occupies two extent.
- The staging Table 9 is created, and the index is initialized to occupy four extent.
Following is the copy stage:
[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
Let's look at the distribution of data blocks at this point:
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 Let's look at the second step:
Table 7 occupies 48,49,50,51,52,53 except for the first data block.
Table 8 fills in 28,54,55,56,57,57,58 except for the first data block.
The replicated table 8 has more than one data block.
Then Table 8 of the 59th extension builds an index, which occupies 60*, 61, 62, 63, 64, 65, 66, 67 extent s.
The original table 7 was indexed in 68 extents, which occupied extent69*,70,71,72,73,74,75,76.
Then you see that table 8 has one more data block than table 7, and then the index block is the same.
Why is Table 8 one more data block than Table 7?
What is the index in Tables 8 and 7?
Let's look at some constructs of data blocks and examine table data with 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 and pages All from0Start, so page416 It is the first.14A block is a block. extent13 The first page, stored here7Table EMP information
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
... ... ... ellipsis
EMP pool page: 447, # entries: 0
Page LSN = 000000000004180F
Pool relative page #'s :
INX extent anchor: 2176 extent68 It's a table.7Bitmap information of index
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
... ... ...ellipsis
EMP pool page: 2207, # entries: 0
Page LSN = 0000000000070A94
Pool relative page #'s :
Look at the data in Table 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
Comparing the exported files with Beyond Compare, we find that table 7 has 194 data pages, and there are 10 Deleted Record s on the last page; Table 8 has 195 pages on the last page and only one slot on the last page.
View the index:
[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.
You can see that because there is no index on the T4 table, the index of ID+NAME is built on the T4 table.
Last step 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 == The staging table is emptied, the index built is emptied, and the replacement is completed. The id of the table is 8.
These empties come down after reconnecting to the database.
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 Look at what happened with the change in extension?
- The original table id7 became 8:4.SWAP: The stored procedure quickly offlines the source table, assigns the source table name and index name to the shadow copy and its index, and then makes the shadow table online to replace the source table. By default, the source table is deleted, but you can use the KEEP option to keep the source table under another name. This is the replacement phase, which is different from the replacement of offline table reorganization. This admin_move_table is a new table object create d in the reorganization process, which is different from the copy of offline table reorganization.
- The original table 7 occupies 13 (EMP), 14 (the first data block) EMPTY, and the positions of the two blocks are moved to 22 and 23. This understanding is as follows: 1.INIT stage: creating shadow table of the source table, initializing EMP and the first data block make use of the empty EXTENT22 and 23.
- The other six data blocks occupied originally, one on extension 28, which is empty in t2, and the other five new applications for extent. How did this happen?
- The original table 7 occupies 48-53 EMPTY. The source table is deleted at the end of the SWAP phase.
Experiment 2
3-2. Now delete some of the data in Table 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.dartHigh 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. Table 7 occupies 8 EXTENTs, but only 7 EXTENTs in the middle.
Use admin_move_table in the second way
The first step is to create tables manually.
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). Then call the stored procedure to pass in the name of the target table:
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 From here it can be seen that table 9 occupies 24, 25, 26, 27 blocks. This is an indexed table.
[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.Table 9 should be 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 After the COPY phase is completed, you can see why 54-58 is EMPTY. Why 54-58 is EMPTY? Our original table 7 is not indexed. In the copy phase, Table 8 has an index. The index bitmap and the first index block occupy 59,60 EXTENT. Then 61, 62, 63, 64, 65, 66, 67 EXTENTs are indexed. Then an index is created for table 7. The index bitmap and index block occupy 9 EXTENTs, which are consistent with table 8. What has been done in this step?
Failed. Because this admin_move_table can only be moved to another table space. Reported SQL2104N in the same table space
The ADMIN_MOVE_TABLE procedure could not be completed at this time by this user.
Explanation: Move from one table space to another: You can use the SYSPROC.ADMIN_MOVE_TABLE procedure to move data from one table space to another table space.
But some rules of online mobile tables are obvious.
First recovery:
operation is 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 ==Then call cleanup: well, SQL2104N The ADMIN_MOVE_TABLE procedure can not be completed at this time by this user. Reason code: "8". SQLSTATE=5UA0M
Reason code 8 indicates: No online move table operation is in progress; therefore, the specified operation is not allowed.
After deleting 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 == How do you cut this off? Try online restructuring 7:
Well, No.
No later, maybe it will take a while.
Well, take a look at different tablespaces:
#Create a new table space
[root@db22 opt]# touch /opt/var/orgsp2
[root@db22 opt]# chown db2inst1 /opt/var/orgsp2
The first step is to create tables manually.
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). Then call the stored procedure to pass in the name of the target table:
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*
The extension of tsi:3 has not changed much. The high water level is up to extension 53.
From here it can be seen that table 9 occupies 24, 25, 26, 27 blocks. This is an indexed table.
[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 Big Discovery: You can see that the index of Table 7 was built at 22-28, then the index occupied 8 EXTENT s, and then two new applications were made.
Then move truncation:
Well, I still can't.
SQL2104N The ADMIN_MOVE_TABLE procedure could not be completed at this time
by this user. Reason code: "4". SQLSTATE=5UA0M
Come back tomorrow.
The baby has come to work - ---------------------------------------------------------------------------------------------------------------------------------------------------------
~~ Today is the first test to get the move table running. Then I looked at the following documents and found that the last step was 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 ==You can see that staging table has been deleted; the index created on table t4 has been deleted.
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 ==
You can see that the index data has been deleted.