Db2 简明教程

DB2 - Bufferpools

本章为您介绍了数据库中的缓冲池。

This chapter introduces you to Bufferpools in the database.

bufferpool

Introduction

缓冲池是指数据库管理器分配的主存储空间部分。缓冲池的目的是从磁盘缓存表和索引数据。所有数据库都有自己的缓冲池。在新数据库创建时，会创建一个默认缓冲池。它被称为“IBMDEFAULTBP”。根据用户要求，可以创建多个缓冲池。在缓冲池中，数据库管理器将表行数据作为一个页面放置。此页面会一直驻留在缓冲池中，直到关闭数据库或用新数据写入该空间。缓冲池中已更新数据但未写入磁盘的页面称为“脏”页面。在缓冲池中更新的数据页面写入磁盘后，缓冲池就准备好接收其他数据。

The bufferpool is portion of a main memory space which is allocated by the database manager. The purpose of bufferpools is to cache table and index data from disk. All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. It called as “IBMDEFAULTBP”. Depending on the user requirements, it is possible to create a number of bufferpools. In the bufferpool, the database manager places the table row data as a page. This page stays in the bufferpool until the database is shutdown or until the space is written with new data. The pages in the bufferpool, which are updated with data but are not written onto the disk, are called “Dirty” pages. After the updated data pages in the bufferpool are written on the disk, the bufferpool is ready to take another data.

Relationship between tablespaces and bufferpools

每个表空间都与数据库中的特定缓冲池相关联。一个表空间与一个缓冲池相关联。缓冲池和表空间的大小必须相同。多个缓冲池允许您配置数据库使用的内存，以提高其整体性能。

Each table space is associated with a specific buffer pool in a database. One tablespace is associated with one bufferpool. The size of bufferpool and tablespace must be same. Multiple bufferpools allow you to configure the memory used by the database to increase its overall performance.

Bufferpool sizes

在使用“CREATE DATABASE”命令时设置缓冲池页面的大小。如果不指定页面大小，它将采用默认页面大小，即 4KB。创建缓冲池后，以后无法修改页面大小

The size of the bufferpool page is set when you use the “CREATE DATABASE” command. If you do not specify the page size, it will take default page size, which is 4KB. Once the bufferpool is created, it is not possible to modify the page size later

Listing the available bufferpools in the current database directory

Syntax : [下面的语法显示数据库中所有可用的缓冲池]

Syntax: [The syntax below shows all available bufferpools in database]

db2 select * from syscat.bufferpools

Example : [要查看当前数据库中的可用缓冲池]

Example: [To see available bufferpools in current database]

db2 select * from syscat.bufferpools

Output:

Output:

BPNAME      BUFFERPOOLID DBPGNAME   NPAGES      PAGESIZE    ESTORE
NUMBLOCKPAGES BLOCKSIZE   NGNAME
------------------------------------------------------------
IBMDEFAULTBP
 1 -
 -2        4096 N                  0           0 -

 1 record(s) selected.

Creating the bufferpool

要为数据库服务器创建新的缓冲池，您需要两个参数，即“缓冲池名称”和“页面大小”。执行以下查询以创建新的缓冲池。

To create a new bufferpool for database server, you need two parameters namely, “bufferpool name” and “size of page”. The following query is executed to create a new bufferpool.

语法：[在下面的语法中，“bp_name”表示缓冲池名称，“size”表示您需要为缓冲池声明的页面大小 (4K、8K、16K、32K)]

Syntax: [In the syntax below,‘bp_name’ indicates bufferpool name and ‘size’ indicates size for page you need to declare for bufferpools (4K,8K,16K,32K)]

db2 create bufferpool <bp_name> pagesize <size>

Example : [要创建一个名称为“bpnew”、大小为“8192”(8Kb) 的新缓冲池。]

Example: [To create a new bufferpool with name “bpnew” and size “8192”(8Kb).]

db2 create bufferpool bpnew pagesize 8192

Output

DB20000I The SQL command completed successfully.

Dropping the bufferpool

在删除缓冲池之前，需要检查是否有表空间已分配给它。

Before dropping the bufferpool, it is required to check if any tablespace is assigned to it.

Syntax : [要删除缓冲池]

Syntax: [To drop the bufferpool]

drop bufferpool <bp_name>

Example : [要删除名为“bpnew”的缓冲池]

Example: [To drop ‘bpnew’ named bufferpool]

db2 drop bufferpool bpnew

Output

DB20000I The SQL command completed successfully.