Table Spaces

A table space is a logical entity used to define where tables and indexes will be stored within a database. All DB2 tables and indexes reside in table spaces, allowing complete control over where the table and index data are physically stored.

A table space can be created using one or more underlying physical storage devices called containers. This provides the ability to create a physical database design that provides optimal performance in any physical environment.

Details about the table spaces in a database can be obtained using:

1. GET SNAPSHOT FOR TABLESPACES ON <database name>

2. LIST TABLESPACES

Containers

A table space is a logical database entity; table space containers are the physical storage associated with a table space. A container definition depends on the type of table space being created and can be defined as an operating system directory, a logical device/drive name, or a file.

When a table space is created, it must have at least one container associated with it. A single table space can contain multiple containers, but a container can belong to only one table space.

Details about a table space's containers can be obtained using the LIST TABLESPACE CONTAINERS FOR x command, where x is the table space's ID. A table space's ID can be found using the LIST TABLESPACES command and searching for the table space of interest.

Extents

The basic unit of storage in a DB2 database is the page, and pages can be different sizes. When pages are written to disk, they are grouped into contiguous ranges called extents. The extent size for a table space is specified for the table space when it is created and cannot be changed.

The DFT_EXTENT_SZ database configuration parameter specifies the default extent size for all table spaces in the database. This value can be overridden when the table space is created, using the EXTENTSIZE parameter of the CREATE TABLESPACE statement.

When a table space is created with more than one container, DB2 will write the data to the containers in a round-robin fashion. DB2 will fill an extent in one container, then fill an extent in the next container, and so on, until it has written an extent in all of the containers in the table space. DB2 will then fill the second extent in the first container, and so on.

However, this may change if containers are added or removed using the ALTER TABLESPACE command.

In Figure 1.5, the first extent (Extent 0) is written to Container 0, Extent 1 is written to Container 1, Extent 2 is written to Container 2. At this point, there is one extent in each of the containers, so DB2 will go back to the first container and add the next extent. Therefore, Extent 3 is written to Container 0, Extent 4 is written to Container 1, and so on, as more data is added.

Figure 1.5. Extents written to containers.

Creating Table Spaces

Creating a table space within a database assigns the specified containers to the table space and records the table space and container definitions and attributes in the database. When creating a table space, it is important to consider the following:

• What type of data the table space will hold

  • Regular data?Data or Index

  • Large/Long data

  • Temporary data

• What management type will be used

  • System Managed Space (SMS)

  • Database Managed Space (DMS)

• If using DMS, the type of container that will be used

  • File

  • Device

• What a good overhead and transfer rate would be, based on the underlying physical disks and/or disk subsystem being used for the container(s)

• The extent size and prefetch size that will best suit the workload and the underlying disks

• The page size that will be used for objects created within the table space

• The database partition group in which the table space will be placed

• The buffer pool(s) that will be used to manipulate data for the table space

The Table Space Types

There are three types of table spaces in DB2: regular, large, and temporary.

Regular Table Spaces

User-created tables are stored within regular table spaces. By default, every DB2 database has at least one regular table space called USERSPACE1. This table space is created when the CREATE DATABASE command is executed. In previous versions of DB2, indexes had to be stored in regular table spaces; however, in DB2 Version 8, indexes can be created in large table spaces, as well as in regular table spaces.

The system catalog tables are created and stored in a special regular table space called SYSCATSPACE. By default, the USERSPACE1 and SYSCATSPACE table spaces are created as SMS, but they can be created as DMS when the database is created.

The USERSPACE1 table space may be dropped once another user table space has been created; however, the SYSCATSPACE table space cannot be dropped because it holds the system catalog tables for the database.

The maximum size of a regular table space depends on the page size used for the table space. For a table space with 4-KB pages, the maximum size is 64 GB. For a table space with 32-KB pages, the maximum size is 512 GB.

The catalog table space

