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. Unlike database managed (DMS)
table spaces, storage space is not pre-allocated when the table space
is created; it 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) will be allocated when space is required. Otherwise,
space will be allocated one page at a time.
Multi-page file
allocation only affects 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 has been 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 has been 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 very 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:
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 will create 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 will be 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.