How does SMS and DMS in the DB2 database correctly understand it?

The following articles mainly describe the SMS and DMS in the DB2 database. There are two storage methods for tablespace In the DB2 database: SMS (System Managed Space) and DMS (Database Managed Space ). SMS directly uses the file system of the operating system to manage data.

Data in the tablespace is divided by data blocks on all containers in the system. All tables in the tablespace are assigned their own file names, which are used in all containers. The file extension indicates the Data Type stored in the file.

DMS controls the storage space of tablespaces in databases. When defining a DMS tablespace, You need to select a device or a file list to make it belong to the tablespace.

The main difference between the DMS tablespace and the SMS tablespace is that for the DMS tablespace, the bucket is allocated when the tablespace is created, rather than applying to the operating system as needed. In addition, there are some differences in data placement between the two types of tablespaces. For example, it is important to physically store data blocks for efficient table scans. For SMS, the file system of the operating 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 storage location, these logically contiguous pages may be stored consecutively or not continuously allocated. For DMS, because it is a DB2 database management program that deals directly with disks, it may theoretically ensure that pages are physically stored continuously.

However, it should be noted that when using DMS tablespace, there are two container options: raw of the original device) and file ). When the file option is used, the DB2 database management program applies to the file system to allocate the entire container when creating the tablespace. Because it is allocated from the file system, physical allocation is usually but not guaranteed) consecutive. When the original device container is used, the database management program directly controls the entire device and can always ensure that pages in the data block are continuously allocated.

Compared with DMS tablespaces, SMS tablespaces are especially suitable for general purposes. SMS tablespace provides certain performance and has low management costs. To achieve optimal performance, use DMS tablespace. Because the use of file containers or SMS tablespace to move data will cause double buffering at the DB2 database management program level, first buffering data, and then buffering data in the file system, which is a double buffering ), therefore, using a device container may provide better performance.

The above content is the description of SMS and DMS in the DB2 database, hoping to help you in this regard.