The DB2 system catalogs store all of the metadata about all of the DB2 objects in the database. The catalog table space is known as SYSCATSPACE and is created when the database is created. The system catalog table space is a regular table space, but it is used only to store the database catalog tables.

The system catalog table space contains many tables of varying sizes. When using a DMS table space, a minimum of two extents will be allocated for each table object. Depending on the table space's extent size, a significant amount of allocated and unused space may result. If using a DMS table space for the system catalogs, use a small extent size (2?4 pages) to minimize the amount of wasted space.

The catalog tables contain large object data type columns. These columns must be read from disk each time they are needed and do not use the buffer pool. By using an SMS table space or a DMS table space with file containers, DB2 can take advantage of the file system cache to handle the read requests for this data more quickly.

Large Table Spaces

If a table contains indexes, LongVarchar or Large Object (LOB) columns, the table may be split between table spaces. This must be done when the table is created and cannot be changed afterward. All columns not defined as LongVarchar or LOB must be stored in the regular table space; however, any indexes or LongVarchar or LOB columns can be stored in the regular table space or in a large table space, depending on how the table was created. To store indexes or LongVarchar and LOB objects in a separate table space from the other regular data columns, the LONG IN and/or INDEX IN options must be specified on the CREATE TABLE statement.

The maximum size of a large table space is 2 terabytes (TB).

Temporary Table Spaces

Temporary table spaces can be either system temporary table spaces or user temporary table spaces. System temporary table spaces are used by DB2 during SQL operations for holding transient data, such as intermediate tables, during sort operations, reorganizing tables, creating indexes, and joining tables. A database must have at least one system temporary table space. By default, an SMS table space called TEMPSPACE1 is created when the database is created. It can be dropped after another temporary table space with the same page size has been created.

In the event that a temporary table is created, DB2 will create the temporary table in the system temporary table space with the largest buffer pool that has a page size large enough to hold the temporary table. The largest buffer pool is based on total size, not on the number of pages. For example: a 1,000-page, 4-KB buffer pool is smaller than a 1,000-page, 8-KB buffer pool. If there are multiple system temporary table spaces with the same page size and the same size buffer pool, DB2 will then choose the system temporary table space for the temporary table in a round-robin manner.

If applicatons or users will create declared global temporary tables while working with the database, a user temporary table space must exist in the database to hold the temporary table.

Temporary table spaces can be either SMS or DMS.

The maximum size of a temporary table space is 2 TB.

SMS Table Spaces

SMS table spaces use the file system manager provided by the operating system to allocate and manage the space where the tables are stored. Within an SMS table space, each container is an operating system directory, and the table objects are created as files within them. When creating an SMS table space, the user must specify the name of the directory for each of the containers. DB2 will create the tables within the directories used in the table space by using unique file names for each object.

If a table space is created with more than one container, DB2 will balance the amount of data written to the containers. Because containers cannot be dynamically added to an SMS table space once it has been created, it is important to know the size requirements of the table space and to create all required containers when the table space is created.

An SMS table space can be created using the following command:

CREATE TABLESPACE <NAME>
MANAGED BY SYSTEM
USING ('<path1>', '<path2>', '<path3>')

When the path is specified for an SMS container, it can be either the absolute path or the relative path to the directory. If the directory does not exist, DB2 will create it. If the directory does exist, it cannot contain any files or subdirectories.

For example:

CREATE TABLESPACE ts1
MANAGED BY SYSTEM
USING ('D:\DIR1')

specifies the absolute path to the directory; therefore, DB2 would create the DIR1 directory on the D: drive on the database server if it does not already exist. The statement:

CREATE TABLESPACE ts2
MANAGED BY SYSTEM
USING ('DIR1')

specifies the relative path DIR1; therefore, DB2 would create the DIR1 directory under the database home directory. For this example, the table space will be created in the nonpartitioned database MYDB, which is the only database that was created in the default instance (DB2) on the D: drive on a Windows server. Therefore, the database will be created in the D:\DB2\NODE0000\SQL00001 directory. The three default table spaces will then use the following directories:

