IBM Books

Administration Guide

Designing and Choosing Table Spaces

A table space is a storage model that provides a level of indirection between a database and the tables stored within that database. Table spaces reside in nodegroups. Table spaces allow you to assign the location of database and table data directly onto containers. (A container can be a directory name, a device name, or a file name.) This can provide improved performance, more flexible configuration, and better integrity.

Since table spaces reside in nodegroups, the table space selected to hold a table defines how the data for the table is partitioned across the database partitions in a nodegroup. A single table space can span several containers. It is possible for multiple containers (from one or more table spaces) to be created on the same physical disk (or drive, in Intel terms). For improved performance, each container should use a different disk. The following diagram shows an example of the relationship between tables and table spaces within a database and the containers and disks associated with the database.

Figure 17. Table Spaces and Tables Within a Database


The EMPLOYEE and DEPARTMENT tables are in the HUMANRES table space which spans Containers 0, 1, 2 and 3. The PROJECT table is in the SCHED table space in Container 4. This example shows each container existing on a separate disk.

The database manager attempts to balance the load of the data across the containers. As a result, all containers will be used to store data. The number of pages that the database manager writes to a container before using a different container is called the extent size. The database manager does not always start storing table data in the first container.

The following diagram shows the HUMANRES table space with an extent size of two 4KB pages, and with four containers each with a small number of allocated extents. The DEPARTMENT and EMPLOYEE tables both have 7 pages and span all four containers.

Figure 18. Use of Container and Extents


A database must contain at least three table spaces:

Note:In a partitioned database environment, the catalog node will have all three table spaces and the other database partitions will each have only TEMPSPACE1 and USERSPACE1.

There are two types of table spaces, both of which can be used in a single database:

After understanding the differences between these two types of table spaces, see "Table Space Design Considerations".

System Managed Space Table Space

In a System Managed Space (SMS) table space, the operating system's file system manager allocates and manages the space where the table is to be 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 evenly spreads the data over the table space containers. An SMS table space is the default table space.

In addition to the database physical files, each table has at least one SMS physical file associated with it. See "SMS Physical Files" for a list of these files and a description of their contents.

In an SMS table space, the file is extended one page at a time as the object grows. When inserting a large number of rows, some delay may result from waiting for the system to allocate another page.
Note:If you need improved insert performance, you can consider enabling multipage file allocation. This allows the system to allocate or extend the file by more than one page at a time. You must run db2empfa to enable multipage file allocation. The db2empfa utility must be run on each database partition in a partitioned database. Once multipage file allocation is enabled, it cannot be disabled. See the Command Reference for more information on db2empfa.

You should explicitly define SMS table spaces using the MANAGED BY SYSTEM on the CREATE DATABASE command or on the CREATE TABLESPACE statement. You must consider two key factors when you design your SMS table spaces:

  1. Containers for the table space

    You must specify the number of containers that you wish to use for your table space. It is very important to identify all the containers you want to use, since you cannot add or delete containers after an SMS table space is created. In a partitioned database environment, when a new partition is added to the nodegroup 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). As a result, the maximum size of the table space can be calculated by:

       number of containers * (maximum file system size supported by the
       operating system)
    Note:This formula assumes that there is a distinct file system mapped to each container, and that each file system has the supported maximum of space available. In practice, this may not be the case and the practical maximum database size may be much smaller.
    Note:Care must be taken when defining the containers. There must not be any files or directories on the containers. If there are existing files or directories on the containers, error message "SQL0298N Bad container path." is reported.

  2. Extent size for the table space

    Similar to specifying the number of containers, 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. See "Choosing an Extent Size" for more information.

    When creating a table space, if you do not specify the extent size, the database manager will create the table space using the default extent size, defined by the dft_extent_sz database configuration parameter (see "Default Extent Size of Table Spaces (dft_extent_sz)"). This configuration parameter is initially set based on information provided when the database is created. If the DFT_EXTENTSIZE parameter is not specified on the CREATE DATABASE command, the default extent size will be set to 32.

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

SMS Physical Files

The following files are found within an SMS table space directory container:

File Name

There is one of these files in each container subdirectory, and they are used by the database manager when you connect to the database to verify that the database is complete and consistent.

Table file. All rows of a table are stored here, with the exception of LONG VARCHAR, LONG VARGRAPHIC, CLOB, BLOB or DBCLOB data.

