DB2 supports automatic storage of three table spaces for SMS, DMS, DMS

DB2 supports automatic storage of three table spaces for SMS, DMS, DMS

In DB2, a tablespace is a logical layer between a database and a table stored in that database. Table spaces are created in the database, and tables are created in the tablespace. A container is a physical storage device. It can be identified by a directory name, a device name, or a file name. The container is assigned to a table space. Tablespace can span many containers (similar to the concept of datafile in Oracle), which means that the operating system can break the limits of the amount of data a container can contain. DB2 supports three types of table spaces:

· System-Managed space (system-managed space,sms) : In this case, the file system Manager of the operating system allocates and manages the space. Prior to DB2 9, creating a database or tablespace without any parameters would cause all tablespaces to be created as SMS objects.

· Database Management space (database-managed SPACE,DMS): here, the storage space is controlled by the database management program. This kind of tablespace is essentially a special-purpose file system implementation, which can best meet the needs of the database management program.

· DMS Automatic Storage (Automatic Storage with DMS): auto-storage is not actually a separate tablespace type, but rather a different way of handling DMS storage. DMS containers require more maintenance, and automatic storage is introduced in DB2 V8.2.2 as a way to simplify space management.

The SMS tablespace requires very little maintenance. However, the SMS tablespace provides fewer optimization options and poor performance than DMS table spaces.

SMS , DMS and automatic storage

Characteristics	Sms	Dms	Automatic storage
Is it segmented (Striping)?	Is	Is	Is
Default Type	Version 8	No	Version 9
Object Management	Operating system	DB2	DB2
Space allocation	Grow/Shrink On demand	Pre-allocated; size can shrink and grow, but DBA intervention is required.	be pre-allocated;
Ease of management	It is best to have little tuning or even need	OK, but some tuning is needed (for example, Extentsize prefetchsize	It is best to have little tuning or even need
Performance	Very good	Preferably, 5% to 10% more revenue can be obtained by using the original container	Preferably, however, you can not use the original container
Maximum table space size	64GB (4K page)	2TB (4K page)	2TB (4K page)

In addition to using SMS tablespace to simplify administration, the most significant difference between the two storage models is the maximum size of the tablespace. When using SMS, DBAs can only put up to 64GB of data in a table space. Changing the page size to 32K can extend this limit to 512GB, but at the expense of less free space on each page. Instead, the DMS model expands the tablespace limit to 2TB (in the case of a 4K page size). If you change the page size to 32K, the available space can grow to 16TB. Although there are other ways to break the table size beyond the 64GB limit, the simplest approach might be to use DMS table space at the outset.

DMS and automatic storage

DB2 8.2.2 introduces the concept of automatic storage. Automatic storage allows DBAs to set the storage path that can be used when creating all the tablespace containers for the database. The DBA does not have to explicitly define the location and size of the table space, and the system will automatically allocate tablespaces. In DB2 9, the database is enabled for automatic storage when it is created, unless the DBA explicitly overrides this setting.

The database that is enabled for automatic storage has one or more associated storage paths. A tablespace can be defined as "managed by Automatic storage", and its containers are allocated by DB2 based on these storage paths. The database can only enable automatic storage when it is created. This feature cannot be enabled at a later time for databases that do not initially have automatic storage enabled. Similarly, you cannot disable this feature at a later time for databases that are initially enabled for automatic storage.

Characteristics	Non-automatic storage	Automatic storage
Creation of containers	You must explicitly provide the container when you create the tablespace.	You cannot provide a container when you create a tablespace; they are automatically assigned by DB2.
Adjustment of container size	By default, the automatic adjustment of the tablespace size is off (autoresize NO).	By default, the automatic resizing of the tablespace size is open (autoresize Yes).
Initial size	You cannot use the INITIALSIZE clause to specify the initial size of a tablespace.	Use the INITIALSIZE clause to specify the initial size of the tablespace.
Modification of containers	You can use the ALTER tablespace statement (ADD, DROP, BEGIN NEW STRIPE SET, and so on) to perform container operations.	The container operation cannot be performed because the DB2 controls the space management.
Ease of management	You can use a redirected restore operation to redefine the container associated with a tablespace.	You cannot use a redirected restore operation to redefine the container associated with a tablespace because the DB2 controls the space management.

Table spaces are stored in DB2 in two ways, SMS (System Managed space) and DMS (Database Managed space).
　　
SMS uses the operating system's file system to manage data directly. The data in the tablespace is divided by data blocks on all the containers in the system. All tables in the tablespace are given its own file name, which is used in all containers. The file name extension indicates the type of data stored in the file.
　　
DMS is the storage space control of the table space by the database. When you define a DMS tablespace, you need to select the device or file list so that it belongs to the table space.
　　
The main difference between DMS tablespace and SMS tablespace is that, for DMS tablespace, storage is allocated when the tablespace is created, rather than when required to be applied to the operating system. Also, there are some differences in the placement of data on these two types of table spaces. For example, when considering efficient table scans, it is important that data blocks are physically stored continuously. For SMS, the operating system's file system determines the physical storage location of each logical file page. Depending on the level of other activities on the file system and the algorithm used to determine the location, these logically contiguous pages may or may not be allocated continuously. For DMS, it is theoretically possible to ensure that the pages are physically stored continuously because the database manager is dealing with the disk directly.
　　
However, it should be noted that when using DMS table spaces, there are two container options: Raw Device (raw) and file. When you use the file option, the database Manager requests the file system to allocate the entire container when it creates the tablespace. Because it is allocated from the file system, the physical allocation is usually (but not guaranteed) to be continuous. When using the original device container, the Database manager controls the entire device directly and always ensures that the pages in the data block are continuously allocated.
　　
SMS table spaces are particularly suitable for general purposes compared to the DMS table space. The SMS Tablespace provides some performance and low management costs. If you need to achieve the best performance, you should use DMS table space. Because double buffering occurs when moving data using a file container or SMS table space (buffering the data first at the database manager level and then buffering the data again at the file system, which is double buffering), using the device container may provide better performance.

SMS is a system-managed tablespace, and DMS is a table space for database management.
SMS is used in the old DB2 version, and DMS is recommended in the new DB2 release.
DMS can use bare devices and SMS is not available.
DMS can store data objects separately, such as regular data, indexes, large characters, etc., which can be stored separately, and SMS cannot do so.
This is the main difference between SMS and DMS, in addition to some details of the differences, can refer to DB2 related documents

DB2 supports automatic storage of three table spaces for SMS, DMS, DMS