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.
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.
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
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. Db2® striping is
recommended when writing data into multiple containers. 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.