DB2 Version 10.1 for Linux, UNIX, and Windows

System managed space

In an SMS (System Managed Space) table space, the operating system's file system manager allocates and manages the space where the table is stored. Storage space is allocated on demand.

The SMS storage model consists of files representing database objects; for example, each table has at least one physical file associated with it. When you set up the table space, you decide the location of the files by creating containers. Each container in an SMS table space is associated with an absolute or relative directory name. Each of these directories can be located on a different physical storage device or file system. The database manager controls the names of files created for objects in each container, and the file system is responsible for managing them. By controlling the amount of data written to each file, the database manager distributes the data evenly across the table space containers.

Important: The SMS table space type has been deprecated in Version 10.1 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

How space is allocated

In an SMS table space, space for tables is allocated on demand. The amount of space that is allocated is dependent on the setting of the multipage_alloc database configuration parameter. If this configuration parameter is set to YES (the default), then a full extent (typically made up of two or more pages) is allocated when space is required. Otherwise, space is allocated one page at a time.

Multi-page file allocation affects only the data and index portions of a table. This means that the files used for long data (LONG VARCHAR, LONG VAR GRAPHIC), large objects (LOBs) are not extended one extent at a time.

Note: Multipage file allocation is not applicable to temporary table spaces that use system managed space.

When all space in a single container in an SMS table space is consumed, the table space is considered full, even if space remains in other containers. Unlike DMS table spaces, containers cannot be added to an SMS table space after it is created. Add more space to the underlying file system to provide more space to the SMS container.

Planning SMS table spaces

When considering the use of SMS table spaces, you must consider two factors:

The number of containers the table space will need. When you create an SMS table space, you must specify the number of containers that you want your table space to use. It is important to identify all the containers you want to use, because you cannot add or delete containers after an SMS table space is created. The one exception to this is in a partitioned database environment; when a new database partition is added to the database partition group for an SMS table space, the ALTER TABLESPACE statement can be used to add containers to the new database partition.
The maximum size of the table space can be estimated by the formula:
- n × maxFileSystemSize
where n is the number of containers and maxFileSystemSize represents the maximum file system size supported by the operating system.
This formula assumes that each container is mapped to a distinct file system, and that each file system has the maximum amount of space available, and that each file system is of the same size. In practice, this might not be the case, and the maximum table space size might be much smaller. There are also SQL limits on the size of database objects, which might affect the maximum size of a table space.

Attention: The path you specify for the SMS table space must not contain any other files or directories.
The extent size for the table space. The extent size is the number of pages that the database manager writes to a container before using a different container. The extent size can only be specified when the table space is created. Because it cannot be changed later, it is important to select an appropriate value for the extent size.
If you do not specify the extent size when creating a table space, the database manager creates the table space using the default extent size, defined by the dft_extent_sz database configuration parameter. This configuration parameter is initially set based on information provided when the database is created. If the value for dft_extent_sz is not specified for the CREATE DATABASE command, the default extent size is set to 32.

Containers and extent size

To choose appropriate number of containers and the extent size for the table space, you must understand:

The limitation that your operating system imposes on the size of a logical file system. For example, some operating systems have a 2 GB limit. Therefore, if you want a 64 GB table object, you will need at least 32 containers on this type of system. When you create the table space, you can specify containers that reside on different file systems and, as a result, increase the amount of data that can be stored in the database.
How the database manager manages the data files and containers associated with a table space. The first table data file (by convention, SQL00002.DAT) is created in one of the table space containers. The database manager determines which one, based on an algorithm that takes into account the total number of containers together with the table identifier. This file is allowed to grow to the extent size. After it reaches this size, the database manager writes data to SQL00002.DAT in the next container. This process continues until all of the containers contain SQL00002.DAT files, at which time the database manager returns to the starting container. This process, known as striping, continues through the container directories until a container becomes full (SQL0289N), or no more space can be allocated from the operating system (disk full error). Striping applies to the block map files (SQLnnnnn.BKM), to index objects, as well as other objects used to store table data. If you choose to implement disk striping along with the striping provided by the database manager, the extent size of the table space and the strip size of the disk should be identical.
Note: The SMS table space is deemed to be full as soon as any one of its containers is full. Thus, it is important to have the same amount of space available to each container.

SMS table spaces are defined using the MANAGED BY SYSTEM option on the CREATE DATABASE command, or on the CREATE TABLESPACE statement.