File containing LONG VARCHAR or LONG VARGRAPHIC data (also called "long field data"). This file is only created if LONG VARCHAR or LONG VARGRAPHIC columns exist in the table.

Files containing BLOB, CLOB, or DBCLOB data (also called "LOB data"). These files are only created if BLOB, CLOB, or DBCLOB columns exist in the table.

Files containing allocation and free space information about the SQLxxxxx.LB files.

Index file for a table. All indexes for the corresponding table are stored in this single file. It is only created if indexes have been defined.
Note:When an index is dropped, the space is not physically freed from the index (.INX) file until the index file is deleted. The index file will be deleted if all the indexes on the table are dropped (and committed) or if the table is reorganized. If the index file is not deleted, the space will be marked free once the drop has been committed, and will be reused for future index creations or index maintenance.

Temporary data file for a REORG of a DAT file. While reorganizing a table, the REORG utility creates a table in one of the temporary table spaces. These temporary table spaces can be defined to use containers different from those used for the user-defined tables.

Temporary data file for a REORG of a LF file. Notes for the .DTR file apply here as well.

Temporary data file for a REORG of a LB file. Notes for the .DTR file apply here as well.

Temporary data file for a REORG of a LBA file. Notes for the .DTR file apply here as well.


  1. Do not make any direct changes to these files. They can only be accessed indirectly using the documented APIs and by tools that implement those APIs, including the command line processor commands and the graphical Control Center.

  2. Do not remove these files.

  3. Do not move these files.

  4. The only supported means of backing up a database or table space is through the BACKUP API, including implementations of that API, such as those provided by the command line processor and Control Center.

Database Managed Space Table Space

In a Database Managed Space (DMS) table space, the database manager controls the storage space. The storage model consists of a limited number of devices, whose space is managed by DB2. The Administrator decides which devices to use, and DB2 manages the space on the devices. This table space is essentially an implementation of a special purpose file system designed to best meet the needs of the database manager. The table space definition includes a list of the devices or files belonging to the table space in which data can be stored.

A DMS table space containing user-defined tables and data can be defined as:

When designing your DMS table spaces and containers, you should consider the following:

Adding Containers to DMS Table Spaces

You can add a container to an existing table space to increase its storage capacity with the ALTER TABLESPACE statement. The contents of the table space are then re-balanced across all containers. Access to the table space is not restricted during the re-balancing. If you need to add more than one container, you should add them at the same time either in one ALTER TABLESPACE statement or within the same transaction to prevent the database manager from having to re-balance the containers more than once.

You should check how full the containers for a table space are by using the LIST TABLESPACE CONTAINERS or the LIST TABLESPACES commands. Adding new containers should be done before the existing containers are almost or completely full. The new space across all the containers is not available until the re-balance is complete.

Adding a container which is smaller than existing containers results in a uneven distribution of data. This can cause parallel I/O operations, such as prefetching data, to perform less efficiently than they otherwise could on containers of equal size.

Table Space Design Considerations

Based on the logical design of your database, you should have a good idea of the size of each table, and as a result, of your database. Based on your understanding of this information, you should consider the following to complete your database design as it relates to table space use:

Considerations for Table Space Input and Output (I/O)

The type and design of your table space determines the efficiency of the I/O performed against that table space. Here are some concepts that you should understand before considering further the issues surrounding table space design and use.

Big-block reads
A read where several pages (usually an extent) is retrieved in a single request. Reading several pages at once is more efficient than reading each page separately.

The reading of pages in advance of those pages being referenced by a query. The overall objective is to reduce response time. This can be achieved if the prefetching of pages can occur asynchronously to the execution of the query. The best response time is achieved when either the CPU(s) or the I/O subsystem are operating at maximum capacity.

Page cleaning
As pages are read and modified, these pages accumulate in the database buffer pool. Whenever a page is read in, there must be a buffer pool page to read it into. If the buffer pool is full of modified pages, one of these modified pages must be written out to the disk before the new page can be read in. To prevent the buffer pool from becoming full, page cleaner tasks write out modified pages in order to guarantee the availability of buffer pool pages for use by read requests.

Whenever it is advantageous, DB2 performs big-block reads. This typically occurs when retrieving data that is sequential or partially sequential in nature. The amount of data read in one read depends on the extent size -- the bigger the extent size, the more pages that are read at one time.