D:\DB2\NODE0000\SQL00001\SQLT0000.0         -         catalog table space
D:\DB2\NODE0000\SQL00001\SQLT0001.0         -         temp table space
D:\DB2\NODE0000\SQL00001\SQLT0002.0         -         userspace1

The table space TS2 would be created in the subdirectory DIR1 under the D:\DB2\NODE0000\SQL00001 directory, as shown below:

D:\DB2\NODE0000\SQL00001\DIR1

The following SQL statements create an SMS table space with three containers on three separate drives/file systems. Note that the table space name is the same in the examples, showing the differences between the UNIX/Linux and Windows table space definitions:

CREATE TABLESPACE smstbspc
MANAGED BY SYSTEM
USING ('d:\tbspc1', 'e:\tbspc2', 'f:\ tbspc3')

CREATE TABLESPACE smstbspc
MANAGED BY SYSTEM
USING ('/dbase/container1', '/dbase/container2', '/dbase/container3')

When an SMS table space is created, DB2 will create a tag file (SQLTAG.NAM) inside each container to uniquely identify the container. This file is called the container tag, and it contains information that identifies the container number, the table space the container belongs to, and the database to which the table space belongs. This is used to ensure that no other table space attempts to use the same container, even if it is in a different database.

For the table space TS1 created using:

CREATE TABLESPACE ts1
MANAGED BY SYSTEM
USING ('/mydir1', '/mydir2')
EXTENTSIZE 4

DB2 would create two directories, /mydir1 and /mydir2, and within each directory, DB2 will create the tag file SQLTAG.NAM.

With SMS containers, DB2 stores each database object in its own file.

• The file names are assigned by DB2, based on the table ID for the table that is assigned when it is created.

• The file name for the data part of the table will then be SQL<objectid>.DAT.

• The file name for the block map for any MDC table will be SQL<objectid>.BKM.

• The file name for the indexes for the table will be SQL<objectid>.INX.

• The file name for any LongVarchar columns in the table will be SQL<objectid>.LF.

• The file name for any LOB columns in the table will be SQL<objectid>.LB.

  • For each LOB object, there is also a LOB allocation object needed.

  • This will be stored in the file named SQL<objectid>.LBA.

For example, for a table with an object ID of 14, the following files may be created:

SQL00014.DAT     -    Normal data records
SQL00014.INX     -    Indexes
SQL00014.LF      -    Long varchar column data
SQL00014.LB      -    LOB column data
SQL00014.LBA     -    LOB allocation information
SQL00014.BKM     -    Block information for MDC tables

When the following SQL statements are run to create the tables T1 and T2, two additional files will be created, one for each table. The first table, T1, will be assigned object ID 2 and, therefore, will use the file SQL00002.DAT. The table T2 will be assigned object ID 3 and, therefore, will use the file SQL00003.DAT.

create table T1 (c1 int) in TS1
create table T2 (c1 float) in TS1

As shown in Figure 1.6, the first extent of table TS1 is created in container 0 (mydir1), the second extent in container 1 (mydir2), the third extent will be back in container 0, and so on. The table T2 will have its first extent created in container 1, its second extent in container 0, and so on.

Figure 1.6. Tables in an SMS table space.

The initial empty file(s) will be created in one container only but will be written to subsequent containers as data is added to the table. The starting point for each table (i.e., the container where each table will have its first extent created) is determined using the following formula:

Start Container ID = Objectid  %  number of containers

For the example above, table T1 has an objectid of 2; therefore, it will start in container:

Start Container ID = 2  %  2 = 0

Table T1 has an objectid of 3; therefore, it will start in container:

Start Container ID = 3  %  2 = 1

Enabling Multipage File Allocation

