Section 8.1. The DB2 Storage Model: The Big Picture

8.1. The DB2 Storage Model: The Big Picture

This section provides an overview of the DB2 storage model. In Figure 8.1 you can see the interaction between the different database objects as they relate to the DB2 storage model. This figure presents a combination of physical and logical views of the primary database objects.

Figure 8.1. A view of the DB2 database objects

Figure 8.1 illustrates a user retrieving some data from the table t2. From this user's perspective, the information he needs is stored in a table, and how and where it is stored on disk is irrelevant. When this user issues the SQL statement:

 SELECT ProdId FROM t2 WHERE ProdName = 'Plum' 

the column from the specified rows in the table is retrieved by DB2 and returned to the user. Behind the scenes, DB2 may need to read a number of pages of data from one or more physical disks or from memory to provide this information to the user.

DB2 stores table and index data on pages, which are the smallest unit of storage in a DB2 database. Figure 8.1 shows four pages for the table t2, and each page contains one or more rows from the tables t1 and t2 and one or more key values for the index ix1 on table t1.

When DB2 processes SQL statements, it must scan the data and/or index pages. DB2 only scans these pages in the buffer pools. If the page is already in a buffer pool due to the processing of other SQL statements, DB2 can process the request immediately. If the page is not in the buffer pool, DB2 must read it from disk and place it in the buffer pool, as shown on the right side of Figure 8.1.

Rather than reading one page at a time, DB2 can group pages together into extents and read multiple pages with each I/O request. In Figure 8.1 you can see that pages are grouped together in extents of two pages. You can also see that the tables and indexes are stored in table spaces. Table spaces are a logical layer only and do not physically exist. Containers are the physical storage for the table spaces.

To optimize performance, DB2 lets you specify the table spaces in which you want your tables and indexes stored. You can also associate buffer pools with specific table spaces so that you can optimize the placement of tables and indexes within memory.