Type 2 Indexs
Version 8 introduced Type 2 indexes as a new standard index
type,
replacing the V7 Type 1 indexes. Both indexes currently coexist, however,
Type 1 indexes will be de-supported in the future. The type 2 index is replacement for the old Type 1 index,
which has suffered from:
-
concurrency
issues during inserts and next key locks
-
performance
issues due to physically removing a key from a leaf page during update or
delete
Unlike Type 1 indexes, Type 2 indexes are more Oracle
like. Some benefits of Type 2 indexes:
-
no more
physical action after delete or update action until final commit
-
next-key
locking is reduced to a minimum, dramatically improving concurrency
-
online index reorganization supported
-
can be created
on columns whose length is greater than 255 bytes
Example of an existing Type 1 index, table DEMO01, schema
ARTIST:
$ pwd
/home/db2inst1/sqllib/db2dump
>> db2 inspect check table name DEMO1 schema ARTIST results keep index_check1.log
DB20000I The INSPECT command completed successfully.
$ ls -lrt
total 1856
-rw-rw-rw- 1 db2inst1 db2dba 157260 Aug 17 14:01 db2diag.log
-rw-rw-rw- 1 db2inst1 db2dba 5329 Aug 17 14:02 db2inst1.nfy
-rw-r----- 1 db2inst1 db2dba 159822 Aug 17 16:47 index_check1.log.000
$ cat index_check1.log.000
Tablespace phase start. Tablespace ID: 7
Tablespace name: ARTISTTS
Tablespace Type: SMS - System Managed Space; Extent size: 24; Page size: 8192; Number of containers: 1
Container name: /home/db2inst1/node0/artist/ARTISTTS
Table phase start (ID Signed: 2, Unsigned: 2; Tablespace ID: 7) :
Data phase start. Object: 2 Tablespace: 7
The index type is 1 for this table.
DAT Object Summary: Total Pages 532 - Used Pages 127 - Free Space 74 %
Data phase end.
Index phase start. Object: 2 Tablespace: 7
INX Object Summary: Total Pages 33 - Used Pages 33
Index phase end.
Table phase end.
Listing 11: Index Type 1 example
DB2
inspect check proved that our table, DEMO1, has one Type 1 index on it. This
is normal behavior after the database has been migrated to V8. The index was manually converted
to a Type 2 index, using REORG INDEXES command.
db2 => reorg indexes all for table ARTIST.DEMO1 convert
DB20000I The REORG command completed successfully.
>> db2 inspect check database results keep index_check2.log
DB20000I The INSPECT command completed successfully.
>> ls -lrt
total 1856
-rw-rw-rw- 1 db2inst1 db2dba 157260 Aug 17 14:01 db2diag.log
-rw-rw-rw- 1 db2inst1 db2dba 5329 Aug 17 14:02 db2inst1.nfy
-rw-r----- 1 db2inst1 db2dba 159822 Aug 17 16:47 index_check2.log.000
$ cat index_check2.log.000
Tablespace phase start. Tablespace ID: 7
Tablespace name: ARTISTTS
Tablespace Type: SMS - System Managed Space; Extent size: 24; Page size: 8192; Number of containers: 1
Container name: /home/db2inst1/node0/artist/ARTISTTS
Table phase start (ID Signed: 2, Unsigned: 2; Tablespace ID: 7) :
Data phase start. Object: 2 Tablespace: 7
The index type is 2 for this table.
DAT Object Summary: Total Pages 532 - Used Pages 127 - Free Space 74 %
Data phase end.
Index phase start. Object: 2 Tablespace: 7
INX Object Summary: Total Pages 36 - Used Pages 36
Index phase end.
Table phase end.
Tablespace phase end.
Listing 19: IBM withdraw support announcement
The table index was successfully converted to a Type 2
index. The new Type 2 index uses more database space (33 pages before and 36
pages after). Therefore, attention should be given to tablespace space prior
to converting all database indexes to Type 2 indexes. Some DBAs recommend
recreation and relocation of Type 2 indexes in a single, large, index
tablespace. All existing, non-converted Type 1 indexes will continue to
function properly and will be converted during index recreation. It is quite possible
that future DB2 versions will omit support for Type 1 indexes.
Conclusion
This article was a mixture of advice and techniques
to boost performance and prevent system failure after migration. The next logical
step should be performing a stress test with a critical application. One thing
is for sure--DB2 V8 brings more DB2 code stability and faster response times.
»
See All Articles by Columnist Marin Komadina
» 
IE 7
Firefox 2.0
Comment and Contribute
