DB2 Version 10.1 for Linux, UNIX, and Windows

Comparison of automatic storage, SMS, and DMS table spaces

Automatic storage, SMS, and DMS table spaces offer different capabilities that can be advantageous in different circumstances.

Important: The SMS table space type has been deprecated in Version 10.1 for user-defined permanent table spaces and might be removed in a future release. The SMS table space type is not deprecated for catalog and temporary table spaces. For more information, see SMS permanent table spaces have been deprecated
Important: Starting with Version 10.1 Fix Pack 1, the DMS table space type is deprecated for user-defined permanent table spaces and might be removed in a future release. The DMS table space type is not deprecated for catalog and temporary table spaces. For more information, see DMS permanent table spaces have been deprecated.
Table 1. Comparison of SMS, DMS and automatic storage table spaces
  Automatic storage table spaces SMS table spaces DMS table spaces
How they are created Created using the MANAGED BY AUTOMATIC STORAGE clause of the CREATE TABLESPACE statement, or by omitting the MANAGED BY clause entirely. If the automatic storage was enabled when the database was created, the default for any table space you create is to create it as an automatic storage table space unless you specify otherwise. Created using the MANAGED BY SYSTEM clause of the CREATE TABLESPACE statement Created using the MANAGED BY DATABASE clause of the CREATE TABLESPACE statement
Initial container definition and location You do not provide a list of containers when creating an automatic storage table space. Instead, the database manager automatically creates containers on all of the storage paths associated with the database. Data is striped evenly across all containers so that the storage paths are used equally. Requires that containers be defined as a directory name.
  • Requires that containers be defined as files or devices.
  • Must specify the initial size for each container.
Initial allocation of space
  • For nontemporary automatic storage table spaces:
    • Space is allocated when the table space is created
    • You can specify the initial size for table space
  • For temporary automatic storage table spaces, space is allocated as needed.
 Done as needed. Because the file system controls the allocation of storage, there is less likelihood that pages will be contiguous, which could have an impact on the performance of some types of queries. Done when table space created.
  • Extents are more likely to be contiguous than they would be with SMS table spaces.
  • Pages within extents are always contiguous for device containers.
Changes to table space containers
  • Containers can dropped or reduced if the table space size is reduced.
  • Table space can be rebalanced to distribute data evenly across containers when new storage is added to or dropped from the database.
 No changes once created, other than to add containers for new data partitions as they are added.
  • Containers can be extended or added. A rebalance of the table space data will occur if the new space is added below the high water mark for the table space.
  • Containers can be reduced or dropped. A rebalance will occur if there is data in the space being dropped
Handling of demands for increased storage
  • Containers are extended automatically up to constraints imposed by file system.
  • If storage paths are added to the database, containers are extended or created automatically.
 Containers will grow until they reach the capacity imposed by the file system. The table space is considered to be full when any one container reaches its maximum capacity. Containers can be extended beyond the initially-allocated size manually or automatically (if auto-resize is enabled) up to constraints imposed by file system.
Ability to place different types of objects in different table spaces Tables, storage for related large objects (LOBs) and indexes can each reside in separate table spaces. For partitioned tables only, indexes and index partitions can reside in a table space separate from the one containing table data. Tables, storage for related large objects (LOBs) and indexes can each reside in separate table spaces.
Ongoing maintenance requirements
  • Reducing size of table space
  • Lowering high water mark
  • Rebalancing
 None
  • Adding or extending containers
  • Dropping or reducing containers
  • Lowering high water mark
  • Rebalancing
Use of restore to redefine containers You cannot use a redirected restore operation to redefine the containers associated with the table space because the database manager manages space. You can use a redirected restore operation to redefine the containers associated with the table space You can use a redirected restore operation to redefine the containers associated with the table space
Performance Similar to DMS Generally slower than DMS and automatic storage, especially for larger tables. Generally superior to SMS
Automatic storage table spaces are the easiest table spaces to set up and maintain, and are recommended for most applications. They are particularly beneficial when: