Managing DB2 Memory Structures
Craig S. Mullins
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.
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.
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.
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.
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
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.
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.
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.
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
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, 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.
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
Trends, May 2001.
© 2001 Craig S. Mullins, All rights reserved.