Shulou(Shulou.com)06/01 Report--

Automatic storage of three tablespaces SMS, DMS and DMS supported by DB2

In DB2, a tablespace is the logical layer between a database and the tables stored in that database. Tablespaces are created in the database, and tables are created in tablespaces. The container is a physical storage device. It can be identified by a directory name, device name, or file name. The container is assigned to the tablespace. Tablespaces can span many containers (similar to the concept of datafile in Oracle), which means that you can break the operating system's limit on the amount of data a container can contain. DB2 supports three tablespaces:

System managed space (System-Managed Space,SMS): here, the space is allocated and managed by the operating system's file system manager. Before DB2 9, creating a database or tablespace without any parameters resulted in all tablespaces being created as SMS objects.

Database managed space (Database-Managed Space,DMS): here, the storage space is controlled by the database manager. This tablespace is essentially a special-purpose file system implementation that best meets the needs of database managers.

Automatic storage for DMS (Automatic Storage With DMS): automatic storage is not really a separate tablespace type, but a different way of dealing with DMS storage. DMS containers require a lot of maintenance, and automatic storage is introduced in DB2 V8.2.2 as a way to simplify space management.

SMS tablespaces require very little maintenance. However, compared with DMS tablespaces, SMS tablespaces provide fewer optimization options and poor performance.

SMS, DMS and automatic Storage

Characteristics

SMS

DMS

Automatic storage

Do you want to Striping?

Yes

Yes

Yes

Default Typ

Version 8

None

Version 9

Object management

Operating system

DB2

DB2

Space allocation

On-demand growth / contraction

Pre-allocated; size can shrink and grow, but requires DBA intervention.

Allocate in advance; can grow automatically.

The simplicity of management

Best; little or no tuning is needed.

OK, but some tuning is needed (for example, EXTENTSIZE PREFETCHSIZE

Best; little or no tuning is needed.

Performance

Very good

Best; 5% to 10% more revenue can be obtained by using the original container

Preferably; however, you don't have to use the original container

Maximum tablespace size

64GB (4K pages)

2TB (4K pages)

2TB (4K pages)

In addition to using SMS tablespaces to simplify management, the most significant difference between the two storage models is the maximum size of the tablespaces. When using SMS, DBA can only put 64GB data in the tablespace at most. Changing the page size to 32K can extend this limit to 512GB, but at the cost of less free space on each page. Changing to the DMS model extends the tablespace limit to 2TB (in the case of a 4K page size). If you change the page size to 32K, the free space can grow to 16TB. Although there are other ways to break the 64GB limit for table size, the easiest way is probably to use DMS tablespaces in the first place.

DMS and automatic Storage

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

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

Characteristics

Non-automatic storage

Automatic storage

Creation of containers

Containers must be explicitly provided when creating tablespaces.

Containers cannot be provided when creating tablespaces; they will be automatically allocated by DB2.

Container resizing

By default, automatic tablespace resizing is turned off (AUTORESIZE NO).

By default, automatic tablespace resizing is on (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 container

You can use ALTER TABLESPACE statements (ADD, DROP, BEGIN NEW STRIPE SET, and so on) to perform container operations.

The container operation cannot be performed because the space is managed by DB2 control.

The simplicity of management

You can use redirected restore operations to redefine containers associated with tablespaces.

Containers associated with tablespaces cannot be redefined using redirected restore operations because the space is managed by DB2 control.

There are two ways to store table spaces in DB2, SMS (System Managed Space) and DMS (Database Managed Space).

SMS directly uses the operating system's file system to manage data. The data in the tablespace is divided into blocks on all containers in the system. All tables in the tablespace are given their own file name, which is used in all containers. The file extension indicates the type of data stored in the file.

DMS is controlled by the database for the storage space of the table space. When defining an DMS tablespace, you need to select a list of devices or files to belong to that tablespace.

The main difference between DMS tablespaces and SMS tablespaces is that for DMS tablespaces, storage space is allocated when the tablespace is created, rather than being requested from the operating system when needed. Moreover, there are some differences in the placement of data between the two types of tablespaces. For example, when considering efficient table scans, it is important that data blocks are physically stored continuously. 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 activity 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, because the database manager deals directly with the disk, it is theoretically possible to ensure that pages are physically stored continuously.

It should be noted, however, that when using DMS tablespaces, there are two container options: raw device (raw) and file (file). When using the file option, the database manager requests the file system to allocate the entire container when creating the tablespace. Because it is allocated from the file system, the physical allocation is usually (but not guaranteed) continuous. When using the original device container, the database manager directly controls the entire device and always ensures that the pages in the data block are allocated continuously.

Compared with DMS tablespaces, SMS tablespaces are particularly suitable for general use. SMS tablespaces can provide some performance with low administrative costs. If you need to achieve the best performance, you should choose the DMS tablespace. Because double buffering occurs when moving data using a file container or SMS tablespace (buffering the data first at the database manager level, and then buffering the data again on the file system, which is double buffering, using a device container may provide better performance.

SMS is the tablespace managed by the system, and DMS is the tablespace managed by the database.

SMS is used in the old version of DB2, and DMS is recommended in the new version of DB2.

DMS can use bare devices, but SMS cannot.

DMS can store data objects separately, such as regular data, indexes, large fields, etc., which SMS cannot do.

This is the main difference between SMS and DMS. In addition, there are some differences in details. You can refer to the relevant documentation of DB2.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.