Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: Tables To DMS Tablespace Design

    It has been my contention that there are reasons that one should normally have a single table per tablespace (and with DMS, separate data from index):
    Recovery by tablespace
    Should probably have better performance where one doesn't have to contend with other tables in the tablespace and that the data could be easier to REORG without concern of other table data.

    Small tables which are related could be in same tablespace.

    The environment is a data warehouse.

    I'd like to get a feel for other experiences/thoughts.

    Someone stated that IBM stated to have different tables (not necessarily VERY LARGE) in same partitioned tablespace.

    Opinions, please.

  2. #2
    Join Date
    May 2003
    Partitioned tablespace? I think you are confusing DB2 for z/OS (mainframe) with DB2 for LUW. They are very different at the physical level. Only DB2 for z/OS has partitioned tablespaces.

    With DB2 for z/OS there are several very good reasons for having separate tablespaces for each table. I don't want to get into that discussion here since you apparently have DB2 for LUW (you mentioned DMS).

    With DB2 for LUW, tables are reorged, not tablespaces. If you are going to recover just one table, then you can recover the tablespace if it has only one table, but not many people recover just one table.

    From a performance point of view, a lot depends on your disk subsystem. If you have multiple containers spread among multiple disks (or multiple arrays) then it does not make much difference. If you have just one or a few containers, then you may want multiple tablespaces to spread the data around.

    When IBM runs TPC-H decision support benchmarks for DB2 (see they have multiple tables in each tablespace. Each tablespace has many containers spread across the disk arrays. Proper matching of extents, prefetch size, and number of containers is important for decision support systems, much more so than the number of tablespaces.
    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