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 is deprecated 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: 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.
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 default storage group. Data is striped evenly across all containers so that the storage paths are used equally. | Requires that containers be defined as a directory name. |
|
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.
|
Changes to table space containers |
|
No changes once created, other than to add containers for new data partitions as they are added. |
|
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 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 |
|
None |
|
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. During the restore, the data and containers might be redefined. For redefinition conditions, see Rebalancing during RESTORE of automatic storage database. | 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:
- 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.