Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Aug 25, 2004

DB2 Migration to Version 8 - Part 2 - Page 3

By Marin Komadina



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



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 



















Thanks for your registration, follow us on our social networks to keep up-to-date