Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Placement of tables in Table Space

    On Unix platforms, what is the guideline for placement of tables in table spaces?

    i) Each table on its own table space with the corresponding indexes in their own table space? e.g., 156 tables have 156 data table spaces and n number of index table spaces.
    ii) Group tables on size & nature of use. Now place into appropriate category of table space. e.g., small tables data table space, big tables data table space, small tables index table space, etc.,

    TIA.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is no reason on DB2 for Linux, UNIX, and Windows to have a separate tablespace for each table. The main criteria for grouping tables together in tablespaces is that each tablespace can be assigned to a separate bufferpool (if desired) so if you want multiple bufferpools (of the same page size) you will need multiple tablespaces.

    How you place the containers within the tablespace, and the number of containers, can be important for data warehouses where a large number of tablespace scans take place. You can optimize the placement to reduce disk contention and sometimes invoke intra-partition parallelism (without DPF).

    SMS tablespaces should be used for system temporary tablespaces because it faster for DB2 to create a table in SMS compared to DMS. This is because DB2 spends more time looking for the optimal location to place the table in the tablespace if it is DMS.
    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
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by Marcus_A
    There is no reason on DB2 for Linux, UNIX, and Windows to have a separate tablespace for each table. The main criteria for grouping tables together in tablespaces is that each tablespace can be assigned to a separate bufferpool (if desired) so if you want multiple bufferpools (of the same page size) you will need multiple tablespaces.

    How you place the containers within the tablespace, and the number of containers, can be important for data warehouses where a large number of tablespace scans take place. You can optimize the placement to reduce disk contention and sometimes invoke intra-partition parallelism (without DPF).

    SMS tablespaces should be used for system temporary tablespaces because it faster for DB2 to create a table in SMS compared to DMS. This is because DB2 spends more time looking for the optimal location to place the table in the tablespace if it is DMS.
    On Unix, MAXFILOP parameter setting can be an issue if each table has its own data table space & index table space.

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    I still prefer for large tables using their own tablespace for not having to worry about access of other data during maintenance (reorg, load etc) - I know we have now online utilities available, but they do not cure all problems.
    Juliane

Posting Permissions

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