Filter
Top Products
accommodate tables with these page sizes. DB2 automatically places tables and
indexes in the available tablespaces using its internal logic.
SMS Tablespaces in DB2
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. The storage
model typically consists of many files, representing table objects, stored in the file
system space. The user decides on the location of the files, DB2 controls their
names, 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. By default, the initial table spaces created
at database creation time are SMS.
SMS table spaces are defined using the MANAGED BY SYSTEM option in the
CREATE DATABASE command, or in the CREATE TABLESPACE statement.
Consider two key factors when you design your SMS table spaces:
v Containers for the table space. You must specify the number of containers that
you want to use for your table space. It is crucial to identify all the containers
you want to use, because you cannot add or delete containers after an SMS table
space is created.
v In a partitioned database environment, when a new partition is added to the
database partition group for an SMS table space, the ALTER TABLESPACE
statement can be used to add containers for the new partition.
Each container used for an SMS table space identifies an absolute or relative
directory name. Each of these directories can be located on a different file system
(or physical disk). Estimate the maximum size of the table space with:
Number of containers * maximum file system size supported by the operating
system
This formula assumes that there is a distinct file system mapped to each
container, and that each file system has the maximum amount of space available.
In practice, this may not be the case, and the maximum table space size may be
much smaller. There are also SQL limits on the size of database objects, which
may affect the maximum size of a table space.
v Extent size for the table space
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 as
defined by the dft_extent_sz database configuration parameter. This
configuration parameter is initially set based on the information provided when
the database is created. If the dft_extent_sz parameter is not specified in the
CREATE DATABASE command, the default extent size is set to 32.
DMS Tablespaces in IBM DB2
In a DMS (Database Managed Space) table space, the database manager controls
the storage space. The storage model consists of a limited number of devices or
files whose space is managed by DB2. The database administrator decides which
devices and files to use, and DB2 manages the space on those devices and files.
The table space is essentially an implementation of a special-purpose file system
designed to meet the needs of the database manager.
Performance Management 45