Database01 (KUD_DB2_Database01) attributes

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