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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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.