How many buffer pools are available in db2

Two types of sync reads, random and sequential, and three types of prefetch reads, dynamic list and sequential. And so in this methodology, it would be the initial candidate for allocating additional memory. Whether you are conducting product research, need support on a project, are experiencing downtime, or want to learn more about how IntelliMagic can support your business, our experts are here to help.

Analyze the performance impacts of implementing asynchronous duplexing for Db2 lock structure in a high volume Db2 data sharing environment. Explore Db2 Performance Management and Monitoring. To specify a range of partitions, use the TO clause between the partition numbers. When specifying a range of partitions, the second database partition number must be higher than the first database partition number, and the buffer pool must exist on all of the database partitions.

After the partitions or partitions have been specified, their size must also be specified. The block size must be between two and pages, and the default value is 32 pages. The default page size is 4 KB, or bytes. The page size can be specified in both bytes and KB. Extended storage is more efficient than retrieving data from disk but less efficient than retrieving data from the buffer pool, so it is not applicable to bit environments.

Once defined for a buffer pool, the page size and buffer pool name cannot be altered. If the applications do not perform sequential prefetching of pages into the buffer pool, this area of the buffer pool will be wasted. Because the page size is not specified, the buffer pool will use the default page size of 4 KB. Because the IMMEDIATE option is the default, the buffer pool will be allocated immediately and available for use, as long as there is enough memory available to fulfill the request.

The buffer pool will use an 8-KB page size. Because the IMMEDIATE option is specified, the buffer pool will be allocated immediately and available for use, as long as there is enough memory available to fulfill the request.

Because the block size is set to pages, it is recommended that the extent size for the table spaces assigned to this buffer pool also be set to pages. This may not always be possible, because several table spaces with different extent sizes may be bound to the same block-based buffer pool. The proportions of the page area and block area are not maintained. The block-based area of the buffer pool will be altered only if:. This gives a page-based area of 4, pages and a block-based area of 4, pages.

However, because the BUFFPAGE parameter is used, the buffer pool will be reduced in size, but the block-based area will have to be reduced accordingly. This is done to allow for a certain number of pages to exist in the page-based area for workloads that do not consist of sequential prefetching. Use this statement:. To create a buffer pool named BP5 with a size of 25, pages and a page size of 32 KB that will be allocated only on the partitions where DPG1 is defined, use the following statement:.

For the same database and partition groups as described above, to create a buffer pool named BP6 with a page size of 32 KB and a size of 25, pages on partitions 0, 1, 2, 3, 4, and 5 and a size of 35, pages on partitions 6 and 7, use either of the following statements:.

There are two main schools of thought regarding the number of buffer pools that should be configured for a database:. Use one big buffer pool and let DB2's aging algorithm take care of which pages are in the buffer pool.

Use multiple buffer pools and assign them to specific table spaces to ensure that highly active pages are kept in the buffer pool. If DB2 is installed in bit mode, there are limitations on the maximum amount of shared memory that can be addressed by a process.

Because the buffer pool is allocated in shared memory, there are limits on the maximum size of the buffer pool s that can be created for a database, regardless of the amount of physical memory on the system.

If DB2 is installed in bit mode, the shared memory limit is much higher, and most systems will run out of real memory before hitting the operating system limit. If they are properly sized and assigned to the right table spaces, multiple buffer pools will outperform a single buffer pool. However, multiple buffer pools will require constant monitoring and tuning in order to keep them performing optimally. Other than its size, a single buffer pool needs no tuning.

DB2 has a highly optimized algorithm for aging pages in the buffer pool that uses several techniques designed to optimize the buffer pool hit ratio by:. Placing pages that are unlikely to be accessed again on "Hate stacks," which are used to identify victim pages quickly. There are multiple applications accessing the database, and the user wants to favor a particular application. Huge tables i.

If tables are usually scanned concurrently, separate them into their own dedicated buffer pools. UDB will try to keep these pages in memory; but in aggregate, this could swamp the buffer pool and interfere with the access to other objects. To isolate high-priority or high-usage tables or indexes from other database activity.

If people occasionally run large reports on OLTP systems and the reports do large joins, sorts, etc. These temporary tables can sometimes overwhelm the buffer pool, so it can be beneficial to create a buffer pool for the temporary table space. Size this buffer pool according to the typical overflow sorts, if there are any, under normal operations. This buffer pool typically does not need to be large because it is normally accessed sequentially. This way, the reports have less impact on the overall performance of the online system.

Before creating large buffer pools, it may be necessary to increase the size of the database heap dbheap configuration parameter to hold the descriptors for the buffer pool pages. For a 1-GB buffer pool with a page size of 4 KB, there must be , pointers allocated in the database heap. This will use up approximately 25 MB of database heap.

