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. They 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.
For information about creating or altering a table space, see Creating a Table Space , or Altering a Table Space .
Since table spaces reside in nodegroups, the table space selected to hold a table defines how the data for that table is distributed 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). For improved performance, each container should use a different disk. Figure 36 illustrates the relationship between tables and table spaces within a database, and the containers associated with that database.
Figure 36. 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 data load across containers. As a result, all containers are 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.
Figure 37 shows the HUMANRES table space with an extent size of two 4 KB pages, and four containers, each with a small number of allocated extents. The DEPARTMENT and EMPLOYEE tables both have seven pages, and span all four containers.
Figure 37. Containers and Extents
 |
A database must contain at least three table spaces:
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 user-created table spaces exist, choose the one with the smallest page size large enough for this table. Otherwise, use USERSPACE1 if it's page size is large enough for the table. If no table spaces with a large enough page size exist, the table is not created.
A table's page size is determined either by row size, or the number of columns. The maximum allowable length for a row is dependent upon the page size of the table space in which the table is created. Possible values for 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, but that 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).
If a database uses more than one temporary table space, temporary objects are allocated among the temporary table spaces in a round-robin fashion.
If queries are running against tables in table spaces that are defined with a page size larger than the 4 KB default (for example, an ORDER BY on 1012 columns), some of them may fail. This will occur if there are no temporary table spaces defined with a larger page size. You may need to create a temporary table space with a larger page size (8 KB, 16 KB, or 32 KB). Any DML (Data Manipulation Language) statement could fail unless there exists a temporary table space with the same page size as the largest page size in the user table space.
You should define a single SMS temporary table space with a page size equal to the page size used in the majority of your user table spaces. This should be adequate for typical environments and workloads. See also Recommendations for Temporary Table Spaces.
In a partitioned database environment, the catalog node will contain all three default table spaces, and the other database partitions will each contain only TEMPSPACE1 and USERSPACE1.
There are two types of table space, both of which can be used in a single database:
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. An SMS table space is the default table space.
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, a file is extended one page at a time as the object grows. 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. In a partitioned database environment, this utility must be run on each database partition. Once multipage file allocation is enabled, it cannot be disabled. For more information about db2empfa, refer to the Command Reference.
You should explicitly define SMS table spaces using the MANAGED BY SYSTEM option 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 want to use for your table space. 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. 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). The maximum size of the table space can be estimated by:
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.
| Note: | Care must be taken when defining the containers. If there are existing files or directories on the containers, an error (SQL0298N) is returned. |
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. For more information, see Choosing an 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 (refer to the Administration Guide: Performance for more information about this parameter). This configuration parameter is initially set based on information provided when the database is created. If the DFT_EXTENT_SZ parameter is not specified on the CREATE DATABASE command, the default extent size will be set to 32.
To choose 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.
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.
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 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. 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 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 distribute data across the containers more evenly, the database manager determines which container to use first by taking the table identifier (1 in the above example) modulo the number of containers. Containers are numbered sequentially, starting at 0.
For more information about the files used in an SMS table space, see SMS Physical Files.
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 creation or index maintenance. |
Notes:
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 whose space is managed by DB2. The Administrator decides which devices to use, and DB2 manages the space on those devices. The 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 that belong to the table space, and 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:
(extent_size * n) + 1where extent_size is the size of each extent in the table space, and n is the number of extents that you want to store in the container.
The ALTER TABLESPACE statement lets you add a container to an existing table space to increase its storage capacity. The contents of the table space are then rebalanced across all containers. Access to the table space is not restricted during rebalancing. 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 rebalance 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 command. Adding new containers should be done before the existing containers are almost or completely full. The new space across all containers is not available until rebalancing 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.
This section covers the following topics:
The type and design of your table space determines the efficiency of the I/O performed against that table space. Following are concepts that you should understand before considering further the issues surrounding table space design and use:
Whenever it is advantageous to do so, 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 operation depends on the extent size -- the bigger the extent size, the more pages can be read at one time.
How the extent is stored on disk affects I/O efficiency. In 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. If files are being used, however, 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. A large file that has been pre-allocated for use by a DMS table space tends to be contiguous on disk, especially if the file was allocated in a clean file space.
You can control the degree of prefetching 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 database configuration parameter.) The PREFETCHSIZE parameter tells DB2 how many pages to read whenever a prefetch is triggered. By setting PREFETCHSIZE to be 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 database 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, DB2 can do a big-block read from each device in parallel, thereby significantly increasing 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 run time based on query speed, buffer pool utilization, and other factors.
Some file systems use their own prefetching method (such as the Journaled File System on AIX). In some cases, file system prefetching is set to be more aggressive than DB2 prefetching. This may cause prefetching for SMS and DMS table spaces with file containers to appear to outperform prefetching for DMS table spaces with devices. This is misleading, because 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. For example, there could be a parallel prefetch request that reads three extents from a table space, and for each page being read, one modified page is written out from the buffer pool. The prefetch request may be slowed down 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 about these topics, 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. For table spaces with one or more large tables that are accessed randomly by users, the size of the buffer pool can be limited, because caching the data pages might not be beneficial. The table space for an online transaction application might be associated with a larger buffer pool, so that the data pages used by the application can be cached longer, resulting in faster response times. Care must be taken in configuring new buffer pools. For more information on this topic, refer to "Managing the Database Buffer Pool" in the Administration Guide: Performance.
| Note: | If you have determined that a page size of 8 KB, 16 KB, or 32 KB is required by your database, each table space with one of these page sizes 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 the database is started. If DB2 is unable to obtain the required storage, the database manager will start up with default buffer pools (one each of 4 KB, 8 KB, 16 KB, and 32 KB page sizes), and issue a warning.
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 different sizes on different partitions. For more information about the CREATE BUFFERPOOL statement, refer to the SQL Reference.
In a partitioned database environment, each table space is associated with a specific nodegroup. This allows 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. In a single-partition environment, each table space is associated with the default nodegroup. The default nodegroup, when defining a table space, is IBMDEFAULTGROUP, unless a system temporary table space is being defined; then IBMTEMPGROUP is used. For more information about the CREATE NODEGROUP statement, refer to the SQL Reference. For more information about nodegroups and physical database design, see Designing Nodegroups.
When determining how to map tables to table spaces, you should consider:
At a minimum, you should ensure that the table space you choose is in a nodegroup with the partitioning you want.
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, 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; 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 distribute your table data across three different table spaces: one for index data; one for LOB and long field data; and 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 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 roll-forward recovery is enabled. SMS table spaces do not support this type of data distribution across table spaces.
Some administrative functions can be performed at the table space level instead of the database or table level. For example, taking a backup 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 can restore a database or a table space. If unrelated tables do not share table spaces, you have the option to restore a smaller portion of your database and reduce costs.
A good approach is to group related tables in a set of table spaces. These tables could be related through referential constraints, or through other defined business constraints.
If you need to drop and redefine a particular table often, you may want to define the table in its own table space, because it is more efficient to drop a DMS table space than it is to drop a table.
The extent size for a table space represents 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 one 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 each object allocates new extents as needed. Each table object is also paired with a meta-data object called an extent map, which describes all of the extents in the table space that belong to the table object. Space for extent maps is also allocated one 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 a 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 its relationship to the extent size.)
If there is not enough space in the containers for five extents of the table space, 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 to experiment with different temporary table space configurations and workloads. The following points should be considered:
You can also reorganize 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. For additional information about table reorganization, refer to Administration Guide: Performance.
| Note: | Catalog table spaces are restricted to using 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. |
An SMS table space is recommended for database catalogs, for the following reasons:
Given these considerations, an SMS table space is a somewhat better choice for the catalogs.
Another factor to consider is whether 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 you can use redirected restore to enlarge an SMS table space, the use of a DMS table space facilitates the addition of new containers.
The primary type of workload being managed by DB2 in your environment can affect your choice of what table space type to use, and what page size to specify. An online transaction processing (OLTP) workload is characterized by transactions that need random access to data and that usually return small sets of data. Given that the access is random, and involves one or a few pages, prefetching is not 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 the PREFETCHSIZE parameters on the CREATE TABLESPACE statement are not important for I/O efficiency.
A query workload is characterized by transactions that need sequential or partially sequential access to data, and that usually return large sets of data. A DMS table space using multiple device containers (where each container is on a separate disk) offers the greatest potential for efficient parallel 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 for 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 to achieve I/O parallelism.
Your goal for a mixed workload is to make single I/O requests as efficient as possible for OLTP workloads, and to maximize the efficiency of parallel I/O for query workloads.
The considerations for determining the page size for a table space are as follows:
pagesize / 255there will be wasted space on each page (there is a maximum of 255 rows per page). In this situation, a smaller page size may be more appropriate.
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 an 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. If you are using 8 KB pages, the table data and the index data can be as much as 128 GB. If you are using 16 KB pages, it can be as much as 256 GB. If you are using 32 KB pages, the table data and the index data can be as much as 512 GB.
| Note: | On Solaris and PTX (IBM NUMA-Q), DMS table spaces with raw devices is strongly recommend for performance-critical workloads. |
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, you will probably want to store long field data and indexes 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, refer to "Performance Considerations for DMS Devices" in the Administration Guide: Performance.
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 the CREATE TABLESPACE or the ALTER TABLESPACE statement) will have 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 do not show whether a container has been created as a striped container. They use the label "file" or "device", depending on how the container was created. To verify that a container was created as a striped container, you can use the /DTSF option of DB2DART to dump table space and container information, and then look at the type field for the container in question. The query container APIs (sqlbftcq and sqlbtcq), can be used to create a simple application that will display the type.