How the extent is stored on disk affects the I/O efficiency. When considering a DMS table space using device containers, the data tends to be contiguous on disk and can be read with a minimum of seek time and disk latency. However, if files are being used, the data may have been broken up by the file system and stored in more than one location on disk. This occurs most often when using SMS table spaces where files are extended one page at a time, making fragmentation more likely. Preallocation of a large file for use by a DMS table space tends to be contiguous on disk, especially if the file was allocated in a clean file space.

DB2 performing big-block reads is only one way in which query execution is assisted. You can control how aggressive prefetching can be by tuning the PREFETCHSIZE parameter on the CREATE TABLESPACE statement. (The default value for all table spaces in the database is set by the dft_prefetch_sz configuration parameter.) The PREFETCHSIZE parameter tells DB2 how many pages to read whenever a prefetch is triggered. By setting PREFETCHSIZE to a multiple of the EXTENTSIZE parameter on the CREATE TABLESPACE statement, you can cause multiple extents to be read in parallel. (The default value for all table spaces in the database is set by the dft_extent_sz configuration parameter. The EXTENTSIZE parameter specifies the number of 4K pages that will be written to a container before skipping to the next container.)

For example, suppose you had a table space that used three devices. If you set the PREFETCHSIZE to be three times the EXTENTSIZE, then DB2 can do a big-block read from each device in parallel, thereby significantly increasing the I/O throughput. This assumes that each device is a separate physical device and that the controller has sufficient bandwidth to handle the data stream from each device. Note that DB2 may have to dynamically adjust the prefetch parameters at runtime based on query speed, buffer pool utilization, and other factors.

You should know that some file systems use their own prefetching (such as the Journaled File System on AIX). In some cases, the file system prefetching is set to be more aggressive than the DB2 prefetching. This results in situations where you observe that prefetching for SMS and DMS table spaces with file containers is outperforming prefetching for DMS table spaces with devices. This is misleading since it is likely the result of the additional level of prefetching that is occurring in the file system. DMS table spaces should be able to outperform any equivalent configuration.

For prefetching or even reading to be efficient, a sufficient number of clean buffer pool pages must exist into which to read the data. For example, there could be a parallel prefetch request which reads three extents from a table space and where a modified page must be written out from the buffer pool for each page being read. With the potential for a buffer page to be written out for every page being read in, it is clear that the prefetch request is slowed significantly perhaps to the point where it cannot keep up with the query. Page cleaners should be configured in sufficient numbers to satisfy the prefetch request. At least one page cleaner should be defined for each real disk used by the database. For more information on these topics and performance, see the Chapter 15. "Operational Performance".

Mapping Table Spaces to Buffer Pools

Each table space is associated with a specific buffer pool. The default buffer pool is IBMDEFAULTBP. If another buffer pool is to be associated with a table space, the buffer pool must exist (it is defined with the CREATE BUFFERPOOL statement), and the association is defined when the table space is created (using the CREATE TABLESPACE statement). The association between the table space and the buffer pool can be changed using the ALTER TABLESPACE statement.

Having more than one buffer pool allows you to configure the memory used by the database to improve overall performance and to help with setting performance goals for specific applications. For example, for table spaces with one or more large tables which are accessed randomly by users, the size of the buffer pool can be limited since caching the data pages might not be beneficial. Another example would have the table space for an important online transaction application associated with a buffer pool that is larger than others. In this way, the data pages used by the application could be cached longer in the buffer pool resulting in lower response times. Care must be taken in configuring new buffer pools beyond the default. See "Managing the Database Buffer Pool" for more information on buffer pools.
Note:If you have determined that a page size of 8 KB is required within your database, then each table space with this page size must be mapped to a buffer pool with the same page size.

The storage required for all the buffer pools must be available to the database manager when starting up the database. If DB2 is unable to obtain the storage required for all defined buffer pools, the database manager will start up with default buffer pools (one of 4 KB page size and one of 8 KB page size) of a minimal size, and issue a warning message.

In a partitioned database environment, you can create a buffer pool of the same size for all partitions in the database. You can also create buffer pools of particular sizes on different partitions. For more information on the CREATE BUFFERPOOL statement, see the SQL Reference manual.

Mapping Table Spaces to Nodegroups