For a GB buffer pool with a page size of 4 KB, there must be 8,, pointers allocated in the database heap. This will use up approximately MB of database heap. When DB2 is installed in bit mode, the amount of shared memory available and the memory management options are more limited than in bit mode. To guarantee that memory is available to create a new buffer pool or enlarge an existing buffer pool with the IMMEDIATE option, memory must be reserved when the database is started.

The total shared memory used by the buffer pools, lock list, database heap, shared sort heap threshold, package cache, and utility heap. The db2level command now displays whether DB2 is installed in or bit mode, in addition to the version and fixpack level. When DB2 is installed in bit mode, there are operating system limitations to the maximum size of a shared memory that a process can allocate. Because each database will have all of its shared memory allocated in the same segment, there is a maximum amount of shared memory that can be allocated per database.

The following items are allocated in shared memory:. Of these, only seven can be used by DB2 for shared memory. Of these seven segments or 1. Otherwise, any performance gain from the extra memory will be lost due to contention on disk. To force the FCM to use the network instead of shared memory, set the following DB2 registry variable to no. The maximum addressable amount of shared memory for the bit version of DB2 varies, depending on the operating system.

Table 2. When working with Windows , the total addressable memory can be up to 64 GB; therefore, the maximum buffer pool sizes that can be created on Windows equals 64 GB minus the memory used by the operating system and other DB2 memory allocations, assuming that the server is dedicated to DB2. To verify that you have the correct boot option selected, under Control Panel, select System, then select "Startup and Recovery. If the option you want is not available for selection, you must add the option to the boot.

The boot. Once you have saved this changed file, you can verify and select the correct boot option, as mentioned above. Windows also has to be modified to associate the right to "lock pages in memory" with the userid that was used to install DB2. To set the "lock pages in memory" correctly, once you have logged on to Windows as the user who installed DB2, under the Start menu on Windows , select the "Administrative Tools" folder, then the "Local Security Policy" program.

Under the local policies, you can select the user rights assignment for "lock pages in memory. This registry variable must be set to the buffer pool ID of the buffer pool that will be larger than 3 GB and have a need for AWE support, as well as the number of physical pages and the address window pages to be allocated for the buffer pool.

The number of physical pages to allocate should be less than the total number of available pages of memory, and the actual number chosen will depend on the working environment. For example, in an environment where only DB2 UDB and database applications are used on the server, normally select a value between one-half of the available memory up to 1 GB less than the available memory.

In an environment where other nondatabase applications are also running on the server, these values will need to be reduced to leave memory available for the other applications.

The upper limit on the address window pages is 1. When a database is activated or started i. These buffer pools are hidden and do not have entries in the system catalog tables. In addition, these buffer pools cannot be used directly by assigning table spaces to them and cannot be altered. There will be one hidden buffer pool per page size i. DB2 UDB will use these buffer pools under the following conditions:. Any table spaces that are using the buffer pool will be remapped to the hidden buffer pool with the same page size.

Any table space that is using a buffer pool that was not allocated will be remapped to the hidden buffer pool with the same page size. DB2 will be fully functional because of the hidden buffer pools, but performance will be drastically reduced.

When a table space is created and its page size does not correspond to the page size of any of the user created buffer pools. Any user-created table spaces that are assigned to this buffer pool will be remapped to the hidden buffer pool with the same page size for the duration of the roll forward operation. By default, the hidden buffer pools will be created with a size of 16 pages. To change the size of the hidden buffer pools to use 64 pages each, set the registry variable as follows:.

In previous versions of DB2 UDB, the buffer pool size could not be changed until the database was stopped and restarted i. With DB2 UDB Version 8, the buffer pool size can be changed immediately, provided that there is enough memory available to handle the request. However, changes to any other buffer pool configuration parameters will be deferred until the database is stopped and restarted.

If there is not enough reserved space in the database shared memory to allocate the new buffer pool, a warning is returned, and buffer pool creation will be DEFERRED. This is the default. Extended storage is more efficient than retrieving data from disk but less efficient than retrieving data from the buffer pool so is not applicable to bit environments. The buffer pool will be created on all database partitions that are part of the specified database partition group s.

The block size must be between 2 and pages, and the default value is 32 pages. Before altering the number of pages in a buffer pool, it is important to understand and assess the impact on applications accessing the database. A change in buffer pool size may result in a different access plan. In order to change the size of the buffer pool to MB use the following statement:. In order to change the size of the block-based area of the buffer pool to MB use the following statement:.

Changes to the size of the block-based area of the buffer pool will not take effect immediately. They will take effect when the database is stopped and restarted. All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. 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. After the updated data pages in the bufferpool are written on the disk, the bufferpool is ready to take another data. Each table space is associated with a specific buffer pool in a database.