Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Type-2 indexes benefits?

    I just opened new thread continuing from: http://www.dbforums.com/showthread.php?t=1620217 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 04:58.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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/2012
    Wim

    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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
    .

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Didn't you try this in a test system first?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    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 03:04.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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

  9. #9
    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

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •