Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    31

    Unanswered: Advantages of separate TS containers for LUW indexes

    Can anyone confirm if there is there any advantage within DB2 LUW when specifying tablespace containers for indexes to have each index in its own container? Thinking about it from a maintenance as well as access perspective it would seem to be the prudent way to go. Some shops seem to bundle them up into one singe tablespace.

    thanks
    Last edited by chippib; 01-16-12 at 13:32. Reason: Spelling

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There may be, as it allows you to allocate separate physical resources: storage and/or memory (via separate bufferpools) to some indexes or groups of indexes. Whether it makes sense in your circumstances, only you can decide.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can't directly specify different containers for indexes, but you can specify a different tablespace (which obviously has different containers than the other tablespaces.

    There usually is not much a performance difference if you are only looking at the disk I/O aspects, but in order to use different bufferpools for tables and indexes, they must be in different tablespaces. For that reason, I always specify different tablespaces for the table and the indexes. If the size of the entire database is larger than I can allocate for bufferpools, then I aim for a higher bufferpool hit ratio for indexes than for table data.
    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
    Nov 2011
    Posts
    31

    Advantages of separate TS conatainers for LUW indexes

    Thanks Marcus, please clarify. If I have 5 indexes against a very large table (1.7 billion rows) you see no performance (I/O) increase by creating 5 tablespaces for each of the indexes or at least a grouping of indexes in their own tablespace?

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by chippib View Post
    Thanks Marcus, please clarify. If I have 5 indexes against a very large table (1.7 billion rows) you see no performance (I/O) increase by creating 5 tablespaces for each of the indexes or at least a grouping of indexes in their own tablespace?
    Hmm. Since v9.7 you can assign a dedicated tablespace for 1 index, but I do not know if if also works for non-partitioned tables.

    Marcus_a is right and I want to add that you can also use another page-size for your index-tablespace. I think that a 4k tablespace in good for indexes and your data could require a larger pagesize.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by chippib View Post
    Thanks Marcus, please clarify. If I have 5 indexes against a very large table (1.7 billion rows) you see no performance (I/O) increase by creating 5 tablespaces for each of the indexes or at least a grouping of indexes in their own tablespace?
    The only reason why I would use separate tablespaces for each index, is if I was considering putting them in separate bufferpools (either now or in the future). There should not be much, if any, noticeable phyiscal I/O difference, if the indexes are not already in the bufferpool (hopefully not often). DB2 doesn't often use more than one index at a time, but there may be situations where it does use more than one index depending on your database and application design.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2011
    Posts
    31
    Thanks. Good response. Could you expand upon the advantages of separating Data & Indexes in their own tablespaces.

  8. #8
    Join Date
    Nov 2011
    Posts
    31
    My apologies, regarding my last post, does this methodology imply to both OLTP and DW environments?

    thanks again

  9. #9
    Join Date
    Jan 2012
    Posts
    3

    Nice discussion

    Very nice discussion on index container.

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    The mostly reason for put the indexes or/data in separated tablespaces is for holding them in separated bufferpool。If you put them together in one bufferpool,Maybe some indexes or data pages you want to pin in the memory will be chose as victim pages to flush out to disk。The best way to avoid it is to put each indexes and tables in a seperated tablespaces ,but There will be too many table spaces and bufferpools to maintain。SO how may tablespaces , bufferpools you will create is depend on the specific situations
    Last edited by fengsun2; 01-17-12 at 22:16.

Posting Permissions

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