Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: subpages in index

    hi

    This w.r.t type 1 indexes as its outdated, as per doc the leaf pages are broken down to number of subpages and this subpages have index entry, and type 2 indexes don't have subpages, please share how this works and how this is better as compare to type 2 indexes as they don't have subpages,

    Please share ur valuable suggestion, as doc don't say much abt this.

    regds
    Paul

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    on this page
    Index structure
    you have an overview...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The main benefit of Type 2 indexes is reducing locking problems in the index.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2007
    Posts
    246
    thks guy and marcus reply

    But how this works like type 2 indexes is reducing locking compare to type 1 after elimination of subpages, how this work, could pls give a example, or any more light how this work's

    redgs
    Paul

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated before :
    have you looked at the page, pointed by the link ?
    have you read the doc - infocenter - pdf files (performance tuning...reference...)
    this is not an sql that you can show a example for.. it is a design-architecture... and explained - maybe not in detail..as this is not always relevant to a user..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't know the details myself, but one explanation could be this: Physically removing an entry from the index requires some sort of mechanism to close the introduced gap. Closing this gap means you'll have to lock/protect larger parts of the index page. But if you keep the entry where it is and just set a bit/byte to mark the entry as already deleted, you don't have to worry about the mentioned reorganization. Hence, you'll need less locking at that point. Eventually, you'll have to clean up the page at some point in time, but that could be done when multiple deleted index entries are detected and handled together.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Mathew_paul View Post
    thks guy and marcus reply

    But how this works like type 2 indexes is reducing locking compare to type 1 after elimination of subpages, how this work, could pls give a example, or any more light how this work's

    redgs
    Paul
    Since type 1 indexes are no longer supported in the latest release, and even since 8.1 were automatically converted to type 2 when a reorg was done, I can't imagine why you even care. It is complete waste of time discussing it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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