Monitoring Information metrics capture the monitoring information for the database, including general monitoring information, monitored values of the Agent, and monitored values of the database. Monitoring Information metrics consist of the following categories:
The metrics in this category return information about Agents from an application snapshot.
Default Collection Interval — Every 15 minutes
Table 2-17 Agent Monitoring Metrics
| Metric | Description and User Action |
|---|---|
|
Agent Cpu Utilization (%) |
Total CPU utilization, which is equal to: Agent_total_cpu_time-_Agent_total_cpu_time )/1000)/__interval)*100 |
|
Agent Identifier |
Unique ID for each Agent. |
|
Application Average Lock Wait Time (ms) |
The average waiting time for locks, which equals: lock_wait_time / lock_wait If the average lock wait time is high, you should look for applications that hold many locks, or have lock escalations, with a focus on tuning your applications to improve concurrency, if appropriate. If escalations are causing a high average lock wait time, the values of one or both of the locklist and maxlocks configuration parameters may be too low. |
|
Application Commit SQL Statements Rate |
Commit SQL statements reading rate, which equals: commit_sql_stmts - _commit_sql_stmts/ __interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
Application Dynamic SQL Statements Rate |
Dynamic SQL statements reading rate, which equals: dynamic_sql_stmts - _dynamic_sql_stmts/ __interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
Application Failed SQL Statements Rate |
Failed SQL statements reading rate, which equals: failed_sql_stmts - _failed_sql_stmts/ __interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
Application Identifier |
Unique ID for each application. |
|
Application Name |
Name of the application. |
|
Application Priority |
Priority of Agents working for this application. |
|
Application Rollback SQL Statements Rate |
Rollback SQL statements reading rate, which equals: rollback_sql_stmts - _rollback_sql_stmts/ __interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
Application Row Reading Rate |
Rows reading rate in the last interval, which equals: rows_read - rows_read)/__interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
Application Row Writing Rate |
Rows writing rate in the last interval, which equals: rows_written - rows_written)/__interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
Application Static SQL Statements Rate |
Static SQL statements reading rate, which equals: static_sql_stmts - _static_sql_stmts/ __interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
Application Status |
Status of the application corresponding to the value of application_status_raw. |
|
Authorization ID |
Authorization ID of the user who invoked the application being monitored. On a DDCS gateway node, this is the user's authorization ID on the host. |
|
Client Database Alias |
Alias of the database provided by the application to connect to the database. |
|
Client Name |
Name of the Client for the database. |
|
Client Node Number |
client_nname in the database manager configuration file at the client node. |
|
Commit SQL Statements |
Number of commit SQL statements. |
|
Coordinator Agent Process Identifier |
Process ID (UNIX systems) or thread ID (Windows systems) of the coordinator Agent for the application. |
|
Dynamic SQL Statements |
Number of dynamic SQL statements. |
|
Execution Identifier |
ID that the user specified when logging in to the operating system. This ID is distinct from the Authorization ID, which the user specifies when connecting to the database. |
|
Failed SQL Statements |
Number of failed SQL statements. |
|
Host CPU Usage Per Sec (%) |
Ratio of CPU time utilized in the last interval, which equals: elapsed_exec_time_ms - elapsed_exec_time_ms / __interval You can set the required value for the warning and critical thresholds to monitor any adverse conditions. |
|
IO Waits |
IO wait time. |
|
Number of Applications Waiting on Locks |
Total number of applications that are currently waiting on locks. |
|
Number of Deadlocks |
Total number of deadlocks that have occurred. |
|
Number of Exclusive Lock Escalations |
Number of exclusive lock escalations. |
|
Number of Lock Escalations |
Number of lock escalations. |
|
Number of Locks Held |
Number of locks currently held by an application. |
|
Number of Lock Timeouts |
Number of lock timeouts for the application. |
|
Number of Lock Waits |
Number of times the application waited for locks. |
|
Number of Rows Read |
Total number of rows read. |
|
Number of Rows Written |
Total number of rows written. |
|
Number of Sorts |
Number of sorts performed by the statement. |
|
Rollback SQL Statements |
Number of rollback SQL statements. |
|
Statement Average Sort Time (ms) |
Average sort time per sort for the statement, which equals: stmt_sorts/total_sort_time |
|
Statement Rows Read |
Total number of statement rows read |
|
Statement Rows Written |
Total number of statement rows written. |
|
Statement Operation |
Statement operation value. |
|
Statement Start Time |
Start time of the statement. |
|
Statement Text |
Whole query or the statement executed. |
|
Statement Total Sort Time (ms) |
Total time used for statement sorting. |
|
Statement Total Sorts |
Total number of sorts. |
|
Statement Type |
Number of requests to perform a direct write of one or more sectors of data. |
|
Static SQL Statements |
Number of static SQL statements. |
|
System-CPU Time Used by Agent (ms) |
Total system time used by DBM for processing by the Management Agent. |
|
System-CPU Time Used by Statement (ms) |
Total system time used by DBM for processing by the statement. |
|
Total CPU Time Used Statement (ms) |
Total CPU time, which equals: stmt_usr_cpu_time + stmt_sys_cpu_time |
|
Total CPU Time Used by Agent (ms) |
Total CPU time, which equals: agent_usr_cpu_time + agent_sys_cpu_time |
|
Total Elapsed Execution Time (ms) |
Sum of the host execution times (in milliseconds) for all the statements that were executed for a particular application. |
|
Total Idle Time (ms) |
Time spent idle for an application. |
|
Total Lock Wait Time (ms) |
Total time the application waited for locks. |
|
Total Sort Time |
Total time used for sorting. |
|
User-CPU Time Used by Agent (ms) |
Total user time used by DBM for processing by the Agent. |
|
User-CPU Time Used by Statement (ms) |
Total user time used by DBM for processing by a Statement. |
|
Statement Hash |
Hash of the SQL statement text. |
The metrics in this category return snapshot information from the database and detail_log logical data groups.
Default Collection Interval — Every 15 minutes
Table 2-18 Database Monitoring Metrics
| Metric | Description |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Allocated Database Size (Bytes) |
Capacity of the database. (Not available in partitioned databases.) |
|
Commit SQL Statements |
Number of applications waiting for a lock on an object in the database. Number of commit SQL statements. |
|
Database Average Lock Wait Time (ms) |
Average waiting time for locks, which equals: lock_wait_time/lock_wait High wait for an application can mean that the application is degrading performance. |
|
Number of Applications Waiting on Locks |
Number of applications waiting for a lock on an object in the database. |
|
Database Commit SQL Statements Rate |
Commit SQL statement reading rate, which equals: commit_sql_stmts - _commit_sql_stmts/ __interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Database Connection Time |
Last time the database was connected. |
|
Database Dynamic SQL Statements Rate |
Dynamic SQL statement reading rate, which equals: dynamic_sql_stmts - _dynamic_sql_stmts/ __interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Database Failed SQL Statements Rate |
Failed SQL statement reading rate, which equals: failed_sql_stmts - _failed_sql_stmts/ __interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Logical Location of Database |
Location of the database.
|
|
Physical Disk Location of Database |
Physical disk location of the database. |
|
Database Rollback SQL Statements Rate |
Rollback SQL statement reading rate, which equals: rollback_sql_stmts - _rollback_sql_stmts/ __interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Current Database Size (Bytes) |
Size of the database in bytes. |
|
Database Space Utilization (%) |
Total percentage space utilization in the database, which equals: (db_size/db_capacity) *100 |
|
Database Static SQL Statements Rate |
Static SQL statement reading rate, which equals: static_sql_stmts - _static_sql_stmts/ __interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Database Status |
Status of the database. |
|
Database Deadlock Rate (per sec) |
Rate of deadlocks, which equals: (deadlocks-_deadlocks)/_interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Dynamic SQL Statements |
Number of dynamic SQL statements. |
|
Database Exclusive Lock Escalation Rate (per sec) |
Rate of exclusive lock escalations, which equals: (x_lock_escals-_x_lock_escals)/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Number of Exclusive Lock Escalations |
Number of exclusive lock escalations. |
|
Failed SQL Statements |
Number of failed SQL statements. |
|
Server Instance Name |
Name of the database manager instance for which the snapshot was taken. |
|
Database Internal Deadlock Rollback Rate (per sec) |
Rate of internal deadlock rollbacks, which equals: (int_deadlock_rollbacks-_int_deadlock_rollbacks) /__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Number of Lock Escalations |
Number of lock escalations. |
|
Database Lock Escalation Rate (per sec) |
Rate of lock escalations, which equals: (lock_escals-_lock_escals)/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Number of Lock Timeouts |
Number of lock timeouts for the application. |
|
Database Lock Timeouts Rate (per sec) |
Rate of lock timeouts. The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Number of Locks Held |
Number of locks currently held by an application. |
|
Number of Active Current Connections |
Total number of active connections in the database. |
|
Number of Current Connections |
Total number of currently connected applications. |
|
Number of Deadlocks |
Total number of deadlocks that have occurred. |
|
Number of Internal Rollbacks |
Total number of internal deadlock rollbacks. |
|
Number of Lock Waits |
Number of times the application waited for locks. |
|
Database Percentage of Applications Wating on Locks (%) |
Ratio of applications waiting on locks, which equals: (locks_waiting /appls_cur_cons)*100 If a large number of applications are waiting on locks, this indicates a possible performance degradation. |
|
Rollback SQL Statements |
Number of rollback SQL statements. |
|
Static SQL Statements |
Number of static SQL statements. |
|
Total Lock Wait Time (ms) |
Total time the application waited for locks. |
|
Lock List in Use |
Total lock list memory in use. |
|
Last Reset |
Time DB2 was last reset. |
The metrics in this category provide information about the last database backup.
Default Collection Interval — Every 2 hours
Table 2-19 Database Backup Monitoring Metrics
| Metric | Description |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Server Instance Name |
Name of the database manager instance for which the snapshot was taken. |
|
Last Backup Timestamp |
Last backup of the database. |
|
Days Since Last Backup |
Amount of days since the last database backup. |
The metrics in this category provide basic information about the status of the databases and some of the important configuration values.
Default Collection Interval — Every 15 minutes
Table 2-20 General Monitoring Metrics
| Metric | Description and User Action |
|---|---|
|
DB2 Start Time |
Date and time that the database manager was started using the db2start command. |
|
Registered Agents |
Number of Agents registered in the DBM instance that is being monitored. |
|
Communication Private Memory |
Amount of communication private memory. |
|
Allocated Sort Heap Memory |
Amount of memory allocated to sort heap. |
|
Sort Heap Threshold (in 4 KB Pages) |
Maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts. If the sort is a private sort, this parameter affects Agent private memory. If the sort is a shared sort, this parameter affects the database shared memory. Each sort has a separate sort heap that is allocated as needed by the Database Manager. This sort heap is the area where data is sorted. If directed by the optimizer, a smaller sort heap than the one specified by this metric is allocated using information provided by the optimizer. |
|
DB2 Status |
Status of the DB2 instance. If DB2 status equals:
|
|
Sort Heap Utilization (%) |
Sort heap utilization, which equals: sort_heap_allocated / sortheap_threshold *100 Increase the value of the sort heap threshold configuration parameter, sheapthres. |