Database managed space
In a DMS (database managed space) 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 that 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. The maximum size of a regular table space is 512 GB for 32 KB pages. The maximum size of a large table space is 64 TB. See SQL and XML limits for the maximum size of regular table spaces for other page sizes.
There are two options for containers when working with DMS table spaces: files and raw devices. When working with file containers, the database manager allocates the entire container at table space creation time. A result of this initial allocation of the entire table space is that the physical allocation is typically, but not guaranteed to be, contiguous even though the file system is doing the allocation. When working with raw device containers, the database manager takes control of the entire device and always ensures the pages in an extent are contiguous. (An extent is defined as the number of pages that the database manager writes to a container before using a different container. )
Planning DMS table spaces
When designing your DMS table spaces and containers, you should consider the following:
- The database manager uses striping to ensure an even distribution of data across all containers. This writes the data evenly across all containers in the table space, placing the extents for tables in round-robin fashion across all containers. Db2® striping is recommended when writing data into multiple containers. If you choose to implement disk striping along with Db2 striping, the extent size of the table space and the strip size of the disk should be identical.
- Unlike SMS table spaces, the containers that make up a DMS table space are not required to be the same size; however, this is not normally recommended, because it results in uneven striping across the containers, and sub-optimal performance. 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. 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 on it. To avoid wasted space, the size of the device and the size of the container should be equivalent. For example, if the device is has a storage capacity equivalent to 5000 pages, and the device container is defined to be 3000 pages, 2000 pages on the device will not be usable.
- By default, one extent in every container is reserved for additional
required space. Only full extents are used, so for optimal space management,
you can use the following formula to determine an appropriate size
to use when allocating a container:
- extent_size * (n + 1)
- The minimum size of a DMS table space is five extents.
- Three extents in the table space are reserved for overhead:
- At least two extents are required to store any user table data. (These extents are required for the regular data for one table, and not for any index, long field or large object data, which require their own extents.)
Attempting to create a table space smaller than five extents will result in an error (SQL1422N).
- Device containers must use logical volumes with a
character special interface,
not physical volumes. - You can use files instead of devices with DMS table spaces. The default table space attribute - NO FILE SYSTEM CACHING in Version 9.5 allows files to perform close to devices with the advantage of not requiring to set up devices. For more information, see Table spaces without file system caching.
- If your workload involves LOBs or LONG VARCHAR data, you might
derive performance benefits from file system caching. Note: LOBs and LONG VARCHARs are not buffered by the database manager's buffer pool.
- Some operating systems allow you to have physical devices greater than 2 GB in size. You should consider dividing the physical device into multiple logical devices, so that no container is larger than the size allowed by the operating system.
When working with DMS table spaces, you should consider associating each container with a different disk. This allows for a larger table space capacity and the ability to take advantage of parallel I/O operations.
The CREATE TABLESPACE statement creates a new table space within a database, assigns containers to the table space, and records the table space definition and attributes in the catalog. When you create a table space, the extent size is defined as a number of contiguous pages. Only one table or object, such as an index, can use the pages in any single extent. All objects created in the table space are allocated extents in a logical table space address map. Extent allocation is managed through space map pages.
The first extent in the logical table space address map is a header for the table space containing internal control information. The second extent is the first extent of space map pages (SMP) for the table space. SMP extents are spread at regular intervals throughout the table space. Each SMP extent is a bit map of the extents from the current SMP extent to the next SMP extent. The bit map is used to track which of the intermediate extents are in use.
The next extent following the SMP is the object table for the table space. The object table is an internal table that tracks which user objects exist in the table space and where their first extent map page (EMP) extent is located. Each object has its own EMPs which provide a map to each page of the object that is stored in the logical table space address map. Figure 1 shows how extents are allocated in a logical table space address map.
