DB2 SMS、DMS and automatic storage table spacesv

系统管理空间（System Management Space，SMS）和数据库管理空间（Database Management Space，DMS）。

SMS的管理比较简单，由操作系统自动管理，空间的大小随数据量的变化系统自动调整。

DMS是由数据库管理的，空间大小在创建时确定，空间不够时要手工添加或删除部分数据以释放空间。

默认用户表空间名为USERSPACE1，索引也存储在规则表空间中，另外系统目录表也放在规则表空间中。默认的系统目录表空间名为SYSCATSPACE。
临时表空间分为系统临时表空间和用户临时表空间。
系统临时表空间用来存储各种数据操作（排序、重组表、创建索引、连接表）中所需的内部临时数据，虽然可以创建任意多个系统临时表空间，但建议用户只使用大多数表所使用的页大小创建一个，默认系统临时表空间名为TEMPSPACE1。
用户临时表空间用来存储已说明全局临时表（已说明全局临时表存储的是应用程序临时数据）。用户临时表空间不是在数据库创建时默认创建的。
SMS每个容器是操作系统的文件空间中的一个目录；DMS每个容器是一个固定的、预分配的文件，或是物理设备。

两者的对比

        特性                                SMS        DMS
能够在表空间中动态增加容器的数目吗               N        Y
能够把索引数据存放到不同表空间的表中吗            N        Y
能够把大对象数据存放到不同表空间的表中吗          N        Y
表可以分散存放到多个表空间中吗                   N        Y
仅在需要时才分配空间吗                          Y        N
表空间可以被放在不同的磁盘中吗                   Y        N
创建之后，区段大小能够改变吗                     N        N

 

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. 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.

• SMS and DMS workload considerations
  The primary type of workload being managed by the database manager in your environment can affect your choice of what table space type to use, and what page size to specify.
• SMS and DMS device considerations
  There are a few options to consider when choosing to use file system files versus devices for table space containers: the buffering of data and whether to use LOB or LONG data.

变更表空间大小

--更改表空间大小

ALTER TABLESPACE edwadm RESIZE (device '/dev/redwadm' 900000);

ALTER TABLESPACE TS1 RESIZE (FILE '/conts/cont0' 2000, DEVICE '/dev/rcont1' 2000, FILE 'cont2' 2000);

ALTER TABLESPACE TS1 RESIZE (ALL 2000)；

db2 " ALTER TABLESPACE PAYROLL ADD (DEVICE '/dev/rhdisk9' 10000) "；