The following database objects let you define how data will be stored on your system, and how performance (related to accessing the data) can be improved:
A database is organized into parts called table spaces. A table space is a place to store tables. When creating a table, you can decide to have certain objects such as indexes and large object (LOB) data kept separately from the rest of the table data. A table space can also be spread over one or more physical storage devices. The following diagram shows some of the flexibility you have in spreading data over table spaces:
|
Table spaces reside in nodegroups (see Nodegroups). Table space definitions and attributes are recorded in the database system catalog (see System Catalog Tables).
Containers are assigned to table spaces. A container is an allocation of physical storage (such as a file or a device).
A table space can be either system managed space (SMS), or database managed space (DMS). For an SMS table space, each container is a directory in the file space of the operating system, and the operating system's file manager controls the storage space. For a DMS table space, each container is either a fixed size pre-allocated file, or a physical device such as a disk, and the database manager controls the storage space.
Figure 6 illustrates the relationship between tables, table spaces, and the two types of space. It also shows that tables, indexes, and long data are stored in table spaces.
Figure 6. Table Spaces and Tables
 |
Figure 7 shows the three table space types: regular, temporary, and long.
Tables containing user data exist in regular table spaces. The default user table space is called USERSPACE1. Indexes are also stored in regular table spaces. The system catalog tables exist in a regular table space. The default system catalog table space is called SYSCATSPACE.
Tables containing long field data or long object data, such as multi-media objects, exist in long table spaces.
Temporary table spaces are classified as either system or user. System temporary table spaces are used to store internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and joining tables. Although you can create any number of system temporary table spaces, it is recommended that you create only one, using the page size that the majority of your tables use. The default system temporary table space is called TEMPSPACE1. User temporary table spaces are used to store declared global temporary tables that store application temporary data. User temporary table spaces are not created by default at database creation time.
Figure 7. Three Table Space Types
 |
A container is a physical storage device. It can be identified by a directory name, a device name, or a file name.
A container is assigned to a table space. A single table space can span many containers, but each container can belong to only one table space.
Figure 8 illustrates the relationship between tables and a table space within a database, and the associated containers and disks.
Figure 8. Table Spaces and Tables Within a Database
 |
The EMPLOYEE, DEPARTMENT, and PROJECT tables are in the HUMANRES table space which spans containers 0, 1, 2, 3, and 4. This example shows each container existing on a separate disk.
Data for any table will be stored on all containers in a table space in a round-robin fashion. This balances the data across the containers that belong to a given table space. The number of pages that the database manager writes to one container before using a different one is called the extent size.
A buffer pool is the amount of main memory allocated to cache table and index data pages as they are being read from disk, or being modified. The purpose of the buffer pool is to improve system performance. Data can be accessed much faster from memory than from disk; therefore, the fewer times the database manager needs to read from or write to a disk (I/O), the better the performance. (You can create more than one buffer pool, although for most situations only one is required.)
The configuration of the buffer pool is the single most important tuning area, because you can reduce the delay caused by slow I/O.
Figure 9 illustrates the relationship between a buffer pool and containers.
Figure 9. Buffer Pool and Containers
 |