A table space is a storage structure containing tables, indices, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system. Table spaces are stored in database partition groups.
Table spaces consist of one or more containers. A container can be a directory name, a device name, or a file name. A single table space can have several containers. It is possible for multiple containers (from one or more table spaces) to be created on the same physical storage device (although you will get the best performance if each container you create uses a different storage device).
Figure 4. Table spaces and tables in a database
If you are using automatic storage table spaces, the creation and management of
containers is handled automatically by the database manager. If you are not
using automatic storage table spaces, you must define and manage containers
yourself. To get details about the table spaces in a database, use the following
get snapshot for tablespaces or
System Managed Space (SMS) table spaces use the file system manager provided by the operating system to allocate and manage the space where the tables are stored. Within an SMS table space, each container is an operating system directory, and table objects are created as files within that directory. When creating an SMS table space, the user must specify the name of the directory for each of the containers. DB2 will create the tables within the directories used in the table space by using unique file names for each object.
If a table space is created with more than one container, DB2 will balance the amount of data written to the containers. Since containers cannot be dynamically added to an SMS table space once it has been created, it is important to know the size requirements of the table space and create all required containers when the table space is created.
With SMS table spaces:
- All table data and indices share the same table space.
- Each table in a table space is given its own file name used by all containers. The file extension denotes the type of the data stored in the file.
- There is the possibility for dynamic file growth, with an upper boundary on size governed by the number of containers, OS limits on the size of the file system, and OS limits on size of individual files.
- When all space in a single container is allocated, the table space is considered full even if space remains in other containers.
- New containers can only be added to SMS on a partition that does not yet have any containers.
- On Linux or UNIX, the file system size may be increased.
SMS table spaces are very easy to administer, and are recommended for the TEMP table space.
To create an SMS table space, use the following command:
create table space TS1 managed by system using ('path1', 'path2',
'path3'). When the path is specified for an SMS container, it can be an absolute
path or a relative path to the directory. If the directory does not exist, DB2
will create it. If the directory does exist, it cannot contain any files or
subdirectories. For example,
create table space
ts1 managed by system using ('D:\DIR1') specifies the absolute path to the directory. DB2 would create the DIR1 directory
on the D: drive on the database server if it does not already exist.
create table space ts2 managed by system
using ('DIR1') specifies the relative path DIR1. DB2 would create the DIR1 directory under the
database home directory.
The following SQL statements create an SMS table space with three containers on three separate drives or file systems. Note that the table space name is the same, as the examples are showing the differences between the Linux/UNIX and Windows table space definitions:
create tablespace smstbspc managed by system using ('d:\tbspc1', 'e:\tbspc2', 'f:\ tbspc3') create tablespace smstbspc managed by system using ('/dbase/container1', '/dbase/container2', '/dbase/container3')
SMS table spaces can only be altered to change the prefetch size. Containers
cannot be added to an SMS table space using the
alter command. However, containers can be redefined, added, or removed during a redirected
In a database managed space (DMS) table space, the database manager controls the storage space. Unlike SMS table spaces, storage space is pre-allocated on the file system based on container definitions you specify when you create the DMS table space.
The DMS storage model consists of a limited number of files or devices where space is managed by the database manager. You decide which files and devices to use when creating containers, and you manage the space for those files and devices.
A DMS table space containing user defined tables and data can be defined as a large (the default) or regular table space that stores any table data or index data.
With DMS table spaces:
- The database manager uses striping to ensure an even distribution of data across all containers.
- Containers that make up a DMS table space are not required to be the same size. If any container is full, DMS table spaces use available free space from other containers.
- Because space is pre-allocated, it must be available before the table space can be created.
- Enabling DMS table spaces that use file containers for automatic resizing allows the database manager to handle the full table space condition automatically by extending existing containers for you.
- The table space is considered to be full when all of the space within the containers has been used.
- Unlike for SMS table spaces, you can add or extend containers manually,
ALTER TABLESPACEstatement, allowing more storage space to be given to the table space.
The following statement creates a DMS table space without enabling auto-resize (the default):
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING FILE '/db2files/DMS1' 10 M)
To enable the auto-resize feature, specify the
YES clause for the
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES
To create a table space that can grow to 100 MB (per database partition
if the database has multiple database partitions), use the
MAXSIZE clause of the
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES MAXSIZE 100 M
If you do not specify the
MAXSIZE clause, there is no
maximum limit when the auto-resize feature is enabled. The table space will grow
until a file system limit is reached.
INCREASESIZE clause of the
CREATE TABLESPACE statement defines the amount of space used to
increase the table space when there are no free extents within the
table space. You can specify the value as an explicit size or as a percentage, as shown in
the following examples:
Listing 2. Specify value as explicit size or percentage
CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES INCREASESIZE 5 M CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES INCREASESIZE 50 PERCENT
You can enable or disable the auto-resize feature after creating a DMS
table space by using
ALTER TABLESPACE statement with
ALTER TABLESPACE DMS1 AUTORESIZE YES ALTER TABLESPACE DMS1 AUTORESIZE NO
ALTER TABLESPACE statement to change the value
MAXSIZE for a table space that has auto-resize
already enabled, as shown in the following examples:
ALTER TABLESPACE DMS1 MAXSIZE 1 G ALTER TABLESPACE DMS1 MAXSIZE NONE
You can also define the amount of space used to increase the table space when there are no free extents as shown in the following examples:
ALTER TABLESPACE DMS1 INCREASESIZE 5 M ALTER TABLESPACE DMS1 INCREASESIZE 50 PERCENT
Automatic storage allows you to specify one or more storage paths for a database. Then when you create table space, a container file will be created automatically on each storage path by DB2. You can enable or configure automatic storage for a database when it is created, as follows:
db2 create database db_name automatic storage yes db2 create database db_name on db_path1, db_path2
You can add additional storage paths to a database set up for automatic storage
add storage parameter:
db2 alter database db_name add storage on db_path3. You can also drop
a storage path from a database set up for automatic storage
drop storage parameter:
db2 alter database db_name drop storage on db_path3.
Once your database has been set up for automatic storage, you can create table
spaces using this mechanism. You have several ways to take advantage of
automatic storage once the database has been set up that way. You can simply
create a table space in the database (once you are connected to the
db2 create tablespace ts_name.
Or you can create a table space and specify its initial size and growth
db2 create tablespace ts_name initialsize 10M increasesize 10M maxsize 100M
In this example, the table space will start out at 10 MB, and as it gets close to being full, DB2 will automatically extend it by 10 MB at a time, up to its maximum size of 100 MB.
If the database was not set up for automatic storage, you can still use automatic storage for a table space if you create it and specify its storage:
db2 create tablespace ts_name managed by automatic storage
A storage group is a named set of storage paths where data can be stored. Storage groups are configured to represent different classes of storage available to your database system. You can assign table spaces to the storage group that best suits the data. Only automatic storage table spaces use storage groups.
A table space can be associated with only one storage group, but a storage group
can have multiple table space associations. To manage storage group
objects, you can use the
With the table partitioning feature, you can place table data in multiple table spaces. Using this feature, storage groups can store a subset of table data on fast storage while the remainder of the data is on one or more layers of slower storage. Use storage groups to support multi-temperature storage, which prioritizes data based on classes of storage. For example, you can create storage groups that map to the different tiers of storage in your database system. Then the defined table spaces are associated with these storage groups. More information about multi-temperature storage is available in the "Multi-temperature data feature" section.
When you create a database, a default storage group named
IBMSTOGROUP is automatically created. However, a database
created with the
AUTOMATIC STORAGE NO clause does not have a default storage
group. The first storage group created with the
STOGROUP statement becomes the designated default storage group.
There can only be one storage group designated as the default storage group.
If a database has storage groups, the default storage group is used when an automatic storage managed table space is created without explicitly specifying the storage group.
You can designate a default storage group by using either the
CREATE STOGROUP or
statement. When you designate a different storage group as the default storage
group, there is no impact to the existing table spaces using the old default
storage group. To alter the storage group associated with a table space, use the
ALTER TABLESPACE statement. You can
determine which storage group is the default storage group by using the
SYSCAT.STOGROUPS catalog view.
You cannot drop the current default storage group. You can drop the IBMSTOGROUP storage group if it is not designated as the default storage group at that time. If you drop the IBMSTOGROUP storage group, you can create another storage group with that name.
To create a storage group by using the command line, enter
CREATE STOGROUP operational_sg ON '/filesystem1', '/filesystem2',
'/filesystem3'... where operational_sg is the name of the storage group
and /filesystem1, /filesystem2, e/filesystem3 , ... are the storage paths to be added.
You can use the
ALTER STOGROUP statement to alter the
definition of a storage group, including setting media attributes, setting a
data tag, or setting a default storage group. You can also add and remove
storage paths from a storage group.
To add storage paths '/hdd/path1' and '/hdd/path2' to storage group
sg, issue the following
ALTER STOGROUP sg
ADD '/hdd/path1', '/hdd/path2'. To drop storage paths '/db2/filesystem1'
and e'/db2/filesystem2' from storage group
sg, issue the following
ALTER STOGROUP sg DROP '/db2/filesystem1',
You can convert an existing non-automatic storage database to use automatic
storage by using the
CREATE STOGROUP statement to
define the default storage group within a database.
When you define a storage group for a database, existing table spaces are not
automatically converted to use automatic storage. By default, only future table
spaces you create are automatic storage table spaces. You must use the
ALTER TABLESPACE statement to convert existing
table spaces to use automatic storage.
Example 1: Converting a database on Linux or UNIX
Assume that a database is a non-automatic storage database and that
/data1/as and /data2/as are the paths you want to use for
automatic storage table spaces. To convert the database to an automatic storage
database, create a storage group with /data1/as and
/data2/as as paths:
CREATE STOGROUP sg ON
Example 2: Converting a database on Windows operating systems
Assume that a database is a nonautomatic storage database, and that
F:\DB2DATA and G: are the paths you want to use for automatic
storage table spaces. To convert the database to an automatic storage database,
create a storage group with F:\DB2DATA and G: as paths:
CREATE STOGROUP sg ON 'F:\DB2DATA', 'G:'. After creating the default
storage group, to convert an existing DMS table space tbspc1 to use
automatic storage, issue the following statements:
ALTER TABLESPACE tbspc1 MANAGED BY AUTOMATIC STORAGE USING STOGROUP sg ALTER TABLESPACE tbspc1 REBALANCE
The rebalance operation moves data from the non-automatic storage containers to the new automatic storage containers.
There are several ways to check if an existing database is an automatic storage database. You can use any of the following options.
ADMIN_GET_STORAGE_PATHStable function — It returns a list of automatic storage paths for each database storage group, including file system information for each storage path.
SYSIBMADM.SNAPSTORAGE_PATHSadministrative view — It returns a list of automatic storage paths for the database including file system information for each storage path.
db2lookutility — Generates the DDL statements for a database by object type.
You can move tables online and offline using the
In fact, this can be used to move the data in a table to a new table
object of the same name (but with possibly different storage characteristics
such as a different table space) while the data remains online and available for
access. You can also generate a new optimal compression dictionary when a table
ADMIN_MOVE_TABLE stored procedure creates a protocol table comprising rows
with status information and configuration options related to the table to
be moved. The return set from this procedure is the rows from that protocol
table related to the table to be moved.
You can invoke the
ADMIN_MOVE_TABLE in one of two ways:
Method 1 — Modify only certain parts of the table definition for the target table. Fill out the data_tbsp, index_tbsp, and lob_tbsp parameters while calling the procedure, leaving the other optional parameters blank.
This example calls the stored procedure using the first method, where the target table is defined within the procedure, to move a table named T1, located in the schema SCHEMA1. Additionally, the column definitions of the target table are passed to the procedure.
Listing 3. Example for the stored procedure using the first method
CALL SYSPROC.ADMIN_MOVE_TABLE( 'SCHEMA1', 'T1', 'ACCOUNTING', 'ACCOUNT_IDX', 'ACCOUNT_LONG', '', '', '', 'CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB', '', 'MOVE')
The above example moves the table SCHEMA1.T1 to a new table with the same name that has the column definitions ('CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB') and resides in table space 'ACCOUNTING' with its indices table space 'ACCOUNT_IDX' and its LOBs table space 'ACCOUNT_LONG'.
Method 2 — create the target table and provide its name to the procedure. This provides you with more control and flexibility by allowing you to create the target table beforehand, rather than having the stored procedure create it.
This example is equivalent to the previous one, but it calls the stored procedure using the second method, where the target table is created outside the procedure and is named within the target_tabname parameter, to move the same table as in the previous example.
Listing 4. Example for the stored procedure using the second method
CREATE TABLE SCHEMA1.T1_TGT ( CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB) IN ACCOUNTING INDEX IN ACCOUNT_IDX LONG IN ACCOUNT_LONG'
CALL SYSPROC.ADMIN_MOVE_TABLE( 'SCHEMA', 'T1', 'T1_TGT', '', 'MOVE')
For online data movement:
- The procedure creates a shadow table to which the data are copied.
- Any changes to the source table during the copy phase are captured using triggers and placed in a staging table.
- After the copy phase is completed, the changes captured in the staging table are replayed to the shadow copy.
- Following that, the stored procedure briefly takes the source table offline and assigns the source table name and index names to the shadow copy and its indices.
- The shadow table is then brought online, replacing the source table. By
default, the source table is dropped, but you can use the
KEEPoption to retain it under a different name.
Obviously, the online operation costs more server resources (disk space and processing power), so make sure you only use it if you value availability more than cost, space, move performance, and transaction overhead. Additionally, avoid performing online moves for tables without indices, particularly unique ones as it might result in deadlocks and complex or expensive replay.
If the online move fails, rerun it:
- Fix the problem that caused the table move to fail.
- Determine the stage that was in progress when the table move failed by querying the SYSTOOLS.ADMIN_MOVE_TABLE protocol table for the status.
- Call the stored procedure again, specifying the applicable option:
- If the status of the procedure is INIT, use the
- If the status of the procedure is COPY, use the
- If the status of the procedure is REPLAY, use the
- If the status of the procedure is CLEANUP, use the
- If the status of the procedure is INIT, use the
You can cancel the move by specifying the CANCEL option for the stored procedure if the status of an online table move is not COMPLETED or CLEANUP.
To find the state for the table spaces in a database:
list tablespaces show detail.
A table space can have a number of different states, as shown below.
Table 3. Table space states
|Roll-forward in progress||0x40|
|Reorg in Progress||0x400|
|Backup in Progress||0x800|
|Storage Must be Defined||0x1000|
|Restore in Progress||0x2000|
|Offline and Not Accessible||0x4000|
|Load in Progress||0x20000|
|Storage May be Defined||0x2000000|
|DMS Rebalance in Progress||0x10000000|
|Table Space Deletion in Progress||0x20000000|
|Table Space Creation in Progress||0x40000000|
More details about each of the above states are available at Information Center.