Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Data manipulation in DB2 UDB

This book chapter focuses on the creation, management, and monitoring of buffer pools in DB2 UDB Version 8 to provide optimal performance for the database.

This is an excerpt from Dwaine Snow and Tom Phan's "Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows." You can purchase the book here.

All regular table data and indexes for a DB2 database are manipulated within the database's buffer pool area. A database must have at least one buffer pool associated with it but can use multiple buffer pools. The buffer pools are the work area for the database, and all searching for and manipulation of the data and indexes must take place within the buffer pools. In order for DB2 to scan a table or an index, the pages of the table or index must be in the database's buffer pool (or buffer pools). If the required page is already in the buffer pool, DB2 can start to work on the page immediately. If the page is not in the buffer pool, DB2 must read the page from disk and position it in the buffer pool(s) before it can be manipulated. Because most data manipulation takes place in the database buffer pools, configuring the buffer pools is the single most important tuning area for DB2 Universal Database (UDB) databases.

The database buffer pools have a profound effect on the overall performance of the database and, as such, there have been a number of enhancements to the manner in which buffer pools can be created and managed in DB2 UDB Version 8. This chapter will focus on the creation, management, and monitoring of buffer pools in DB2 UDB Version 8 to provide optimal performance for the database.

The database buffer pool area is a piece of memory used to cache a table's index and data pages as they are being read from disk to be scanned or modified. The buffer pool area helps to improve database system performance by allowing data to be accessed from memory instead of from disk. Because memory access is much faster than disk access, the less often that DB2 needs to read from or write to a disk, the better the system will perform.

When a database is created, there will be one default buffer pool created for the database. This buffer pool is named IBMDEFAULTBP; it has a page size of 4 KB; and it will be sized depending on the operating system. For Windows, the default buffer pool will be 250 pages or 1 MB, whereas for UNIX, the default buffer pool will be 1,000 pages or 4 MB. The default buffer pool cannot be dropped; however, the size of the default buffer pool can be changed, using the ALTER BUFFERPOOL statement.

A database's buffer pool(s) are allocated in memory when the database is activated or when the first application connects to the database. When all applications disconnect from the database and it is deactivated, the buffer pool memory is deallocated and freed back to the operating system. With DB2 UDB Version 8, buffer pools can now be created, dropped, and resized while the database is active.

Read the rest of the chapter here.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.