DB2 Performance Template
Description
Monitors the Performance of the DB2 Database Instances.
Prerequisites
No prerequisite
Supported Metric
| Monitor Name | Metric Name | Metric Display Name | Unit | Description |
|---|---|---|---|---|
| DB2 Performance Monitor | db2.active.sorts | DB2 Active Sorts | NULL | The number of sorts in the database that currently have a sort heap allocated. |
| db2.app.sec.inserts | DB2 SQL Section Inserts | NULL | Number of inserts of SQL sections by an application from its shared SQL work space per second. | |
| db2.app.sec.lookups | DB2 SQL Section Look-ups | NULL | Number of look-ups of SQL sections by an application from its shared SQL work space per second. | |
| db2.avg.cpu.time | DB2 Avg CPU Time | ms | Average amount of CPU time,in microseconds,used by all external requests that completed successfully. It represents the total of both user and system CPU time. | |
| db2.avg.direct.read.time | DB2 Avg Direct Read Time | ms | The Avg Read time is direct_read_time / direct_reads. A high average time may indicate an I/O conflict. | |
| db2.avg.direct.write.time | DB2 Avg Direct Write Time | ms | The Avg Write time is direct_write_time / direct_writes. A high average time may indicate an I/O conflict. | |
| db2.avg.lock.escals | DB2 Avg Lock Escalations | NULL | The average number of times that locks have been escalated from several row locks to a table lock per coordinator activities (successful and aborted). | |
| db2.avg.lock.timeouts | DB2 Avg Lock Timeouts | NULL | The average number of times that a request to lock an object timed out per coordinator activities (successful and aborted). | |
| db2.avg.lockwait.time | DB2 Avg LockWait Time | ms | The Avg write time is LOCK_WAIT_TIME / LOCK_WAITS at Database level. High wait time can mean that the applications are degrading performance. | |
| db2.catalog.cache.hit.ratio | DB2 Catalog Cache Hit Ratio | % | Catalog cache hit ratio,which equals: 1-(cat_cache_inserts /cat_cache_lookups))*100. The hit ratio is a percentage indicating how well the catalog cache is helping to avoid actual accesses to the catalog on disk. A high ratio indicates it is successful in avoiding actual disk I/O accesses. | |
| db2.catalog.cache.inserts | DB2 Catalog Cache Inserts | NULL | Number of times that a requested section was not available for use and had to be loaded into the package cache per second. | |
| db2.catalog.cache.lookups | DB2 Catalog Cache Lookups | NULL | Number of times that an application looked for a section or package in the package cache per second. | |
| db2.catalog.cache.overflows | DB2 Catalog Cache Overflows | NULL | Number of times that the package cache overflowed the bounds of its allocated memory per second. | |
| db2.commit.sql.stmts | DB2 Commit SQL Stmts | NULL | Number of commit SQL statements. | |
| db2.connected.apps | DB2 Connected Applications | NULL | Number of applications that are currently connected to the database. | |
| db2.database.status | DB2 Database Staus | NULL | Database status. 0 - Active 1 - QUIESCE_PEND 2 - QUIESCED 3 - ROLLFWD 4 - ACTIVE_STANDBY 5 - STANDBY | |
| db2.deadlocks.detected | DB2 Deadlocks | NULL | Total number of deadlocks that have occurred. | |
| db2.direct.read.requests | DB2 Direct Read Reqs | NULL | Number of requests to perform a direct read of one of more sectors of data. | |
| db2.direct.reads | DB2 Direct Reads | NULL | The number of read operations that do not use the buffer pool. | |
| db2.direct.write.requests | DB2 Direct Write Reqs | NULL | Number of requests to perform a direct write of one or more sectors of data. | |
| db2.direct.writes | DB2 Direct Writes | NULL | Number of write operations that do not use the buffer pool. | |
| db2.dirty.page.steal.clns | DB2 Dirty Page Steal Clns | NULL | The number of times a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database. | |
| db2.dynamic.sql.stmts | DB2 Dyanamic SQL Stmts | NULL | Number of Dyanamic SQL statements. | |
| db2.failed.sql.stmts | DB2 Failed SQL Stmts | NULL | Number of failed SQL statements. | |
| db2.io.log.reads | DB2 Log Read Requests | NULL | The number of I/O requests issued by the logger for reading log data from the disk. | |
| db2.io.log.writes | DB2 Log Write Requests | NULL | The number of I/O requests issued by the logger for writing log data to the disk. | |
| db2.lock.escals | DB2 Lock Escalations | NULL | The number of times that locks have been escalated from several row locks to a table lock. | |
| db2.lock.timeouts | DB2 Lock Timeouts | NULL | The number of times that a request to lock an object timed out instead of being granted. | |
| db2.log.page.reads | DB2 LOG Page Reads | NULL | Number of log pages read from disk by the logger per second. | |
| db2.log.page.writes | DB2 LOG Page Writes | NULL | Number of log pages write from disk by the logger per second. | |
| db2.maximum.connections | DB2 Maximum Connections | NULL | The highest number of simultaneous connections to the database since the database was activated. | |
| db2.overall.agent.wait.time.percent | DB2 Overall Agent Wait Time Percent | % | The percentage of time spent waiting within the DB2 database server that was spent by an application queued to wait for an agent under concentrator configurations. | |
| db2.overall.avg.rows.read.per.returned | DB2 Overall Avg Rows Read Per Returned | NULL | The average number of rows read from the table per rows returned to the application. | |
| db2.overall.bufferpool.hit.ratio | DB2 Overall Buffer Pool Hit Ratio | % | The percentage of time that the database manager did not need to load a page from disk to service a data request | |
| db2.overall.io.wait.time.percent | DB2 Overall IO Wait Time Percent | % | The percentage of the time spent waiting within the DB2 database server that was due to I/O operations | |
| db2.overall.lock.wait.time.percent | DB2 Overall IO Wait Time Percent | % | The percentage of time spent waiting within the DB2 database server that was spent waiting on locks. | |
| db2.pkg.cache.hit.ratio | DB2 Package Cache Hit Ratio | % | Package cache hit ratio,which equals: 1-(pkg_cache_inserts /pkg_cache_lookups))*100 The hit ratio is a percentage indicating how well the package cache is helping to avoid reloading packages and sections for static SQL from the system catalogs as well as helping to avoid recompiling dynamic SQL statements. A high ratio indicates it is successful in avoiding these activities. | |
| db2.pkg.cache.inserts | DB2 Package Cache Inserts | NULL | Number of times that a requested section was not available for use and had to be loaded into the package cache per second. | |
| db2.pkg.cache.lookups | DB2 Package Cache Lookups | NULL | Number of times that an application looked for a section or package in the package cache per second. | |
| db2.pkg.cache.overflows | DB2 Package Cache Overflows | NULL | Number of times that the package cache overflowed the bounds of its allocated memory per second. | |
| db2.pool.victim.buffers.count | DB2 Pool Victim Buffers | NULL | Number of times an agent did not have a preselected victim buffer available. | |
| db2.rollback.sql.stmts | DB2 Rollback SQL Stmts | NULL | Number of Rollback SQL statements. | |
| db2.rows.deleted | DB2 Deleted Rows | NULL | ||
| db2.rows.inserted | DB2 Inserted Rows | NULL | Number of Rows Inserted per second. | |
| db2.rows.selected | DB2 Selected Rows | NULL | Number of Rows Selected per second. | |
| db2.rows.updated | DB2 Updated Rows | NULL | Number of Rows Updated per second. | |
| db2.running.apps | DB2 Running Applications | NULL | Number of applications that are currently connected to the database,and for which the database manager is currently processing a request. | |
| db2.secondary.connections | DB2 Secondary Connections | NULL | Number of connections made by a sub agent to the database at the node. | |
| db2.select.sql.stmts | DB2 Select SQL Stmts | NULL | Number of Select SQL statements | |
| db2.sort.overflows | DB2 Sort Overflows | NULL | The total number of sorts that ran out of sort heap and may have required disk space for temporary storage. | |
| db2.total.app.commits | DB2 Total Application Commits | NULL | Total number of commit statements issued by the client application. | |
| db2.total.concurrent.connections | DB2 Total Connections | NULL | Number of connections to the database since the first connect,activate,or last reset (coordinator agents). | |
| db2.total.log.available | DB2 Available LOG Size | KB | Amount of active log space in the database that is not being used by uncommitted transactions in Kilobytes. | |
| db2.total.log.used | DB2 LOG Space Used | KB | Amount of active log space currently used in the database in Kilobytes. |
DB2 BufferPool and TableSpace Statistics
Description
Monitors the Individual Buffer-Pool and Table-Space Statistics.
Prerequisites
No prerequisite
Supported Metric
| Monitor Name | Metric Name | Metric Display Name | Unit | Description |
|---|---|---|---|---|
| DB2 BufferPool and TableSpace Statistics | db2.bufferpool.avg.physical.read.time | DB2 Buffer Pool Avg Physical Read Time | ms | Average time,in milliseconds,spent reading pages from the table space containers (physical) for all types of table spaces. |
| db2.bufferpool.avg.write.time | DB2 Buffer Pool Avg Write Time | ms | Average time,in milliseconds,spent physically writing pages from the buffer pool to disk. | |
| db2.bufferpool.data.hit.ratio | DB2 Buffer Pool Data Hit Ratio | % | The percentage of time that the database manager did not need to load a page from disk to service a data page request | |
| db2.bufferpool.index.hit.ratio | DB2 Buffer Pool Index Hit Ratio | % | The percentage of time that the database manager did not need to load a page from disk to service an index data page request | |
| db2.bufferpool.prefetch.ratio | DB2 Buffer Pool Prefetch Ratio | % | Percentage of pages read asynchronously (with prefetching). If many applications are reading data synchronously without prefetching,your system might not be tuned optimally. | |
| db2.bufferpool.total.physical.reads | DB2 Buffer Pool Total Physical Reads | NULL | Indicates the number of data pages,index pages,and data pages for XML storage objects (XDAs) read from the table space containers (physical) for temporary as well as regular and large table spaces. | |
| db2.bufferpool.total.writes | DB2 Buffer Pool Total Writes | NULL | The number of times a data,index,or data page for an XML storage object (XDA) was physically written to disk. | |
| db2.tablespace.usable.space | DB2 TableSpace Usable space | KB | The total usable size of the table space,in kilobytes. | |
| db2.tablespace.utilization | DB2 TableSpace Utilization | % | The utilization of the table space as a percentage. | |
| db2.total.tablespace.size | DB2 Total TableSpace Size | KB | The total size of the table space in kilobytes. | |
| db2.tablespace.state | DB2 TableSpace State | NULL | The current state of a table space. NORMAL - 0 BACKUP_IN_PROGRESS - 1 BACKUP_PENDING - 2 DELETE_PENDING - 3 DISABLE_PENDING - 4 DROP_PENDING - 5 LOAD_IN_PROGRESS - 6 LOAD_PENDING - 7 MOVE_IN_PROGRESS - 8 OFFLINE - 9 PSTAT_CREATION - 10 PSTAT_DELETION - 11 QUIESCED_EXCLUSIVE - 12 QUIESCED_SHARE - 13 QUIESCED_UPDATE - 14 REBAL_IN_PROGRESS - 15 REDIST_IN_PROGRESS - 16 REORG_IN_PROGRESS - 17 RESTORE_IN_PROGRESS - 18 RESTORE_PENDING - 19 ROLLFORWARD_IN_PROGRESS - 20 ROLLFORWARD_PENDING - 21 STORDEF_ALLOWED - 22 STORDEF_CHANGED - 23 STORDEF_FINAL_VERSION - 24 STORDEF_PENDING - 25 SUSPEND_WRITE - 26 |