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 7 illustrates the relationship between tables and a table space within a database, and the associated containers and disks Database HUMANRES Table Space EMPLOYEE DEPARTMENT PROJECT Table Table D: \DBASE1 F\DBASE1 H: \DBASE1 Container 3 E: DBASE Container 2 Figure 7. 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 Buffer pools 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 pter 1. Basic relational database concepts 11
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 7 illustrates the relationship between tables and a table space within a database, and the associated containers and disks. 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. Buffer pools: 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 Figure 7. Table Spaces and Tables Within a Database Chapter 1. Basic relational database concepts 11
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. because you can reduce the delay caused by slow v0 mportant tuning The configuration of the buffer pool is the single most Figure 8 illustrates the relationship between a buffer pool and containers Database Object/Concept Equivalent Physical Obje System Instance(s) ifer Poc Reserved(A chunk of reserved memory Database(s) Table File Container(s) Device Figure 8. Buffer Pool and Containers Related concepts Indexes"in the SQL Reference, Volume 1 Tables"in the SQL Reference, Volume 1 Relational databases"in the SQL Reference, Volume 1 Schemas"in the SQL Reference, Volume 1 Views"in the SQL Reference, Volume 1 2 Adr tion Guide: P
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 8 illustrates the relationship between a buffer pool and containers. Related concepts: v “Indexes” in the SQL Reference, Volume 1 v “Tables” in the SQL Reference, Volume 1 v “Relational databases” in the SQL Reference, Volume 1 v “Schemas” in the SQL Reference, Volume 1 v “Views” in the SQL Reference, Volume 1 Reserved System Instance(s) Database(s) Database Object/Concept Equivalent Physical Object Buffer Pool (A chunk of reserved memory created in a database and assigned to one or more table spaces) Directory Container(s) Device File Table space(s) Figure 8. Buffer Pool and Containers 12 Administration Guide: Planning
Table spaces and other storage structures"in the SQL Reference, Volume 1 Configuration parameters When a DB2 instance or a database is created, a corresponding configuration file is created with default parameter values. You can modify these parameter values to improve performance Configuration files contain parameters that define values such as the resources allocated to the DB2 products and to individual databases, and the diagnostic level. There are two types of configuration files the database manager configuration file for each DB2 instance the database configuration file for each individual database The database manager configuration file is created when a DB2 instance reated. The parameters it contains affect system resources at the instance level, independent of any one database that is part of that instance. Values for many of these parameters can be changed from the system default values to improve performance or increase capacity, depending on your systems onfiguration There is one database manager configuration file for each client installation as well. This file contains information about the client enabler for a specific workstation. A subset of the parameters available for a server are applicable to the client Database manager configuration parameters are stored in a file named db2systm. This file is created when the instance of the database manager is created In UNIX-based environments, this file can be found in the sqllib subdirectory for the instance of the database manager. In Windows, the default location of this file is the instance subdirectory of the sql lib directory If the DB2INSTPROF variable is set, the file is in the instance subdirectory of the directory specified by the DB2INSTPROF variable In a partitioned database environment, this file resides on a shared file system so that all database partition servers have access to the same file. The onfiguration of the database manager is the same on all database partition servers Most of the parameters either affect the amount of system resources that will be allocated to a single instance of the database manager, or they configure the setup of the database manager and the different communications subsystems based on environmental considerations. In addition, there are other parameters that serve informative purposes only and cannot be pter 1. Basic relational database concepts 13
v “Table spaces and other storage structures” in the SQL Reference, Volume 1 Configuration parameters When a DB2® instance or a database is created, a corresponding configuration file is created with default parameter values. You can modify these parameter values to improve performance. Configuration files contain parameters that define values such as the resources allocated to the DB2 products and to individual databases, and the diagnostic level. There are two types of configuration files: v the database manager configuration file for each DB2 instance v the database configuration file for each individual database. The database manager configuration file is created when a DB2 instance is created. The parameters it contains affect system resources at the instance level, independent of any one database that is part of that instance. Values for many of these parameters can be changed from the system default values to improve performance or increase capacity, depending on your system’s configuration. There is one database manager configuration file for each client installation as well. This file contains information about the client enabler for a specific workstation. A subset of the parameters available for a server are applicable to the client. Database manager configuration parameters are stored in a file named db2systm. This file is created when the instance of the database manager is created. In UNIX-based environments, this file can be found in the sqllib subdirectory for the instance of the database manager. In Windows, the default location of this file is the instance subdirectory of the sqllib directory. If the DB2INSTPROF variable is set, the file is in the instance subdirectory of the directory specified by the DB2INSTPROF variable. In a partitioned database environment, this file resides on a shared file system so that all database partition servers have access to the same file. The configuration of the database manager is the same on all database partition servers. Most of the parameters either affect the amount of system resources that will be allocated to a single instance of the database manager, or they configure the setup of the database manager and the different communications subsystems based on environmental considerations. In addition, there are other parameters that serve informative purposes only and cannot be Chapter 1. Basic relational database concepts 13
changed. All of these parameters have global applicability independent of any single database under that instance of the database a database configuration file is created when a database is created, and resides where that database resides. There is one configuration file per database. Its parameters specify, among other things, the amount of resource to b allocated to that database. Values for many of the parameters can be changed to improve performance or increase capacity. Different changes may be required, depending on the type of activity in a specific database Parameters for an individual database are stored in a configuration file named SQLDBCON. This file is stored along with other control files for the database in the SQLnnnnn directory, where nnnnn is a number assigned when the database was created. Each database has its own configuration file, and most of the parameters in the file specify the amount of resources allocated to that database. The file also contains descriptive information, as well as flags that indicate the status of the database In a partitioned database environment, a separate SQLDBCON file exists for each database partition. The values in the SQLDBCoN file may be the same or different at each database partition, but the recommendation is that the database configuration parameter values be the same on all partitions 14 Administration Guide: Planning
changed. All of these parameters have global applicability independent of any single database stored under that instance of the database manager. A database configuration file is created when a database is created, and resides where that database resides. There is one configuration file per database. Its parameters specify, among other things, the amount of resource to be allocated to that database. Values for many of the parameters can be changed to improve performance or increase capacity. Different changes may be required, depending on the type of activity in a specific database. Parameters for an individual database are stored in a configuration file named SQLDBCON. This file is stored along with other control files for the database in the SQLnnnnn directory, where nnnnn is a number assigned when the database was created. Each database has its own configuration file, and most of the parameters in the file specify the amount of resources allocated to that database. The file also contains descriptive information, as well as flags that indicate the status of the database. In a partitioned database environment, a separate SQLDBCON file exists for each database partition. The values in the SQLDBCON file may be the same or different at each database partition, but the recommendation is that the database configuration parameter values be the same on all partitions. 14 Administration Guide: Planning
Database Object/Concept Equivalent Physical Object System onfiguration Instance(s) Configuration Database(s) Parameters Figure 9. Configuration Parameter Files Related concepts Configuration parameter tuning" in the Administration Guide: Performance Related tasks: Configuring DB2 with configuration parameters" in the Administration Guide: Performance Business rules for data Within any business, data must often adhere to certain restrictions or rules For example, an employee number must be unique. DB2 Provides constraints as a way to enforce such rules DB2 Provides the following types of constraints NOT NULL cOnstraint Unique constraint Chapter 1. Basic relational database concepts 15
Related concepts: v “Configuration parameter tuning” in the Administration Guide: Performance Related tasks: v “Configuring DB2 with configuration parameters” in the Administration Guide: Performance Business rules for data Within any business, data must often adhere to certain restrictions or rules. For example, an employee number must be unique. DB2® provides constraints as a way to enforce such rules. DB2 provides the following types of constraints: v NOT NULL constraint v Unique constraint Database Object/Concept Equivalent Physical Object Database Manager Configuration Parameters Operating System Configuration File Database Configuration Parameters System Instance(s) Database(s) Figure 9. Configuration Parameter Files Chapter 1. Basic relational database concepts 15