Return to Home Page

Intelligently Managing DB2 Memory Structures
by Craig S. Mullins

DB2 for OS/390 requires a plethora of memory structures, commonly referred to as “pools” to manage, modify, and access the data it maintains. Constant vigilance is required to keep these pools optimally configured so that DB2 applications can achieve high performance and deliver required service levels. Pool Advisor, from BMC Software, is the first solution with the built-in intelligence to proactively automate DB2 pools in an automated manner.

BMC Pool Advisor helps to increase staff productivity, making an administrator’s job easier by ensuring that DB2 pools are optimally tuned continuously. Simply stated: DB2 works best when it uses memory efficiently and BMC Pool Advisor helps to ensure that DB2 makes effective use of memory.

What are the Pools and Why are They Important?

There are four pools used by DB2 to cache information in memory as it operates. The longer information can be cached into memory, the better the chance the data can be reused by other processes without having to read it from disk again.

The DB2 buffer pools are the first of the four pools. When data is read off of disk, DB2 places it into a buffer pool. Whenever a user or program accesses a particular piece of data, DB2 first checks for it in the buffer pools. If it exists there, DB2 can avoid an expensive I/O to disk, thereby enhancing performance. DB2 buffer pools provide one of the most fertile areas for performance tuning and optimization. In general, DB2 loves large buffer pools. But buffer pools are backed by memory, and memory is expensive. So tuning the size of the DB2 buffer pools appropriately based on application workload is important. But it is also complex. There are 80 different buffer pools used by DB2, each of which can have multiple DB2 table spaces and indexes assigned to them. And there are tuning knobs for each of the 80 buffer pools that control things like how the buffer pool is optimized for random versus sequential access and how parallel operations are handled.

Monitoring activity in the buffer pools for multiple, concurrent applications is often too difficult for resource constrained DBA groups. As such, buffer pool tuning is usually accomplished only when a problem occurs. And therefore, resources may be wasted (because buffer pools are over-allocated) or applications may be running slower (if buffer pools are under-allocated).

The second of DB2’s four pools is the EDM pool. The EDM pool is used by DB2 to control programs as they execute against DB2. It will contain structures that house the access paths of the SQL statements for running programs. DB2 also permits dynamic SQL prepare information to be cached in the EDM pool to optimize dynamic SQL. But that is not all. The EDM pool also contains database information (DBDs) for the databases being accessed. Managing the size and efficiency of the EDM pool is vitally important because if there is no room in the EDM pool, a critical application may not be allowed to run.

The third of the four pools is the RID pool. The RID pool is used by DB2 to sort RIDs (record identifiers) for List Prefetch, Multiple Index Access, and Hybrid Join access paths.  RID pool failures can cause performance degradation as alternate access paths are invoked, such as scans, and the CPU invested up to the point of the failure is wasted.

Finally, the Sort pool is used by DB2 to perform highly efficient internal sorts of data. Sort pool performance degradations can impact elapsed times dramatically and sort failures can terminate a statement.

Why is Pool Advisor Revolutionary?

BMC Pool Advisor for DB2 for OS/390 is decision-making software that analyzes your DB2 pools and proactively makes recommendations that can be automatically deployed with your consent. Using the Pool Advisor will help to ensure application availability and enable you to meet your stated service level agreements.

The Pool Advisor is revolutionary because it tells you what is wrong and how to fix it before you have a problem. It has built-in DB2 expertise—just like an expert DB2 performance consultant. And Pool Advisor can automate DB2 pool tuning, automatically making the changes it recommends if you so desire.

So, with BMC Pool Advisor for DB2, you will be able to have a DB2 environment that dynamically adapts to your ever-changing application workloads. It will continuously optimize your DB2 buffer pools, EDM pool, RID Pool, and Sort pool.

And isn’t that what you’re looking for? Software that makes complicated things like pool tuning a little easier.  Software that works automatically to proactively identify and then correct performance problems before they happen. The Pool Advisor from BMC Software—it’s like having an expert DB2 consultant bottled up in your system automatically ensuring that your pools are configured correctly.

From Database Trends, May 2001.
 
© 2001 Craig S. Mullins,  All rights reserved.
Home.