Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006

    Question Unanswered: One tablespace per table

    Hi !
    A collegue of mine had created several tables in juste one tablespace.
    I'm wondering what is the best solution
    1/ One table space per table
    2/ Or one tablespace for several tables ?
    Could i have the advantages and inconvenients of these propositions ?
    If you tell me that the best solution is the solution 2:
    How could i correct it ?
    a/ I must create new tablespace for each table
    b/ I must alter my table (could you join sample) ?
    c/ What about data if i change the tablespace of each table ? (should i unload data before alter my table ?)

    Thanks a lot

  2. #2
    Join Date
    Jun 2006


    for large tables it is recommended to place each in its own tablespace - dms if possible and you could also split off data and index to their own ts
    this requires export table - drop-create table - load table
    maintenance is easier for individual table/ts
    also for backup of ts - you backup only 1 table
    for small tables : place tables with same behavior in same ts.
    more ts = more monitoring for space usage....
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Feb 2004

    Arrow No difference

    In my experience it doesn't matter wether you
    have large and small tables grouped together
    in physical storage i.e. tablespaces.
    If they don't have notably different specs i group
    my tables physically according to the front end
    systems, i.e. logical divisions.

    Many tablespaces signifies lots of maintenance
    and DMS TS can always be expanded - which
    is oftenmost the case

    Again pros and cons - but grab a manual
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  4. #4
    Join Date
    Mar 2003
    I understand that for large tables which are frequently used, using one tab/tbsp impacts the caching algorithm on the disk subsystem.

    However, having a large number of tablespaces also impacts performance since at any point in time, the db can have so many file handlers open (maxfilop). If it exceeds this number, then to access data, the db will have to close a handle and open another one for access.

    Then there's the restore angle. All tables in a tablespace can only be restored to the same point in time - therefore tables grouped in a tbsp need to have similar recovery requirements.

  5. #5
    Join Date
    May 2003
    On DB2 for z/OS, it is usually recommended that you have one table per tablespace. In some situations it is OK to have more than one table per tablespace, but make sure the tablespace is segmented in those cases.

    On DB2 for Lnux, UNIX, Windows, you should generally have many tables in a tablespace. The decision about how many tables per tablespace is often determined by the bufferpool configuration, since you assign tablesspaces to bufferpools. If you plan on having 2 bufferpools (one for small tables and all indexes, and another for large tables) then you will need at least 2 different 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