The Buffer
Pool attributes provide information about buffer pool activities.
You can use this information to monitor the performance
of your buffer pools and to identify problem areas for corrective
action. All values are integers that are calculated from the first
application connection, unless otherwise noted.
Avg Data Page Read per Async Req The
average number of pages read for each asynchronous request. This
value is derived by dividing the value of the Pool Async Data Reads
attribute by the value of the Pool Async Data Read Reqs attribute.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Avg Direct Read Time The average
elapsed time for a direct read request. This value is calculated
by dividing the value of the Direct Read Time attribute by the value
of the the Direct Reads attribute. This average is important because
it might indicate the presence of an I/O wait, which in turn might
indicate that you must move data to a different device. The following
value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Avg Direct Write Time The average
elapsed time for a direct write request. This value is calculated
by dividing the value of the Direct Write Time attribute by the value
of the Direct Writes attribute. This average is important because
it might indicate the presence of an I/O wait, which in turn might
indicate that you must move data to a different device. The following
value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Avg Pool Read Time The average elapsed
time for a read request. This value is derived by dividing the
value of the Pool Read Time attribute by the value of the Pool Total
Reads attribute. This average is important because it might indicate
the presence of an I/O wait, which in turn might indicate that you
must move data to a different device. The following value is also
valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Avg Pool Write Time The average
elapsed time for a write request. This value is derived by dividing
the value of the Pool Write Time attribute by the value of the Pool
Total Writes attribute. The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Avg Sync Read Time The average elapsed
time used to perform a synchronous read. This value is derived
by dividing the value of the Pool Sync Read Time attribute by the
value of the Pool Sync Read attribute. This average is important because
it might indicate the presence of an I/O wait, which in turn might
indicate that you must move data to a different device. The following
value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Avg Sync Write Time The average
elapsed time used to perform a synchronous write. This value
is derived by dividing the value of the Pool Sync Write Time attribute
by the value of the Pool Sync Write attribute. This average is important
because it might indicate the presence of an I/O wait, which in turn
might indicate that you must move data to a different device. The
following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
BP ID The internal identifier for
the buffer pool. This attribute is for internal use only.
BP Name The name of the buffer
pool. A new database has a default buffer pool (named IBMDEFAULTBP).
The size of the default buffer pool is determined by the platform.
Depending on your needs you might choose to create several buffer
pools, each of a different size, for a single database. The CREATE,
ALTER, and DROP BUFFERPOOL statements allow you to create, change,
or remove a buffer pool.
DB Name The real name of the database
for which information is collected or to which the application is
connected. This name was given to the database when it was created.
The value format is a simple text string with a maximum of 60 characters.
Use this attribute to identify the specific database to which the
data applies.
DB Partition The DB2® database partition node number, which can
range from 0 to 999. The Aggregated and Current® Partition values can be used within
a query or situation filter. If a db partition filter is not specified,
data is returned for the current database partition. If a db partition
filter is set to Aggregated, only aggregated partition data is returned.
Historical data collection includes both aggregated and individual
partition attribute data. In addition to numeric partition numbers
in the 0 to 999 range, the following values are also valid:
| External value |
Internal value |
| Aggregated |
-1 |
| Current Partition |
-2 |
| All |
-3 |
DB Path The full path of the location
where the database is stored on the monitored system. Use this
attribute with the Database Name attribute to identify the specific
database to which the data applies.
Direct Read Reqs The number of requests
to perform a direct read of one or more sectors of data. Use
the following formula to calculate the average number of sectors that
are read by a direct read:
direct reads from database / direct read requests
The
following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Direct Read Time The elapsed time
(in milliseconds) required to perform the direct reads. Use the
following formula to calculate the average direct read time per sector:
direct read time / direct reads from database
A
high average time might indicate an I/O conflict.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Direct Reads The number of read
operations that do not use the buffer pool. Use the following
formula to calculate the average number of sectors that are read by
a direct read:
direct reads from database / direct read requests
When
using system monitors to track I/O, this data attribute helps to distinguish
database I/O from non-database I/O on the device.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Direct Write Reqs The number of requests
to perform a direct write of one or more sectors of data. Use
the following formula to calculate the average number of sectors that
are written by a direct write:
direct writes to database / direct write requests
The
following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Direct Write Time The elapsed time
(in milliseconds) required to perform the direct writes. Use
the following formula to calculate the average direct write time per
sector:
direct write time / direct writes to database
A
high average time might indicate an I/O conflict.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Direct Writes The number of write
operations that do not use the buffer pool. Use the following
formula to calculate the average number of sectors that are written
by a direct write:
direct writes to database / direct write requests
When
using system monitors to track I/O, this data attribute helps to distinguish
database I/O from non-database I/O on the device.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Files Closed The total number
of database files closed. The database manager opens files for
reading and writing into and out of the buffer pool. The maximum number
of database files open by an application at any time is controlled
by the MAXFILOP configuration parameter. If the maximum is reached,
one file is closed before the new file is opened. Note that the actual
number of files opened might not equal the number of files closed.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Input DB Alias The alias of the
database provided when calling the snapshot function. The value
format is a simple text string with a maximum of 60 characters. Use
this attribute to help you identify the specific database to which
the monitor data applies. It contains blanks unless you requested
monitor information related to a specific database.
Node Name For new installations
of version 6, release 2, the format is instanceid:hostname:UD for
all operating systems. The format for version 6, release 1 of
theDB2 agent on Windows systems
is instanceid:hostname:UD; on UNIX and Linux® systems, the format is instanceid:hostname.
Pool Async Data Read Reqs The number
of asynchronous read requests. To calculate the average number
of data pages read per asynchronous request, use the following formula:
buffer pool asynchronous data reads / buffer pool asynchronous read requests
This
average can help to determine the amount of asynchronous I/O in each
interaction with the prefetcher.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Async Data Reads The number
of pages read asynchronously into the buffer pool. Use this attribute
with the Buffer Pool Data Physical Reads attribute to calculate the
number of physical reads that were performed synchronously (that is,
physical data page reads that were performed by database manager agents).
Use the following formula:
buffer pool data physical reads - buffer pool synchronous data reads
By
comparing the ratio of asynchronous to synchronous reads, you can
gain insight into how well the prefetchers are working.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Async Data Writes The number
of times a buffer pool data page was physically written to disk by
an asynchronous page cleaner or by a prefetcher. A prefetcher
might have written dirty pages to disk to make space for the pages
being prefetched. Use this attribute with the Buffer Pool Data Writes
attribute to calculate the number of physical write requests that
were performed synchronously (that is, physical data page writes that
were performed by database manager agents). Use the following formula:
buffer pool data writes - buffer pool asynchronous data writes
By
comparing the ratio of asynchronous to synchronous writes, you can
gain insight into how well the buffer pool page cleaners are performing.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Async Index Reads The number
of index pages read asynchronously into the buffer pool by a prefetcher. Asynchronous
reads are performed by database manager prefetchers. Use this attribute
with the Buffer Pool Index Physical Reads attribute to calculate the
number of physical reads that were performed synchronously (that is,
physical index page reads that were performed by database manager
agents). Use the following formula:
buffer pool index physical reads - buffer pool asynchronous index reads
By
comparing the ratio of asynchronous to synchronous reads, you can
gain insight into how well the prefetchers are working.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Async Index Writes The number
of times a buffer pool index page was physically written to disk by
an asynchronous page cleaner or a prefetcher. A prefetcher might
have written dirty pages to disk to make space for the pages being
prefetched. Use this attribute with the Buffer Pool Index Writes attribute
to calculate the number of physical index write requests that were
performed synchronously. That is, physical index page writes that
were performed by database manager agents. Use the following formula:
buffer pool index writes - buffer pool asynchronous index writes
By
comparing the ratio of asynchronous to synchronous writes, you can
gain insight into how well the buffer pool page cleaners are performing.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Async Read Time The total
elapsed time spent reading by database manager prefetchers. Use
this attribute to calculate the elapsed time for synchronous reading,
using the following formula:
total buffer pool physical read time - buffer pool synchronous read time
You
can also use this attribute to calculate the average asynchronous
read time using the following formula:
buffer pool asynchronous read time / buffer pool asynchronous data reads
These
calculations can be used to understand the I/O work being performed.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Async Write Time The total
elapsed time spent writing data or index pages from the buffer pool
to disk by database manager page cleaners. Calculate the elapsed
time spent writing pages synchronously by subtracting the value of
the Pool Async Write Time attribute from the value of the Pool Physical
Write Time attribute. You can also use this attribute to calculate
the average asynchronous read time:
- Sum the value of the Pool Async Data Writes attribute and the
value of the Pool Async Index Writes attribute.
- Divide the value of the Pool Async Write Time attribute by the
sum from step 1.
These calculations can be used to understand the I/O work being
performed.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Data from Estore Number of
buffer pool data pages copied from extended storage. Required
pages are copied from extended storage to the buffer pool. The copy
process might incur the cost of connecting to the shared memory segment,
but it saves the cost of a disk read. The following value is also
valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Data L Reads The number of
logical read requests for data pages that have gone through the buffer
pool. This count includes accesses to the following data:
- Data that is already in the buffer pool when the database manager
needs to process the page.
- Data that is read into the buffer pool before the database manager
can process the page.
By using the Pool Data Physical Reads attribute, you can calculate
the data page hit ratio for the buffer pool as follows:
1 - (buffer pool data physical reads / buffer pool data logical reads)
By
using the Pool Data Physical Reads, Pool Index Physical Reads, and
Pool Index Logical Reads attributes, you can calculate the overall
buffer pool hit ratio as follows:
1 - ((buffer pool data physical reads + buffer pool index physical reads)
/ (buffer pool data logical reads + buffer pool index logical reads))
Increasing
buffer pool size generally improves the hit ratio until you reach
a point of diminishing returns.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Data P Reads The number of
read requests that required I/O to get data pages into the buffer
pool.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Data to Estore Number of buffer
pool data pages copied to extended storage. Pages are copied
from the buffer pool to extended storage when they are selected as
victim pages. As a result of the copying process, there is sufficient
space for new pages in the buffer pool. The following value is also
valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Data Writes The number of times
a buffer pool data page was physically written to disk. A buffer
pool data page is written to disk for the following reasons:
- To free a page in the buffer pool so another page can be read
- To flush the buffer pool.
If a buffer pool data page is written to disk for a high percentage
of Buffer Pool Data Physical Reads, performance might improve by increasing
the number of buffer pool pages available for the database.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Hit Ratio The buffer pool hit
ratio (as a percentage). The sum of the Pool Data Logical Reads
and Pool Index Logical Reads attributes is divided by the value of
the Pool Total Reads attribute to derive the pool hit ratio. This
attribute can determine whether buffer pool assignment is efficient.
If the pool hit ratio is low, increasing the number of buffer pool
pages might improve performance.
Pool Index from Estore Number of
buffer pool index pages copied from extended storage. Required
index pages are copied from extended storage to the buffer pool. The
copy process might incur the cost of connecting to the shared memory
segment, but it saves the cost of a disk read. The following value
is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Index L Reads The number of
logical read requests for index pages that have gone through the buffer
pool. This count includes accesses to the following index pages:
- Pages that are already in the buffer pool when the database manager
needs to process the page.
- Pages that are read into the buffer pool before the database manager
can process the page.
By using the Buffer Pool Index Physical Reads attribute, you
can calculate the index page hit ratio for the buffer pool as follows:
1 - (buffer pool index physical reads / buffer pool index logical reads)
If
the hit ratio is low, increasing the number of buffer pool pages might
improve performance.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Index P Reads The number of
physical read requests to get index pages into the buffer pool. see
the Pool Index Logical Reads attribute for information about how to
use this element. The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Index to Estore Number of buffer
pool index pages copied to extended storage. Pages are copied
from the buffer pool to extended storage when they are selected as
victim pages. As a result of the copying process, there is sufficient
space for new pages in the buffer pool. The following value is also
valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Index Writes The number of times
a buffer pool index page was physically written to disk. If a
buffer pool index page is written to disk for a high percentage of
Buffer Pool Index Physical Reads, performance might improve by increasing
the number of buffer pool pages available for the database. If all
applications are updating the database, increasing the size of the
buffer pool might have minimal impact on performance; most pages contain
updated data that must be written to disk. The following value is
also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Read Time The total amount
of elapsed time spent processing read requests that caused data or
index pages to be physically read from buffer pool to disk. Use
this attribute with the Buffer Pool Data Physical Reads and Buffer
Pool Index Physical Reads attributes to calculate the average page-read
time. This average is important because it might indicate the presence
of an I/O wait, which in turn might indicate that you must move data
to a different device. The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Data Reads The number
of physical data page reads that were performed by database manager
agents. This value is derived by subtracting the value of the
Pool Async Data Reads attribute from the Pool Data Physical Reads
attribute. By comparing the ratio of asynchronous to synchronous reads,
you can gain insight into how well the prefetchers are working. The
following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Data Writes The total
number of physical write requests that were performed synchronously
(that is, physical data page writes that were performed by database
manager agents). This value is derived by subtracting the value
of the Pool Async Data Writes attribute from the value of the Pool
Data Writes attribute. By comparing the ratio of asynchronous to synchronous
writes, you can gain insight into how well the buffer pool page cleaners
are performing. The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Index Reads The number
of index pages read synchronously (that is, physical index page reads
that were performed by database manager agents) into the buffer pool. This
value is derived by subtracting the value of the Pool Async Index
Reads attribute from Pool Index Physical Reads attribute. By comparing
the ratio of asynchronous to synchronous reads, you can gain insight
into how well the prefetchers are working. The following value is
also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Index Writes The number
of physical index write requests that were performed synchronously
(that is, physical index page writes that were performed by database
manager agents). This value is derived by subtracting the value
of the Pool Async Index Writes attribute from the value of the Pool
Index Writes attribute. By comparing the ratio of asynchronous to
synchronous writes, you can gain insight into how well the buffer
pool page cleaners are performing. The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Read The total number
of synchronous reads. This value is derived by adding the values
of the Pool Sync Data Reads and Pool Sync Index Reads attributes.
The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Read Time The elapsed
time used to perform all synchronous reads. This value is derived
by subtracting the value of the Pool Async Read Time attribute from
the value of the Pool Read Time attribute. Use this attribute to understand
the I/O work being performed. The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Write The total number
of synchronous index writes. The value is derived by adding the
values of the Pool Sync Data Writes attribute and Pool Sync Index
Writes attribute. The following value is also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Write Time The total elapsed
time used to perform all synchronous writes. This value is derived
by subtracting the value of the Pool Async Write Time attribute from
the value of the Pool Write Time attribute. The following value is
also valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Total Reads The total number
of read requests that required I/O to get data pages and index pages
into the buffer pool. This attribute is the total of the Pool
Data Physical Reads and Pool Index Physical Reads attributes. Values
that are greater than or equal to 9223372036854775807 are indicated
with the
Value Exceeds Maximum text in the portal.
The following value is valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Total Writes The total number
of write requests. This attribute is the total of the Pool Data
Writes and Pool Index Writes attributes. Values that are greater than
or equal to 9223372036854775807 are indicated with the
Value
Exceeds Maximum text in the portal. The following value is
valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Pool Write Time The total amount
of time spent physically writing data or index pages from the buffer
pool to disk. Use this attribute with the Buffer Pool Data Writes
and Buffer Pool Index Writes attributes to calculate the average page-write
time. This average is important because it might indicate the presence
of an I/O wait, which in turn might indicate that you must move data
to a different device. The following value is valid:
| External value |
Internal value |
| Value Exceeds Maximum |
9223372036854775807 |
Snapshot Timestamp The date and
time when the database system monitor information was collected. Use
this attribute to help relate data chronologically if you are saving
the results in a file or database for ongoing analysis.