In a partitioned database environment, each table space is associated with a specific nodegroup. This allows for the characteristics of the table space to be applied to each node in the nodegroup. The nodegroup must exist (it is defined with the CREATE NODEGROUP statement), and the association between the table space and the nodegroup is defined when the table space is created using the CREATE TABLESPACE statement.

You cannot change the association between table space and nodegroup using the ALTER TABLESPACE statement. You can only change the table space specification for individual partitions within the nodegroup. If not in a partitioned database environment, each table space is associated with a default nodegroup. The default nodegroup when defining a table space is IBMDEFAULTGROUP unless a temporary table space is being defined and then IBMTEMPGROUP is used. For more information on the CREATE NODEGROUP statement, see the SQL Reference manual. For more information on nodegroups and physical database design, see the "Designing Nodegroups".

Mapping Tables to Table Spaces

When determining how to map tables to table spaces in your design, you should consider:

Choosing an Extent Size

The extent size for a table space indicates the number of pages of table data that will be written to a container before data will be written to the next container. When selecting an extent size, you should consider:

Recommendations for Catalog and Temporary Table Spaces

For each database, one 4 KB SMS temporary table space and one 8 KB SMS temporary table space is recommended. (Having temporary table spaces of both sizes gives the query optimizer more flexibility in arriving at the optimal access plan.) The containers for these table spaces should be defined using the same file system (or file systems) to maximize disk sharing and minimize the total disk requirement.

SMS is recommended over DMS because:

DMS should only be considered if you need better performance than is possible if you use SMS.

For each database, a SMS table space for the catalogs is recommended. SMS and not DMS, is recommended for the following reasons:

Given these considerations, a SMS table space is a slightly better choice for the catalogs.

Another factor to consider is if you will need to enlarge the catalog table space in the future. While some platforms have support for enlarging the underlying storage for SMS containers, and while the use of redirected restore to enlarge a SMS table space is available, the use of a DMS table space would allow for easier addition of new containers than the two other choices.

Workload Considerations

The primary type of workload being managed by DB2 in your environment can have an effect on your choice of the type of table space used, and the page size for the table space. An online transaction process (OLTP) workload is characterized by transactions that make random access to data and that usually return small sets of data. Given that the access is random, and to one or a few pages, then prefetching is not possible. The important fact when considering I/O becomes the retrieving of a page of data with the minimum cost possible.

DMS table spaces using device containers perform best in this situation. DMS table spaces with file containers or SMS table spaces are also reasonable choices for OLTP workloads if maximum performance is not required. With little or no sequential I/O expected, the settings for the EXTENTSIZE and PREFETCHSIZE parameters on the CREATE TABLESPACE statement are not important for I/O efficiency.

A query workload is characterized by transactions that make sequential or partially sequential access to data and that usually return large sets of data. Efficient parallel prefetch should be possible in the type of table space chosen. A DMS table space using multiple device containers and where each container is on a separate disk, offers the greatest potential for efficient prefetching. The value of the PREFETCHSIZE parameter on the CREATE TABLESPACE statement should be set to the value of the EXTENTSIZE parameter multiplied by the number of device containers. This allows DB2 to prefetch from all containers in parallel.

A reasonable alternative with a query workload is to use files if the file system has its own prefetching. The files can be either of DMS type using file containers, or of SMS type. Note that if you use SMS, you need to have the directory containers map to separate physical disks in order to achieve I/O parallelism.

A mixed workload is characterized by transactions that are a mixture of the two types mentioned above. Your choice of SMS or DMS table spaces result from combining the considerations and advice from each of the two types of workload. Your goal will be to make single I/O requests as efficient as possible for OLTP workloads, and to maximize the efficiency of parallel I/O for the query workload.

The considerations for determining the page size for a table space are as follows:

Choosing an SMS or DMS Table Space

There are a number of trade-offs to consider when determining which type of table space you should use to store your data.

Advantages of a SMS Table Space: 

Advantages of a DMS Table Space: 

In general, small personal databases are easiest to manage with SMS table spaces. On the other hand, for large, growing databases you will probably only want to use SMS table spaces for the temporary table spaces and separate DMS table spaces, with multiple containers, for each table. In addition, long fields and indexes would be stored on their own table spaces.

If you choose to use DMS table spaces with device containers, you must be willing to tune and administer your environment. For more information, see "Performance Considerations for DMS Devices".

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]