In an SMS table space, by default the object files are extended one page at a time as the object grows. Inserting a large number of rows into a table can result in a great deal of I/O that can have a negative impact on the database performance. The db2empfa tool enables DB2 to allocate or extend the object file by a full extent at a time. This tool is enabled at the database level, and when it is run, DB2 will allocate empty pages to fill up the last extent in all SMS table space containers within the specified database.

DMS Table Spaces

When using DMS table spaces, the database manager controls the storage allocation within the table space. Within a DMS table space, a container can be either an operating system file or a raw logical volume or disk partition. With DMS table spaces, the space is preallocated when the table space is created. When creating a DMS table space, the user must specify the name of the file, logical volume or disk partition, as well as the size of the container(s).

Device Containers

In Linux and UNIX, a device container is mapped to an underlying logical volume. In Windows, a device container is mapped to an unformatted disk partition. The device that the container is created on cannot be used for any other purpose, i.e., it cannot contain any file systems and should not be formatted. When specifying the size of the container, make sure that all of the space on the device is used because no unused space will be available for any other use and will be unused. However, this space can be used at a later time if the table space containers are extended or resized. The size of the container can be specified in the number of pages, KB, MB, or GB.

For example:

create tablespace mytbspc managed by database using
  (device '/dev/rmydisk1' 20000)
create tablespace mytbspc managed by database using
  (device '\\.\G:' 20000)
create tablespace mytbspc managed by database using
  (device '/dev/rmydisk1' 200M)
create tablespace mytbspc managed by database using
  (device '\\.\G:' 200M)

File Containers

A file container is an operating system file of a given size and is created when the table space is created. The name used to define a file container can be the absolute path or the relative path to the file. If the file does not exist, DB2 will create the file and initialize it to the specific size. If the file does exist, DB2 will check the file to verify that it is not already used for some other purpose. If the file is not the size specified for the container, it will be either expanded or shrunk to match the specified container size. The size of the file can be specified in the number of pages, KB, MB, or GB.

For example:

create tablespace mytbspc managed by database using
  (file '/myfile1' 20000)
create tablespace mytbspc managed by database using
  (file 'G:\myfile1' 20000)
create tablespace mytbspc managed by database using
  (file '/myfile1' 200M)
create tablespace mytbspc managed by database using
  (file 'G:\myfile1' 200M)

Raw Logical Volumes vs. File Systems

Within the database community, there has been a long running debate surrounding the use of raw logical volumes (raw devices) versus file systems. Advocates of raw logical volumes stress the performance gains that can be realized through their use, whereas file system supporters emphasize the ease of use and manageability features of file systems. As with the other aspects of system design, a decision must be made as to which is more important: performance or manageability.

To understand the performance advantages associated with raw logical volumes better, it is helpful to have an understanding of the impact of the file system cache. Most UNIX file systems set aside an area of memory (the cache) to hold recently accessed files, which can subsequently allow physical I/O requests to be satisfied from memory instead of from disk. If DB2 requests data that is not already in the cache, the operating system will read the data from disk into the cache, then copy the data to the DB2 buffer pool so that it can be used. Therefore, each read request translates into a disk read, followed by a copy of the data from the cache to the database buffer pool.

When the data is read from the cache, I/O requests can be satisfied in nanoseconds instead of the milliseconds that would be required to retrieve the data from disk. In addition, most UNIX file systems employ the use of a sequential read-ahead mechanism to prefetch data into the cache when it detects that a file is being accessed sequentially.

In non-database environments, the UNIX file system cache can significantly reduce I/O wait time for heavily accessed files. However, the performance benefits of file system caching in database environments are not so clear. This is due to the fact that most RDBMS systems, including DB2, also allocate a region of memory for caching frequently accessed data (i.e., the database buffer pools). This results in double buffering of the data in the file system cache and the DB2 buffer pool.

