Basic Performance adjustments for IBM DB2

The db2 tutorial is: Basic Performance adjustment of IBM DB2. Buffer Pool

If you have experience using DB2 UDB, you should know that the buffer pool is the space allocated separately to the DB2 database manager in the memory, let it add new data as a database or respond to a query buffer that returns some data pages from the disk. Because data access from the memory is much faster than reading from the disk, the overall performance of the database is improved by reducing the I/O operation buffer pool on the disk. In fact, studying how the buffer pool is created and used is the most important step in adjusting the optimized database performance.
Given the importance of the buffer zone, every DBA should understand how DB2 UDB uses them. Here is a quick review course about it. When new data is added to the database, it first adds a new page to the buffer zone. The page will be embodied in the database bucket. On the other hand, to respond to queries when data is retrieved from the database, the DB2 database manager first stores the pages containing data in the buffer pool, then it will be passed to the application or user who needs it. Each time you execute a new query, the page will be searched in each available buffer pool for required data to reside in the memory. If so, it will be immediately passed to the corresponding application or user. However, if you cannot find the required data in these buffer pools, the DB2 database manager will retrieve the data from the memory and copy it to the buffer before passing the data. Once the page is copied to the buffer pool, the page will remain in the buffer until the database is closed or until other pages need to be stored in its space. (Since all data loading and modification first occur in the buffer zone -- modified pages will eventually refresh the disk storage -- so the data stored in the buffer pool is always the latest) when the buffer pool is full, the DB2 database manager checks the last page reference time, page type, or page modification without affecting the disk content to select which pages to remove. These pages may be referenced again. For example, pages retrieved 30 minutes ago in response to queries are more likely to be overwritten than those that contain the update operation but do not implement the update.
DB2 UDB creates a buffer pool (IBMDEFAULTBP) by default as part of the database creation process. On Linux and Unix platforms, the buffer pool is allocated 1,000 4 kb pages from the memory. On Windows platforms, the buffer pool is allocated 250 4 kb pages from the memory. You can find the buffer pool menu in the control center and select an appropriate operation or execute the alter bufferpool statement to increase or decrease the number of 4 kb pages of the buffer pool. You can also CREATE another buffer by using the same method in the control center or executing the create bufferpool statement.
Due to the importance of the buffer pool, you should carefully consider how many buffer pools are used to meet your implementation needs, how much each buffer pool needs, and how each buffer pool can be fully utilized. In most environments, the number of buffers that can be effectively used depends on the size of the available system memory. If the available memory can retain 10,000 4 k pages (or less), it is better to use a separate large buffer pool than to use multiple small buffer pools. Using multiple small buffers will lead to frequent page access and frequent swap with memory, in turn, it will lead to competition for storage objects such as the I/O of the cataloguing table or repeated access to the user table and index. However, if you have a large amount of memory, you should consider the following:
· Each temporary tablespace is defined
· Contains tablespaces that are frequently accessed by some short-term update transactions.
· Tablespaces that contain tables and indexes that are frequently updated
· Tablespaces that contain tables and indexes that are frequently queried but rarely updated
· Contains tablespaces in which tables are frequently used for random queries
· Tablespace containing data that is rarely accessed by applications
· Table spaces that contain data and indexes you want to use.
In many cases, a larger buffer pool is better than a smaller one. However, considering the total amount of memory available and how the buffer pool will be used. If you have an application that performs many random access operations from a very large table, you should create and use a small buffer pool for this special table. In this case, there is no need to keep data pages in the buffer pool memory once they are used to execute a separate query. On the other hand, if you have an application that frequently retrieves data from several seemingly small tables, you should consider creating a large enough buffer pool to store all the data in these tables. With this design scheme, the data can be loaded into the memory at a time, and it can be obtained repeatedly without the need for additional disk I/O.

There are 3 pages in this news. Currently, there are 3 pages in 1st.