Within the discussion of storage architecture, we will consider:
When you create a database, information about the database including default information is placed within a directory. The directory structure is created for you at a location that is based on the information you provide in the CREATE DATABASE command. If you do not specify the location of the path or drive when creating the database, the default location is used.
It is recommended that you explicitly state where you would like the database created.
At the directory you specify in the CREATE DATABASE command, a subdirectory using the name of the instance is created. This subdirectory ensures that databases created in different instances under the same directory do not use the same path. Following the instance name subdirectory, a subdirectory using "NODE0000" is created. This subdirectory is used to differentiate partitions in a multiple logical partitioned database environment. Following the node directory, a subdirectory using "SQL00001" is created. This subdirectory is named using the database token and represents the database being created. It is also used to differentiate databases created in this instance on the directory you specified in the CREATE DATABASE command.
The directory structure would appear like the following:
<your_directory>/<your_instance>/NODE0000/SQL00001/
The database directory will contain several files that were created as part of the CREATE DATABASE command. Buffer pool information is contained in the files: SQLBP.1 and SQLBP.2. Table space information is contained in the files: SQLSPCS.1 and SQLSPCS.2. There are two of each of these files to allow for backing up the information in these files.
Database configuration information is contained in: SQLDBCON. The history file db2rhist.asc and its backup db2rhist.bak are readable by you and contain history information about backups, restores, loading of tables, reorganization of tables, altering of a table space, and other changes to a database.
The log control file, SQLOGCTL.LFH, contains information about the active logs. Recovery processing uses information from this file to determine how far back in the logs to begin recovery. The SQLOGDIR subdirectory contains the actual log files.
| Note: | You should ensure the log subdirectory is mapped to different disks than those used for your data. A disk problem could then be restricted to your data or the logs but not both. As well, this can provide a substantial peformance benefit, as the log files and database containers are not competing for movement of the same disk heads. You can change the location of the log subdirectory using the newlogpath database configuration parameter. |
The SQLT* subdirectories are created and contain the default System Managed Space (SMS) table spaces required for an operational database. There are three default table spaces created:
You will also read of "containers" when considering table spaces. For SMS table spaces, containers are operating system directories.
Each subdirectory or container has a file created in it called "SQLTAG.NAM". This file marks the subdirectory as being in use so that subsequent table space creation will not attempt to use these subdirectories. There are also other files that are created under the container subdirectories with different name extensions to distinguish between the type of data stored in the files. The extensions are:
There are two types of table spaces supported: System Managed Space (SMS) and Database Managed Space (DMS). Each has its own characteristics that make it appropriate for different environments. See Designing and Choosing Table Spaces for more information.
System Managed Space (SMS) table spaces store data in operating system files. The data in the table spaces is striped by extent across all the containers in the system. An extent is a group of consecutive pages defined to the database. Each table in a table space is given its own file name which is used in all containers. The file extension denotes the type of the data stored in the file. The starting extent for each table is placed in "round robin" fashion throughout the containers. This spreads the space requirement evenly across all containers in the table space. This is very important when there are a large number of small tables.
Allocation of space is done when there is a demand for additional space. By default, space is allocated one page at a time.
With Database Managed Space (DMS) table spaces, the database manager controls the storage space. A list of devices or files is selected to belong to a table space when the DMS table space is defined. The space on those devices or files is managed by the DB2 database manager. As with SMS table spaces and containers, DMS table spaces and the database manager use striping by extent to ensure an even distribution of data across all containers.
DMS table spaces differ from SMS table spaces in that for DMS table spaces, space is allocated when the table space is created and not allocated when needed.
Also, placement of data can differ on the two types of table spaces. For example, consider the need for efficient table scans: It is important that the pages in an extent are physically contiguous. With SMS, the file system of the operating system decides where each logical file page is physically placed. The pages may, or may not, be allocated contiguously depending on the level of other activity on the file system and the algorithm used to determine placement. With DMS, however, the database manager can ensure the pages are physically contiguous because it interfaces with the disk directly.
There is one exception to this general statement regarding contiguous placement of pages in storage. There are two container options when working with DMS table spaces: Raw devices and files. 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.
Unlike SMS table spaces, the containers that make up a DMS table space do not need to be close to being equal in their capacity. However, it is recommended that the containers are equal, or close to being equal, in their capacity. Also, if any container is full, any available free space from other containers can be used in a DMS table space.
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 next figure shows the logical address map for a DMS table space.
|
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. One of the things defined when creating the table space is the extent size. An extent is the unit of space allocation within a table space. It is simply a set of contiguous pages. The extent size is the number of contiguous pages. Only one table (or other object, such as an index) can use the pages in any single extent. All objects (tables, indexes, and others) created in the table space are allocated extents in a logical table space address map. An extent belongs to only one object at a time. Extent allocation is managed through Space Map Pages (SMP).
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 simply 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 or not.
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.
The object table is an internal relational table that maps an object identifier to the location of the table's first EMP extent. This EMP extent, directly or indirectly, maps out all extents in the object. Each EMP contains an array of entries. Each entry maps an object-relative extent number to a table space-relative page number where the object extent is located. Direct EMP entries directly map object-relative addresses to table space-relative addresses. The last EMP page in the first EMP extent contains indirect entries. Indirect EMP entries map to EMP pages which then map to object pages. The last 16 entries in the last EMP page in the first EMP extent contain double-indirect entries.
The extents from the logical table space address map are striped in a round robin fashion across the containers associated with the table space.
When comparing SMS and DMS table spaces, SMS table spaces are an excellent choice for general purposes. SMS table spaces provide very good performance with very little administration cost. DMS table spaces are the best choice when seeking top performance. Device containers provide the best performance since double buffering can occur when moving data using file containers or SMS table spaces. (Double buffering can occur when the data is buffered first at the database manager level and then again at the file system level.)