In 64-bit systems, the memory used by the file system cache could be better utilized by the database buffer pools. In 32-bit systems, with their associated shared memory limits, the file system cache can provide benefit for some workloads.

The primary benefit of raw logical volumes is that they bypass the file system cache by directly accessing the underlying logical device. The extra memory saved by eliminating the file system cache can then be allocated to the database buffer pools. In addition, overall CPU utilization is decreased, due to the fact that the system no longer has to copy the data from the file system cache to the database buffer pools.

The Container Tag

When a DMS table space is created, the file or raw device containers will be preallocated by DB2. Within the first extent of each container, DB2 will create the container tag to identify the container.

In previous versions of DB2, the container tag for DMS containers was stored in a single page at the beginning of the container to minimize the space requirements. Large Storage Area Networks (SANs) and disk arrays using Redundant Array of Independent Disks (RAID) technology have become more popular, and many databases are being created on RAID protected disks. When using a one-page container tag, the beginning and end of an extent could not be made to line up with the beginning and end of a stripe on the underlying disks, and this could cause suboptimal I/O because each I/O would need to access more than one disk. If the extent size is set to be equal to or an integer multiple of the RAID strip size, by making the container tag a full extent in size, the I/Os would always line up with the underlying disk stripes. In DB2 Version 7, the container tag could be made a full extent using the DB2_STRIPED_CONTAINERS registry variable. Now, in DB2 Version 8, the container tag is created as a full extent in size by default for DMS table spaces.

To force DB2 to create the tag on a single page, the new registry variable, DB2_USE_PAGE_CONTAINER_TAG, should be set to ON.

Overhead in DMS Table Spaces

There are some extents created within each DMS table space that are used by DB2 to control the allocation of the extents within the table space. The first extent in each container will be the container tag. There are three extents of overhead that will be used in addition to the container tag. In addition to these four extents required (the minimum if there is only one container), DB2 requires that there be a minimum of two additional extents so that at least one object can be created in the table space.

The overhead for a table space is summarized in Figure 1.7.

Figure 1.7. Overhead extents in a DMS table space.

For the table space TS1 created using:

create tablespace TS1
  managed by database using
  (file '/myfile' 10 M, device '/dev/rhd7' 20 M)
  extentsize 4

DB2 would create the file /myfile with a size of 10 MB and would also use 20 MB on the raw logical volume rhd7. The table space would logically look like Figure 1.8.

Figure 1.8. Logical view of the table space.

When the following SQL statements are run to create the tables T1 and T2, the first table, T1, will be assigned object ID 4, and the table T2 will be assigned object ID 5.

db2 create table T1 (c1 int ...) in TS1
db2 create table T2 (c1 float ...) in TS1

In a DMS table space, each object also needs an extent of overhead to be used to map the allocation of the object within the table space. Therefore, the logical view of the table space after the two tables have been created would look like Figure 1.9.

Figure 1.9. Logical view of the table space with tables T1 and T2.

Some important points to remember with regard to DMS table spaces are:

• An empty table space will always have at least four extents that cannot be used by data but are needed for the table space (because of the container tag, table space header, object table, and the SMP extent).

  NOTE

  The used pages shown in the table space snapshot do not count the pages used for the container tag.

  
  

• The minimum number of extents needed to hold a single table with no indexes or LOB/Long Varchar columns is two (for the data object and the extent map for the data object).

• The smallest size allowed for a table space is six extents. Attempting to create a table space with a smaller number of extents available will result in a table space full error being returned.

Should the Temporary Table Space Be SMS or DMS?

Due to the extra overhead associated with creating an object in a DMS table space, the temporary table space should be SMS in most cases. This is especially true for Online Transaction Processing (OLTP) type workloads with very small, if any, sorts.

For a system that creates very large temporary tables that are flushed to disk and prefetched back into the buffer pool, a DMS temporary table space may provide better performance.

Copyright eTutorials.org 2008-2024. All rights reserved.