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.
See Creating a Table Space or Altering a Table Space for information on how to create or alter a table space.
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 18. 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 4 KB 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 19. Use of Container and Extents
 |
A database must contain at least three table spaces:
By default, one table space, USERSPACE1, is created. IBMDEFAULTGROUP is the default nodegroup for this table space.
You should specify a table space name when you create a table, or the results may not be what you intend. If you do not specify a table space name, the table is placed according to the following rules: If the table space IBMDEFAULTGROUP exists with a sufficient page size, then use it. Otherwise, if user-created table spaces exist, then choose one which is of the smallest page size that is sufficient for this table and use it. Otherwise, use USERSPACE1 if it exists with a sufficient page size. If none of these exist with a sufficient page size, then the table creation fails.
The sufficient page size of a table is determined by either the byte count of the rows or the number of columns. The maximum number of bytes allowed in a row of a table is dependent on the page size of the table space in which the table is created. The possible values for the page size are 4 KB (the default), 8 KB, 16 KB, and 32 KB. You can use a table space with one page size for the base table, and a different table space with a different page size for LONG or LOB data. (Recall that SMS does not support tables that span table spaces, while DMS does.) If the number of columns or the row size exceeds the limits for a table space's page size, an error is returned (SQLSTATE 42997).
One or more temporary table spaces, which contain temporary tables. By default one table space called TEMPSPACE1 is created. A database must have at least one temporary table space. IBMTEMPGROUP is the default nodegroup for this table space.
If a database uses more than one temporary table space, temporary objects are allocated among the temporary table spaces in a round robin fashion.
An application may encounter a temp-tablespace-full condition when one of the table spaces is full even if there is still room in the other temporary table spaces.
You should define a single SMS temporary table space with a page size equal to the page size used in the majority of your regular table spaces. This should be suitable for typical environments and workloads. For detailed guidelines for those environment and workloads not as typical see Recommendations for Temporary 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.
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. Refer to 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:
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 limited 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. |
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 (refer to the Administration Guide, Performance for more information on this parameter). 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:
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.
Check the limitations on size and the number of containers on the platform where you are working as part of your determination regarding the number of containers and the extent size for the table space.
When you create the table space, you can specify containers that reside on different files systems and as a result increase the amount of data that can be stored in the database.
The first table data file (SQL00001.DAT) is created in the first container specified for the table space, and this file is allowed to grow to the extent size. After it reaches this size, the database manager writes the data to SQL00001.DAT in the next container. This process continues until all of the containers contain SQL00001.DAT files, at which time, the database manager returns to the first container to which data was written for that table. This process (known as striping) continues through the container directories until either a container becomes full at which time a -289 error is returned; or, no more space can be allocated from the operating system at which time a disk-full error is returned. This mechanism is also used for index (SQLnnnnn.INX), long field (SQLnnnnn.LF), and LOB (SQLnnnnn.LB and SQLnnnnn.LBA) files.
| Note: | The SMS table space is full as soon as any one of its containers is full. Thus, it is important to allocate the same amount of space for each container. |
To help spread data across the containers more evenly, the database manager determines the container to start writing a table's data by taking the table's ID (1 in the above example) modulo the number of containers. Containers are numbered sequentially starting at 0.
See SMS Physical Files for more information about the files used in an SMS table space.
The following files are found within an SMS table space directory container:
| 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. |
Notes:
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:
Because it is preallocated, the space must be available before the table space can be created. When using device containers, the device must also exist with enough space for the definition of the container. Each device can have only one container defined to it, so to avoid wasted space, the size of the device and the size of the container should be equivalent. If, for example, the device is allocated with 5000 pages and the device container is defined to allocate 3000 pages, then 2000 pages on the device will not be usable.
(extent size * n) + 1
where, extent size is the size of each extent for the table space and n is the number of extents you want to store in the container.
| Note: | LOBs and LONG VARCHARs are not buffered by DB2's buffer pool. |
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.
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:
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.
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 4 KB 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, refer to the Administration Guide, Performance.
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. Refer to "Managing the Database Buffer Pool" in the Administration Guide, Performance for more information on this topic.
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 each of 4 KB, 8 KB, 16 KB, and 32 KB page sizes) 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, refer to the SQL Reference manual.
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, refer to the SQL Reference manual. For more information on nodegroups and physical database design, see the Designing Nodegroups.
When determining how to map tables to table spaces in your design, you should consider:
At a minimum, you should ensure that the table space you choose is in the nodegroup with the partitioning you desire.
If you plan to store many small tables in a table space, consider using SMS for that table space. The DMS advantages with I/O and space management efficiency are not as important with small tables. The SMS advantages of allocating space one page at a time, and only when needed, are more attractive with smaller tables. If one of your tables is larger, or you need faster access to the data in the tables, then a DMS table space with a small extent size should be considered.
You may wish to use a separate table space for each very large table and group all small tables together in a single table space. This separation also allows you to select an appropriate extent size based on the table space usage. (See Choosing an Extent Size for additional information.)
You may, for example, have tables containing historical data that is used infrequently and as a result the end-user may be willing to accept a longer response time for queries executed against this data. In this situation, you could use a different table space for the historical tables and assign this table space to less expensive physical devices that have slower access rates.
Alternatively, you may be able to identify some essential tables which require high availability and fast response time. You may want to put these tables into a table space assigned to a fast physical device that can help support these important data requirements.
Using DMS table spaces, you can also spread your table across three different table spaces: one for index data; one for LOB and long field data; one for regular table data. This allows you to choose the table space characteristics and the physical devices supporting those table spaces to best suit the type of data. For example, you could put your index data on the fastest devices you have available, and as a result, obtain significant performance improvements. If you split a table across DMS table spaces, you should consider backing up and restoring all parts of the table together if ROLLFORWARD recovery is enabled. SMS table spaces do not support the spreading of your table across table spaces in this fashion.
Some administration functions can be performed at the table space level instead of the database or table level. For example, taking a back up of a table space instead of a database can help you make better use of your time and resources. It allows you to frequently back up table spaces with large volumes of changes, while only occasionally backing up tables spaces with very low volumes of changes.
You may restore a database or a table space. If unrelated tables do not share table spaces, you have the ability to restore a smaller portion of your database, and as a result, reduce the time and resource requirements for the restore utility.
A general rule-of-thumb could be to group related tables in a set of table spaces. These tables could be related through referential constraints, or through other business constraints defined on the tables using triggers.
Another aspect to consider for administration of your tables, is how often you might want to drop and redefine a particular table. If the frequency is high, you may want to define the table in its own table space, since it is more efficient to drop a DMS table space than it is to drop a table.
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:
Space in DMS table spaces is allocated to a table an extent at a time. As the table is populated and an extent becomes full, a new extent is allocated.
A table is made up of the following separate table objects:
Each table object is stored separately, and therefore each allocates new extents as needed. Each table object is also paired up with a meta-data object called an extent map, which describes all the extents in the table space which belong to the table object. Space for extent maps is also allocated an extent at a time.
The initial allocation of space for a table, therefore, is two extents for each table object. If you have many small tables in a table space, you may have a relatively large amount of space allocated to store a relatively small amount of data. In such a case, you should specify a small extent size, or use an SMS table space which allocates pages one at a time.
If, on the other hand, you have a very large table that has a high growth rate, and you are using an DMS table space with a small extent size, you could have unnecessary overhead related to the frequent allocation of additional extents.
If access to the tables includes many queries or transactions that process large quantities of data, prefetching data from the tables may provide significant performance benefits. (Refer to Administration Guide, Performance for information about data prefetching and recommendations on its relationship to the extent size.)
There must be enough space in the containers for five extents of the table space, otherwise the table space will not be created.
It is recommended that you define a single SMS temporary table space with a page size equal to the page size used in the majority of your regular table spaces. This should be suitable for typical environments and workloads. However, it can be advantageous, in specific workloads, to experiment with different temporary table space configurations. The following points should be considered:
| Note: | You can also perform reorganization without a temporary table space by reorganizing the table "inplace"; that is, directly in the target table space. Of course, this "inplace" reorganization requires that there be extra space in the target table space for the reorganization process. Refer to Administration Guide, Performance for additional information on reorganization of tables. |
| Note: | Catalog table spaces are restricted to use the 4 KB page size. As such, the database manager always enforces the existence of a 4 KB temporary table space to enable catalog table reorganizations. |
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.
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:
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:
You might want to separate your table data for performance reasons, or to increase the amount of data stored for a table. For example, you could have a table with 64 GB of regular table data, 64 GB of index data and 2 TB of long data.
| Note: | If you are using 8 KB pages, the table data and index data can be as much as 128 GB. If you are using 16 KB pages, the table data and index data can be as much as 256 GB. If you are using 32 KB pages, the table data and index data can be as much as 512 GB. |
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. Refer to "Performance Considerations for DMS Devices" in the Administration Guide, Performance for more information.
This section describes how to optimize performance when data is placed on Redundant Array of Independent Disks (RAID) devices. In general, you should do the following for each table space that uses a RAID device:
DB2_PARALLEL_IO
When reading data from, or writing data to table space containers, DB2 may use parallel I/O if the number of containers in the database is greater than 1. However, there are situations when it would be beneficial to have parallel I/O enabled for single container table spaces. For example, if the container is created on a single RAID device that is composed of more than one physical disk, you may want to issue parallel read and write calls.
To force parallel I/O for a table space that has a single container, you can use the DB2_PARALLEL_IO registry variable. This variable can be set to "*" (asterisk), meaning every table space, or it can be set to a list of table space IDs separated by commas. For example:
db2set DB2_PARALLEL_IO=* {turn parallel I/O on for all table spaces}
db2set DB2_PARALLEL_IO=1,2,4,8 {turn parallel I/O on for table spaces 1, 2, 4, and 8}
After setting the registry variable, DB2 must be stopped (db2stop), and then restarted (db2start), for the changes to take effect.
DB2_STRIPED_CONTAINERS
Currently when creating a DMS table space container (device or file), a one-page tag is stored at the beginning of the container. The remaining pages are available for data storage by DB2, and are grouped into extent-sized blocks.
When using RAID devices for table space containers, it is suggested that the table space be created with an extent size that is equal to, or a multiple of, the RAID stripe size. However, because of the one-page container tag, the extents will not line up with the RAID stripes, and it may be necessary during an I/O request to access more physical disks than would be optimal.
DMS table space containers can now be created in such a way that the tag exists in its own (full) extent. This avoids the problem described above, but it requires an extra extent of overhead within the container. To create containers in this fashion, you must set the DB2 registry variable DB2_STRIPED_CONTAINERS to "ON", and then stop and restart your instance:
db2set DB2_STRIPED_CONTAINERS=ON db2stop db2start
Any DMS container that is created (with CREATE TABLESPACE or ALTER TABLESPACE) will have new containers with tags taking up a full extent. Existing containers will remain unchanged.
To stop creating containers with this attribute, reset the variable, and then stop and restart your instance:
db2set DB2_STRIPED_CONTAINERS= db2stop db2start
The Control Center and the LIST TABLESPACE CONTAINERS command will not show whether a container has been created as striped or not. They will continue to use "file" or "device", depending on how the container was created. To verify that a container was created as striped, you can use the /DTSF option of DB2DART to dump table space and container information, and look at the type field for the container in question. Also, the query container APIs, sqlbftcq( ) and sqlbtcq( ), can be used to create a simple application that will display the type.
Definitions for these new types have been added to the sqlutil.h header file:
#define SQLB_CONT_STRIPED_DISK 5 /* DMS: Striped disk */ #define SQLB_CONT_STRIPED_FILE 6 /* DMS: Striped file */