Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Unanswered: Performance of Tablespaces

    I want to know what is better ..To have one tablespace with many tables or for each table one tablespace.... I would like to know which is the performance of the distribution of tablespaces in DB2 . I use DB2 7.1
    in AIX..

    Now i have almost 140 Tables distributed in 53 Tablespaces..
    And i have a tablespace only for indexes is this correct? for the performance of de database..

    Thank you very much...

  2. #2
    Join Date
    May 2003
    That depends on many factors, including how much buffer pool memory is allocated, for example. If there is lots of buffer pool space allocated, there is not much synchronous physical I/O going on.

    In theory it does help to have indexes on a seperate disk drive or disk array than the tables, so having different tablespaces for them makes that possible if you set it up that way.

    You do not need to have each table in it's own tablespace. However, tablespaces have their own page size, bufferpool, extents, and prefetch size, which could differ among the tables you have.

    Backup and recovery of related objects at the tablespace level is also a consideration.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2004
    Los Angeles
    In general conditions :
    If there is enough buffer pool , I rather to have 1 table space for the tables and 1 table space for my indexes. In this case you will have less hassle for administration and ...
    However it depends to your application, sometimes it is better to have your indexes in 2 different table spaces because you want to synch 2 or more different hard disk to return your results back .

  4. #4
    Join Date
    Feb 2004
    I've found that tables that have a high insert/delete rate are best served in their own TS. We did this for a customer and got a pretty good TPS improvement.

    Every environment/application is different and after following basic guidelines, you'll have to start pursuing your 'gut' feelings.

  5. #5
    Join Date
    Aug 2001
    If you are using LOAD Utility, you may want to put those tables into their own tablespace ...

    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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