Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Type-2 indexes benefits?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-19-07, 03:53
grofaty grofaty is offline
Registered User
  
Join Date: Jan 2003
Posts: 1,570
Type-2 indexes benefits?
I just opened new thread continuing from: Performance SQL or Reorg? just not to mess the above thread by my question.

Quote:
Originally Posted by ARWinner
I suggested doing the CONVERT just in case you had type-1 indexes.
What is the benefit to convert indexes from type-1 to type-2? I still have type-1 indexes, because there was some disk increases by type-2 indexes. So I would really like to hear the reason to convert. As I see there is no way back to type-1 if it is converted to type-2.

My system:
db2 v8.2 FP9 on Windows 2003 SP1
Last edited by grofaty; 07-19-07 at 03:58.
Reply With Quote
  #2 (permalink)  
Old 07-19-07, 08:25
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Type-2 indexes have several differences from type-1. The main difference is in concurrency control. Check here for more info:

http://publib.boulder.ibm.com/infoce.../v8//index.jsp

Andy
Reply With Quote
  #3 (permalink)  
Old 07-19-07, 08:44
Wim Wim is offline
Registered User
  
Join Date: Nov 2004
Posts: 1,279
How do I know if i'm using a level-1 or level-2 index ?

ARWinner, the URL points to the general index page. If you want to pass the URL of one specific page, right klick on the page, select "View Page Info" (in FireFox), and copy-and-paste the "Referred URL".
__________________
With kind regards . . . . . SQL Server 2000/2005/2008/2008 R2 Earned beers: 16
Wim
Beware of bugs in the above code; I have only proved it correct, not tried it. -- Donald Knuth
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Reply With Quote
  #4 (permalink)  
Old 07-19-07, 09:33
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Here is the updated URL:

http://publib.boulder.ibm.com/infoce...e/c0009067.htm

You can use the INSPECT command or db2dart to determine if type-2 indexes are used.

For db2dart usage: http://www.dbazine.com/db2/db2-disarticles/zikopoulos3

Andy
.
Reply With Quote
  #5 (permalink)  
Old 07-20-07, 01:31
grofaty grofaty is offline
Registered User
  
Join Date: Jan 2003
Posts: 1,570
Hi,

I have read this article: http://www.databasejournal.com/featu...0896_3399101_3
and it convinced me. So yesterday I have converted all type-1 indexs to type-2 indexes and disk space hasn't increased so much as I expected only less then 0,5%. So all my indexes in production database are now type-2 and there are no problem so far.

Hope this helps,
Grofaty
Reply With Quote
  #6 (permalink)  
Old 07-20-07, 16:54
stolze stolze is offline
Registered User
  
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
Didn't you try this in a test system first?
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #7 (permalink)  
Old 07-23-07, 01:50
grofaty grofaty is offline
Registered User
  
Join Date: Jan 2003
Posts: 1,570
stolze, sure I did. First on test system and then on production.

BTW, if some needs script to list all indexes...
Code:
db2 SELECT 'REORG INDEXES ALL FOR TABLE ' CONCAT RTRIM(TABSCHEMA) CONCAT '.' CONCAT TABNAME CONCAT ' CONVERT' FROM SYSCAT.TABLES WHERE TYPE='T' AND TABSCHEMA NOT LIKE 'SYS%' AND TABSCHEMA <> 'BIADMIN' ORDER BY 1 FOR READ ONLY WITH UR
Regards,
Grofaty
Last edited by grofaty; 07-23-07 at 02:04.
Reply With Quote
  #8 (permalink)  
Old 07-23-07, 07:24
stolze stolze is offline
Registered User
  
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
Quote:
Originally Posted by grofaty
stolze, sure I did. First on test system and then on production.
That's fine. I just got scared by the way you phrased it.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #9 (permalink)  
Old 07-23-07, 18:05
sundaram sundaram is offline
Registered User
  
Join Date: Mar 2006
Posts: 104
Hi,

We used to get frequent deadlocks after migration from db2 ver 7 to db2 ver 8. We then converted all indexes to type 2 by using reorg index with convert. The deadlocks disappeared after this.

Regards

Harikumar
Reply With Quote
  #10 (permalink)  
Old 07-24-07, 01:51
grofaty grofaty is offline
Registered User
  
Join Date: Jan 2003
Posts: 1,570
Sundaram,
I would agree the benefits according to Database Journal are:
* 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

Hope this helps,
Grofaty
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.