In addition to the Control Center objects already described, the following database objects are important because they let you define how you will store the data on your system and how you can improve performance related to accessing the data:
You are not required to create a table space, container, or buffer pool to be able to add data to tables in a database. You can accept the defaults for table space, container, and buffer pool when you create a database and a table. However, if you are interested in tuning one of them for your environment, you may find their descriptions in this section useful.
A database is organized into parts called table spaces. Essentially, a table space is a place to store tables. A table space can be either a system managed space (SMS), or a 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. Containers are described in the next section. 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 11 illustrates the relationship of tables to table spaces, and the two types of space: SMS and DMS. It also illustrates that tables, indexes, and long data are stored in table spaces.
Figure 11. Table Spaces and Tables
|
Figure 12 shows the three table space types: regular, temporary, and long.
Tables containing user data exist in the regular table space(s). By default, a table space called USERSPACE1 is created. Indexes are also stored in regular table space(s). The system catalog tables exist in regular table space(s) as well. 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 the long table space(s).
The temporary table space is used during SQL operations for things like sorting or reorganizing tables, creating indexes, and joining tables. A database should have one temporary table space. (You can create more, although for most situations only one is required.) By default, a table space called TEMPSPACE1 is created.
Figure 12. 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. All database and table data is assigned to table spaces. A table space's definitions and attributes are recorded in the database system catalog. Once a table space is created, you can then create tables within this table space.
A container is not explicitly shown in the Control Center but it is listed when you view table spaces.
A single table space can span many containers, but each container can belong to only one table space.
Figure 13 shows an example of the relationship between tables and a table space within a database, and the associated containers and disks.
Figure 13. 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 an allocation 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 database system performance. Data can be accessed much faster from memory than from a disk; therefore, the fewer times the database manager needs to read from or write to a disk, 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, since you can reduce the delay caused by slow physical I/O.
Figure 14 illustrates the relationship of a buffer pool and containers to a system, a database, and a table space.
Figure 14. Buffer Pool and Containers
 |