Use these
attributes to obtain information about the efficiency of the database
and identify any problem areas for corrective action.
All values are integers that are calculated from the first
application connection, unless otherwise noted.
Appl Control Heap Size The maximum
size (in 4-KB pages) for the application control heap in the database
during the monitoring interval. The heap is required to share
information among agents working on behalf of the same application
at a node in a massively parallel processing (MPP) or a symmetric
multiprocessor (SMP) system. If complex applications are being run
or the MPP configuration has a large number of nodes, you must increase
the size of this heap. In a partitioned database environment, this
heap is used to store copies of the executing section of SQL statements
for agents and subagents. However, symmetric multiprocessor agents
(SMP), subagents, and agents in all other environments use appl heap
size.
Appl Heap Size The size (in 4-KB
pages) of the application heap that is available for each individual
agent in the database during the monitoring interval. Increase
the value of the parameter if your application receives an error indicating
that there is not enough storage in the application heap. The heap
is allocated when an agent or subagent is initialized for an application.
The amount allocated is the minimum amount needed to process the request
given to the agent or subagent. When the agent or subagent requires
more heap space to process larger SQL statements, the database manager
allocates memory as needed, up to the maximum specified by the parameter.
Appl Section Inserts The number
of inserts of SQL sections by an application from its SQL work area. The
working copy of any executable section is stored in a unique SQL work
area. This value represents the number of times when a copy was not
available and therefore was inserted. The following value is also
valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Appl Section Lookups The number
of lookups of SQL sections by an application from its SQL work area. This
counter indicates how many times the SQL work area was accessed by
agents for an application. It is a cumulative total of all lookups
on all SQL work heaps for agents working on this application. The
following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Appls in DB2® The
number of applications currently executing in the database. The
following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Appls The value of the average
number of active applications.
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.
Use this attribute to determine whether good enough data pages were
read per asynchronous request. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Direct Read Time The average
time in milliseconds that is used to perform direct reads to the database. The
value is derived through this formula:
direct read time / direct reads
The
following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Direct Write Time The average
time in milliseconds for performing direct writes to the database. A
high average time can indicate the existence of an input and output
conflict. The value is derived through this formula:
direct write time / direct write
The
following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Lock Escal per Conn for Interval The
average lock escalations per connection for this database during the
monitoring interval. The value format is an integer. A lock is
escalated when the total number of locks that an application holds
reaches the maximum amount of lock list space available to the application,
or the lock list space consumed by all applications is approaching
the total lock list space. When an application reaches the maximum
permitted number of locks and no additional locks can be escalated,
the application uses space in the lock list that is allocated for
other applications. When the entire lock list is full, an error occurs.
The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Locks Held The average number
of locks held by each currently connected application in the database. The
value is derived through this formula:
locks held / appls cur cons
If
the returned value is high compared to normal operating levels, it
can indicate that one or more applications is using an excessive number
of locks. Refine such applications to improve performance.
The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Pages per Cleaner for Interval The
average number of pages written per page cleaner that are invoked
for the database during the monitoring interval. Use the returned
value to determine how many pages are handled by the page cleaners
of this database. If this value increases over time, you can define
more page cleaners. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Pool Async Data Reads The average
number of buffer pool asynchronous data reads when compared to the
total number of pool reads for the database. The value is derived
through this formula:
pool async data reads / (pool data p
reads + pool index p reads)
. Use the returned value to gain
insight into how well the prefetchers are working and to refine the
num_ioservers configuration parameter. If the returned value is low
compared to normal operating levels, there might not be enough input
and output servers to prefetch data into the buffer, causing the database
manager agents to spend extra time on physical reads. Increase the
number of input and output servers by increasing the value of the
num_ioservers configuration parameter. If too many servers are allocated,
system performance is not reduced because the extra input and output
servers are not used. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Pool Async Data Writes The
average number of buffer pool asynchronous data writes (data and index)
when compared to the total number of pool writes for the database. The
value is derived through this formula:
pool async data writes
/ (pool data writes + pool index writes)
. Use the returned
value to gain insight into how well the page cleaners are working
and to refine the num_iocleaners configuration parameter. If the returned
value is low compared to normal operating levels, increase the number
of input and output cleaners by increasing the value of the num_iocleaners
parameter. If the returned value is high compared to normal operating
levels, you can save system resources by decreasing the number of
input and output cleaners (by decreasing the value of the num_iocleaners
parameter). The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Pool I/O Time The average time
(in milliseconds) for performing buffer pool input and output operations
(reading or writing) to the database. A high average time can
indicate the existence of an input and output conflict. In this case,
you might need to move data to a different device. The returned value
includes the time applied to asynchronous input and output operations
(which are performed by prefetchers and page cleaners). The following
value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Pool Writes per Read The ratio
of total pool writes to pool reads for the database. The value
is derived through this formula:
(pool data writes + pool
index writes) / (pool data p reads + pool index p reads)
.
If the returned value is greater than 1, you can improve performance
by increasing the available buffer pool space. A returned value greater
than 1 indicates that at least one write to disk had to occur (either
to free a page in the buffer pool, or to flush the buffer pool) before
a page can be read into the buffer pool. You can increase the available
buffer pool space by freeing the space more often or by increasing
the total space for the buffer pool. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Sect Read per Direct Read The
average number of sectors that are read by a direct read for the database. The
value is derived through this formula:
direct reads / direct
read reqs
. Direct reads do not use the buffer pool, and so
result in poor performance because the data is physically read from
disk each time. If you are using system monitors to track input and
output for the device, this value helps you distinguish database input
and output from non-database input and output. The following value
is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Sect Written per Direct Write The
average number of sectors that are written by a direct write to the
database. The value is derived through this formula:
direct
writes / direct write reqs
. Direct writes do not use the
buffer pool, which results in poor performance because the data is
physically written from disk each time. If you are using system monitors
to track input and output for the device, this value helps you distinguish
database input and output from non-database input and output. The
following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Avg Sync I/O Time The average time
(in milliseconds) to perform synchronous input and output operations
for the database. Use the returned value to analyze the input
and output work being performed for the database. Synchronous input
and output operations for a database are performed by database manager
agents. Asynchronous input and output operations are performed by
prefetchers (reads) and page cleaners (writes). In general, asynchronous
input and output helps your applications run faster. 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 |
Binds Precompiles The number of
binds and precompiles attempted. Use this attribute to gain insight
into the current level of activity within the database manager. The
following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Buff Page Use the returned value
(in units of 4-KB pages) to analyze the input and output work being
performed for the database. Synchronous input and output operations
for a database are performed by database manager agents. Asynchronous
input and output operations are performed by prefetchers (reads) and
page cleaners (writes). In general, asynchronous input and output
helps your applications run faster. In the currently supported releases
of DB2, multiple buffer pools
might be defined in a single database. For instance, buffer pools
can be defined and associated with a particular tablespace. Each buffer
pool created can be given its own individual size. The buffpage attribute
serves only as a default value for buffer pools created within a particular
database. Therefore, the value of the buffpage attribute is much less
critical to performance in current releases of DB2, because most buffer pools are given an individual
size when created. The buffpage attribute must not be used to evaluate
or tune the performance of DB2 unless
it is used as the default value when creating buffer pools in a database.
The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Catalog Cache Size The value in
units of 4-KB pages of the catalog cache size. This value is
the maximum amount of space that the catalog cache can use from the
database heap (dbheap). The catalog cache is referenced whenever a
table, view, or alias name is processed during the compilation of
an SQL statement. It is dynamically allocated from dbheap, as required,
until the catalog cache size is reached.
Change Pages Threshold The value
in percentage units of the changed pages threshold. This value
sets a limit on how much buffer pool space can be occupied by changed
pages before the asynchronous page cleaners are started, if they are
not currently active. Asynchronous page cleaners write changed pages
from the buffer pool to disk before the space in the buffer pool is
required by a database agent. This means that the agents do not need
to wait for a changed page to be written out before being able to
read a page, and application transactions run faster.
Commit Stmts per Sec The total number
of commits initiated internally by the database per second. Use
the returned value to determine rates of database activity. The following
value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Cur Cons Percent The percentage
of applications currently connected.
Current Primary
Log Used Percent The percentage of primary log space that
is currently in use.
Current Secondary
Log Used Percent The percentage of secondary log space that
is currently in use.
Database Heap The value in units
of 4-KB pages of the database heap. This value is the maximum
amount of memory allowed for a database heap. There is one database
heap for each database. It is used on behalf of all applications connected
to the database. Refining dbheap has minimal impact on performance.
The main function of this parameter is to prevent the database manager
from allocating an excessive amount of space for a particular database.
The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Days Since Last Backup The numbers
of days since the last database backup was completed. The value
format is an integer. The value for no backup completed is 2147483647.
The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
DB Capture Error The number of errors
encountered by the Capture program within the last five minutes. Use
the returned value to determine whether the Capture program encountered
an error that prevented it from running. If any errors are detected,
the Capture program came down at the time the error occurred. The
Capture program might or might not still be down. The Capture program
is the most critical replication component in the replication system.
If the Capture program is not active, there are no new change records
to apply to the target systems. If your data concurrency requirements
are high and you want to ensure that the Capture program runs continuously,
use this monitor to determine when the Capture program encounters
an error that prevents it from running. The following value is also
valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Values for this attribute are only available in a SQL
replication environment.
DB Capture Lag The time difference
in minutes between the current timestamp and the last timestamp recorded
by the Capture program. This time difference is the Capture lag.
Use the returned value to determine whether the Capture program is
keeping up with the DB2 database
log. The Capture program uses an interface to the DB2 database log or journal to detect and save
changes to the data in the tables registered for replication. The
following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Values for this attribute are only available in a SQL
replication environment.
DB Capture Prun The number of rows
in the unit-of-work table. Use the returned value to help you
determine whether you need to prune the unit -of-work (UOW) table
or the change data (CD) table. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Values for this attribute are only available in a SQL
replication environment.
DB Heap Top This data attribute
(now maintained for DB2 version
compatibility) measures memory usage, but not exclusively usage by
the database heap. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
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.
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 Tablespaces The number of Database
Managed Space tablespaces in the database. Use this attribute
to track database growth over a period of time. The following value
is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
DDL SQL Percent for Interval The
percentage of total SQL statements that were SQL DDL statements during
the monitoring interval. Due to the high activity in the system
catalog tables, try to keep DDL statement activity to a minimum. If
the returned value is high compared to normal operating levels, determine
the activity causing it to be high and restrict it from being performed.
Examples of DDL statements are CREATE TABLE, CREATE VIEW, ALTER TABLE,
and DROP INDEX. You can also use the returned value to refine the
package cache hit ratio for this application. DDL statements can also
affect the package cache by invalidating sections that are stored
there and causing additional system overhead due to section recompilation.
Deadlock Rollbacks Percent The percentage
of the total number of rollbacks that deadlock caused. The value
format is an integer.
Deadlocks for Interval The number
of deadlocks detected in the database during the monitoring interval. Use
the returned value to determine whether applications are experiencing
conflict problems in the database. You can resolve the problem by
determining in which applications the deadlocks are occurring. You
can then try to modify the applications to better enable them to run
concurrently. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Estore Read/Write Ratio for Interval The
ratio as a percentage of data and index pages copied from extended
storage to pages copied to extended storage during the monitoring
interval. When a page is transferred from extended storage to
the buffer pool, you save a system input and output call. However,
you still incur the cost of attaching to the extended memory segment,
copying the page, and detaching from the segment. Use the returned
value to determine if you would benefit from using extended storage.
The higher the ratio, the more likely you are to benefit. In general,
extended storage is particularly useful if input and output activity
is very high on your system. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Event Monitors The number of event
monitors defined in the database. Use the returned value to determine
how many event monitors are defined for the database. When you define
an event monitor, its definition is stored in the database system
catalog table. You can create any number of event monitors. However,
the maximum number of event monitors that can be active for a database
at any given time is 32. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Failed SQL Stmts Percent for Interval The
percentage of total Structured Query Language statements that failed
during the monitoring interval. The value format is an integer.
Instance Name The name of the monitored DB2 instance. The valid format
is a text string with a maximum of 60 bytes.
Internal Auto Rebinds The number
of automatic rebinds or recompiles that were attempted in the database. Use
the returned value to determine the level of database activity. Automatic
rebinds are the internal binds that the system performs when a package
is invalidated. They can have a significant impact on performance
and must be minimized where possible. The following value is also
valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Internal Commits The total number
of commits initiated internally by the database. Use the returned
value to gain insight into internal activity within the database.
The returned value is also used in calculating "Commit statements
per second." The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Internal Deadlock Rollbacks Percent The
percentage of the total number of internal rollbacks due to deadlocks. Use
the returned value to distinguish those rollbacks caused by internal
deadlocks from rollbacks caused by other situations (for example,
incomplete imports). The returned value is the percentage of internal
rollbacks due to internal deadlocks since the first database connection
or the last reset of the database monitor counters.
Internal Deadlock Rollbacks Percent for
Interval The percentage of rollbacks that were due to deadlock
during the monitoring interval. Use the returned value to distinguish
those rollbacks caused by internal deadlocks from rollbacks caused
by other situations (for example, incomplete imports). The returned
value is the percentage of internal rollbacks due to internal deadlocks
since the first database connection or the last reset of the database
monitor counters.
Internal Rows Deleted The number
of rows deleted from the database as a result of internal activity. Use
the returned value to gain insight into internal activity within the
database. If this activity is high compared to normal operating levels,
you can evaluate your table design to determine if the referential
constraints or triggers that you defined on your database are necessary.
The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Internal Rows Inserted The number
of rows inserted into a database as a result of internal activity
caused by triggers. Use the returned value to gain insight into
internal activity within the database. If this activity is high compared
to normal operating levels, you can evaluate your design to determine
if you can alter it to reduce this activity. The following value is
also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Internal Rows Updated The number
of rows updated in the database as a result of internal activity. Use
the returned value to gain insight into internal activity within the
database. If this activity is high compared to normal operating levels,
you can evaluate your table design to determine if the referential
constraints that you defined are necessary. The following value is
also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Invalid Packages The number of all
packages that are currently marked not valid in the database. Use
the returned value as an indication of the current number of packages
that are not valid. A package is marked not valid if it depends on
an object (for example, a table) and that object is dropped. The number
of packages that are not valid can indicate how many automatic rebinds
are necessary in the database. Such packages automatically rebound
the next time they are accessed, unless a trigger was dropped or the
dropped object was not re-created. Use of automatic rebinds can significantly
lower performance, and must be minimized if possible. The following
value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Invalid System Packages The number
of system packages that are currently marked not valid in the database. Use
the returned value as an indication of the current number of nonvalid
packages owned by the system. A package is marked not valid if it
depends on an object (for example, a table) and that object is dropped.
The number of packages that are not valid can indicate how many automatic
rebinds are necessary in the database. The package is automatically
rebound the next time it is accessed, unless it was marked not valid
because a trigger was dropped or because the dropped object was not
re-created. Use of automatic rebinds can significantly lower performance,
and must be minimized where possible. The following value is also
valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Invalid Triggers The number of triggers
that are marked not valid in the database. Use the returned value
to determine the number of triggers that must be revalidated. A trigger
is marked not valid if an object on which the trigger depends is dropped.
To revalidate such a trigger, retrieve its definition from the database
system catalog and submit a new CREATE TRIGGER statement. The following
value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Lock List The value in units of
4-KB pages of the maximum storage for lock lists. This value
is the amount of storage that is allocated to the lock list. There
is one lock list for each database, and it contains the locks held
by all applications concurrently connected to the database. Too small
a value can lead to excessive lock waits. Too high a value compared
to normal operating levels can deprive the system of resources or
memory. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Lock List in Use Percent The percentage
of space used in the locklist of this database. Use the returned
value to determine how much of the locklist space is free for new
locks to be requested.
Lock Timeouts for Interval The
number of times that a request to lock an object were timed out instead
of being granted during the monitoring interval. The value format
is an integer. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Lock Waits for Interval The number
of times that applications had to wait for locks in the database during
the monitoring interval. Use the returned value as an indication
of how much time is applied to waiting for locks during a particular
monitoring interval. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Lock Waits Percent The percentage
of currently connected applications that are waiting for a lock in
the database. The value is derived through this formula:
100 * locks waiting / appls cur cons
If
the returned value is high compared to normal operating levels, the
applications can have concurrency problems. You must identify applications
that are holding locks or exclusive locks for long periods of time
and determine whether they can release their locks more often.
Log Buffer Size This value specifies
the amount of the database heap to use as a buffer for log records
before writing these records to disk. It is important that the
log buffer can hold the amount of log space used by an average transaction.
Otherwise, logging performance decreases and slows the overall system.
Log I/O for Interval The total
amount of log input and output. This amount is the sum of the
number of log pages read and the number of log pages written within
the monitoring interval. Use the returned value to determine whether
you must move the log to a different device. If this input and output
is beyond the capabilities of the current device, you can determine
if moving the log (by changing the newlogpath configuration parameter)
improves performance. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Log Primary The number of primary
log files.
Max Active Applications The value
of the maximum number of active applications. This value is the
maximum number of concurrent applications that can be connected (both
local and remote) to a database. Because each application that attaches
to a database causes some private memory to be allocated, allowing
a large number of concurrent applications potentially uses more memory.
Increasing the value of this parameter without lowering the maxlocks
parameter or increasing the locklist parameter can cause you to reach
the database limit on locks (locklist) rather than the application
limit. The result can be pervasive lock escalation problems.
Max Locks The value of the maximum
percentage of lock list before escalation. This value specifies
the percentage of the lock list that an application can hold before
the database manager performs lock escalation. Lock escalation can
increase contention, which reduces system throughput and increases
user response time. The values for the maxlocks and maxappls parameters
must satisfy (MAXLOKS x MAXAPPLS) >100, and each lock uses 32 bytes.
Rebind application packages after changing this parameter.
Min Commit The value of the number
of commits to group. By using this parameter you can delay the
writing of log records to disk until a minimum number of commits have
been performed. This delay can help reduce the overhead associated
with writing log records and can improve performance. The default
value for mincommit is 1, which can be too low for your environment.
By sampling the number of transactions per-second throughout the day,
you can determine the peak per second rate and adjust the value of
the mincommit parameter to accommodate all or most transactions. This
adjustment minimizes the number of log writes under the heaviest conditions.
As you increase the value of the mincommit parameter, you might also
need to increase the log buffer size (LOGBUFSZ parameter) to avoid
filling the log buffer. Filling the log buffer also forces the writing
of log records to disk. If you change the value of the mincommit parameter,
you must change the value for the logbufsz configuration parameter.
New Log Path The current value of
the newlogpath configuration parameter. A valid value is a text
string up to 768 characters in length. You use the newlogpath configuration
parameter to specify a new location for the log files. The specified
path does not become the current log path until both of the following
conditions are met:
- The database is in a consistent state.
- All users are disconnected from the database.
When the first new connection is made to the database, the database
manager moves the logs to this location.
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
the DB2 agent on Windows systems is instanceid:hostname:UD;
on UNIX and Linux® systems, the format is instanceid:hostname.
Number of I/O Cleaners The current
value of the number of asynchronous page cleaners. This parameter
specifies the number of asynchronous page cleaners for a database.
Page cleaners monitor the buffer pool and asynchronously write out
changed pages to disk to free space in the buffer pool.
Num IO Servers The current value
of the number of input and output servers. This value specifies
the number of input and output servers for a database. Input and output
servers are used on behalf of the database agents to perform asynchronous
input and output operations for utilities such as backup and restore,
and to perform prefetch input and output (in which case, they are
called prefetchers) operations. Prefetchers read pages from disk into
the buffer pool in anticipation of their use. In most situations,
these pages are read just before they are needed. However, prefetchers
can cause unnecessary input and output operations by reading pages
into the buffer pool that might not be used. For example, an application
starts reading through a table, and prefetchers read consecutive pages
into the buffer pool before the pages are required by the application.
Then the application fills the application buffer and stops reading.
Meanwhile, the prefetchers already have performed the input and output
operations for additional pages and the buffer pool is partially taken
up with those pages. To exploit all the input and output devices in
the system, a good value for num_ioservers to use is one or two more
than the number of physical devices on which the database is established.
Page Cleans for Interval The number
of times a page cleaner was invoked for the database (for any reason)
during the monitoring interval. Use the returned value to determine
how often pages are written to disk by the page cleaners of this database.
If this value increases over time, you can define more page cleaners.
The number of page cleaners is determined by the number of I/O cleaners
configured. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Pages per Prefetch for Interval The
number of data pages read per prefetch request for the database during
the monitoring interval. Use the returned value to determine
the amount of asynchronous input and output in each interaction with
the prefetcher. An excessively low returned value when compared to
normal operating levels indicates that you need more input and output
servers. The more input and output servers that you have, the better
your query performance. The following value is also valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Package Cache Size The current value
in units of 4-KB pages of the package cache size. This value
controls the amount of application heap memory to be used for caching
static and dynamic SQL statements of a package. You must experiment
with the size of the package cache to find the optimal number for
this attribute. For example, you can use a smaller package cache size
if there is no increase in the number of package cache inserts when
you decrease the size of the cache. Decreasing the package cache size
frees up system resources for other work. However, increasing the
package cache size can improve overall system performance if it results
in a decrease of package cache inserts. The following value is also
valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Pool Hit Ratio Index Percent for Interval The
database index page hit ratio (as a percentage) for the buffer pool
during the monitoring interval. The index page hit ratio for
the buffer pool indicates the percentage of index page requests for
which the database manager did not need to load an index page from
disk to service. That is, the index page was already in the buffer
pool. The higher the returned value, the lower the frequency of disk
input and output, and the faster the performance. If the hit ratio
is low compared to normal operating levels, increasing the number
of buffer pool pages can improve performance.
Pool Hit Ratio Percent for Interval The
overall buffer pool hit ratio as a percentage for the database during
the monitoring interval. This hit ratio includes both index and
data page activity. The overall buffer pool hit ratio indicates the
percentage of page requests for which the database manager did not
need to load a page from disk to service. (That is, the page was already
in the buffer pool.) The greater the buffer pool hit ratio, the lower
the frequency of disk input and output. If the hit ratio is low compared
to normal operating levels, increasing the number of buffer pool pages
can improve performance. A ratio of zero indicates that pages needed
to be read for every request. For a large database, increasing the
buffer pool size can have a minimal effect on the buffer pool hit
ratio. Such a database can have so large a number of data pages that
the statistical chance of a hit is not increased by an increase of
the buffer pools. However, even though the data might be too large
to fit in the buffer pool, the entire index can fit. In this case,
you can refine buffer pool sizes until the overall buffer pool hit
ratio stops increasing, and then refine the buffer pool until the
buffer pool index hit ratio no longer increases.
Pool I/O per Second The rate (per
second) of buffer pool input and output operations for the database. Buffer
pool input and output includes all physical data and index pages that
go through the buffer pool when read or written. Use the returned
value to determine how efficient your data storage device is. A low
value indicates the presence of an input and output wait, in which
case you must move data to a different device. The following value
is 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 valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Pool Sync Index Reads The number
of pool index physical reads minus the pool async index reads. The
following value is 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 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 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 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 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
valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Prefetch Wait Time The time an application
spent waiting for an I/O server (prefetcher) to finish loading pages
into the buffer pool. This attribute can be used to experiment
with changing the number of I/O servers and the I/O server sizes.
The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Primary Log Used Percent The percentage
of total log space used by the primary log. Use the returned
value to help you evaluate the allocated amount of primary log space
and refine the log buffer size, log file size, and primary log configuration
parameters. The returned value is valid only if circular logging is
used.
Primary Log Used Top The maximum
bytes of primary logs used. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Restore Pending The RESTORE PENDING
status in the database during the last monitoring interval.
Rollback Rate for Interval The rate,
in rollbacks per second, at which unit-of-work rollbacks were attempted
during the monitoring interval. Unit-of-work rollbacks include
SQL ROLLBACK statements that are issued from applications and INTERNAL
ROLLBACKS that the database manager initiates. The following value
is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Secondary Log Used Percent The percentage
of maximum log space used by the secondary log. Use the returned
value to show the current dependency on secondary logs. Secondary
logs are used when you have circular logging (log retention off) and
the primary log files are full.
Select SQL Percent for Interval The
percentage of total SQL statements that were SQL SELECT statements
during the monitoring interval. Use the returned value to determine
the level of application activity and throughput for the database.
Sequential Detect The current value
of the sequential detection flag, which determines if the database
manager must perform sequential detection. The database manager
can monitor input and output operations. If sequential page reading
is occurring, the database manager can activate input and output prefetching.
This type of sequential prefetch is known as sequential detection.
If this configuration parameter is set to no
, prefetching
takes place only if the database manager determines that it is useful
(for example, in table sorts).
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.
Sort Heap The current value in units
of 4-KB pages of the sort heap size. This value is the maximum
amount of memory that can be allocated as sort heap for each sort
within a database. The sort heap is the memory block where data is
sorted. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Sort Overflows Percent for Interval The
percentage of application sorts that overflowed during the monitoring
interval. An overflow occurs when a sort has run out of space
in the sort heap and requires disk space for temporary storage. If
this percentage is high, you might want to adjust the database configuration
by increasing the value of the SORTHEAP configuration parameter. The
value format is an integer.
SQL Stmts Rate for Interval The
rate, in issued SQL statements per second, at which SQL statements
that run during the monitoring interval. The value format is
an integer. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
System Tablespaces The number
of SMS tablespaces in the database. Use the returned value to
evaluate the use of SMS tablespaces and their effects on performance.
Table data that is read from disk is available in the database buffer
pool. Sometimes a data page is freed from the buffer pool before it
is used. For SMS tablespaces, when the database manager requests that
data page from the file system, the data page might still be in the
cache of the file system. Having the page in the cache saves an input
and output operation that would otherwise have been required. (For
more information, see the DB2 administration
documentation for the version of DB2 that
you are using.) If you have many SMS tablespaces, you can increase
the size of the file system cache to take advantage of this extra
buffering. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Tables The number of tables in
the database. Use this attribute to track database growth due
to an increased number of tables over a period of time. The following
value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Tablespaces The number of tablespaces
in the database. Use this attribute to track database growth
over a period of time. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Tablespaces Long Data The number
of tablespaces that store LONG data in the database. Use this
attribute to track database growth over a period of time. LONG data
can take up a large amount of space in a database. The following value
is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Total Direct I/O Time The total
time in milliseconds applied to direct reads and writes for the database. The
returned value indicates the amount of time that the database performs
direct reads and writes. A high returned value compared to normal
operating levels can indicate the presence of an input and output
conflict. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Total Log Used The total log space
used in bytes. 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 |
Total Pool Phys I/O The total time
in milliseconds applied to physical I/O for the database. A high
returned value (as compared to the total number of physical buffer
pool input and output operations) can indicate the presence of an
input and output wait, which in turn can indicate that you must move
data to a different device. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Total Pool Phys Read The total
time in milliseconds applied to processing read requests that caused
data or index pages to be physically read from disk to the buffer
pool for the database. The value is derived through this formula:
pool
data p reads + pool index p reads
. The returned value is
used to calculate the average pool read time. This average can indicate
the presence of an input and output wait, which in turn can indicate
that you must move data to a different device. The following value
is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Total Pool Phys Write The total
time in milliseconds for buffer pool physical writes (including asynchronous
writes). The value is derived through this formula:
pool
data writes + pool index writes
. The returned value is used
to calculate the average pool write time. This average can indicate
the presence of an input and output wait, which in turn can indicate
that you must move data to a different device. The following value
is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Total Sync I/O The total synchronous
input and output. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Total Sync I/O Time The total
time in milliseconds applied to processing requests for synchronous
reads or writes for the database. The returned value is the sum
of the returned values from the average pool write time (ms) and average
pool read time (ms). This time is the amount of time that database
agents spend doing synchronous reads and writes. The following value
is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Triggers The number of triggers
defined in the database. Use this attribute to track the use
of triggers in the database. There are benefits to using triggers,
including faster application development, easier maintenance, and
global enforcement of business rules. For more information, see the DB2 administration documentation
for the version of DB2 that
you are using. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
UID SQL Percent for Interval The
percentage of total SQL statements that were SQL UPDATE, INSERT, and
DELETE statements during the monitoring interval. Use the returned
value to determine the level of database data change activity.
User Indexes The number of indexes
created by users in the database. Indexes created by SYSIBM are
not counted. Use this to track the use of indexes in the database.
The use of indexes can improve performance; for example, faster sorting
of data. However, indexes can also have adverse effects on performance;
for example, each INSERT or DELETE operation performed on a table
requires additional updating of each index on that table. For a discussion
of this topic, see the DB2 administration
documentation for the version of DB2 that
you are using. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |
Views The number of views in the
database. Use this attribute to track the use of views in the
database. Views can be created to limit access to sensitive data,
while allowing more general access to other data. This provides flexibility
in the way your programs and end-user queries can look at the table
data. The following value is valid:
External value |
Internal value |
Value Exceeds Maximum |
9223372036854775807 |