DB2 Version 9.7 for Linux, UNIX, and Windows

Comparison of SMS, DMS and automatic storage table spaces

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

Table 1. Comparison of SMS, DMS and automatic storage table spaces
	SMS table spaces	DMS table spaces	Automatic storage table spaces
How they are created	Created using the MANAGED BY SYSTEM clause of the CREATE TABLESPACE statement	Created using the MANAGED BY DATABASE clause of the CREATE TABLESPACE statement	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.
Initial container definition and location	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.	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.
Initial allocation of space	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.	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.
Changes to table space containers	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	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.
Handling of demands for increased storage	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 it's 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.	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.
Ability to place different types of objects in different 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.	Tables, storage for related large objects (LOBs) and indexes can each reside in separate table spaces.
Ongoing maintenance requirements	None	Adding or extending containers Dropping or reducing containers Lowering high water mark Rebalancing	Reducing size of table space Lowering high water mark Rebalancing
Use of restore to redefine containers	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	You cannot use a redirected restore operation to redefine the containers associated with the table space because the database manager manages space.
Performance	Generally slower than DMS and automatic storage, especially for larger tables.	Generally superior to SMS	Similar to DMS

Of the three types of table spaces, automatic storage table spaces are the easiest to set up and maintain, and are recommended for most applications. They are particularly beneficial when:

You have larger tables or tables that are likely to grow quickly
You do not want to have to make regular decisions about how to manage container growth.
You want to be able to store different types of related objects (for example, tables, LOBs, indexes) in different table spaces to enhance performance.

DMS table spaces are useful when:

You have larger tables or tables that are likely to grow quickly
You want to exercise greater control over where data is physically stored
You want to be able to do make adjustments to or control how storage is used (for example, adding containers)
You want to be able to store different types of related objects (for example, tables, LOBs, indexes) in different table spaces to enhance performance.

SMS table spaces are useful when:

You have smaller tables that are not likely to grow quickly
You want to exercise greater control over where data is physically stored
You want to do little in the way of container maintenance
You are not required to store different types of related objects (for example, tables, LOBs, indexes) in different table spaces. (For partitioned tables only, indexes can be stored in table spaces separately from table data).