IBM DB2 for LUW (remote monitoring)IBM DB2 for LUW (remote monitoring)
Remotely collect monitoring metrics from your DB2 databases.
Extension
- Product information
- Release notes
Overview
Remotely monitor DB2 databases to complement information collected by OneAgent.
Get started
Activating the extension
- Activate Extension in the Hub by going to:
- Extensions → DB2 → Add to environment
- Add a new monitoring configuration for every DB2 instance you'd like to monitor
The monitoring configurations will be assigned to an ActiveGate group. Make sure the group selected contains ActiveGates that can reach the DB2 instances in that monitoring configuration.
Creating a DB2 user
The DB2 user must have read access to monitoring table functions and administrative views:
- EXECUTE privilege on the monitoring table fuctions:
- MON_GET_DATABASE
- MON_GET_INSTANCE
- MON_GET_TRANSACTION_LOG
- MON_GET_TABLESPACE
- MON_GET_CONNECTION
- MON_GET_HADR
- ENV_GET_SYSTEM_RESOURCES
- SELECT privilege on the following administrative views:
- ENV_INST_INFO
- MON_CURRENT_SQL
As an alternative to the above 'least-privilege' approach any 1 of the following authorities will provide the required access as well:
- DATAACCESS authority
- DBADM authority
- SQLADM authority
As part of the connection process of the SQL datasource, the user must also have the privilege to perform the SELECT opertation on the SYSIBM.SYSDUMMY1 catalog table.
If the database was created with the RESTRICTIVE option you will also need to provide the user with the USAGE privilege on an enabled workload. See here for details.
Details
Logs ingested
If you enable the 'top_current_sql' feature set, a query to the MON_CURRENT_SQL view will be made once every 5 minutes and the top 50 currently executing statements (based on elapsed time) will be ingested as log records. Note that currently executing means that a statement must be currently executing at the time this query is run for it to be included. This can give you insight into long running queries in your databases. These logs will be associated with the DB2 Database Instance entity.
Note that as this can result in large amounts of log records being ingested it should be used with caution.
Metrics collected
Review the feature set details to see which metrics are collected.
Calculated metrics
Calculated metrics (prefixed with 'func:') are calculated on the Dynatrace cluster side based on the metrics collected through the extension. Some calculated metrics require metrics from certain feature sets, so if you do not have all feature sets enabled you may encounter errors when attempting to chart them.
- func:db2.app_transactions (database) - Number of application transactions executed. Commits and rollbacks.
- func:db2.table_reads_per_returned_rows_ratio (database) - Cache usage during transaction reads.
- func:db2.lock_wait_per_transaction_ratio (database) - Lock wait time per transaction.
- func:db2.lock_fails_per_transaction_ratio (database) - Lock fails (deadlocks and lock timeouts) per transaction.
- func:db2.log_flush_time_per_transaction_ratio (database, transaction_log) - The time an agent waits for log records to be flushed to disk.
- func:db2.pool_index_page_hits (database) - Index pages usage.
- func:db2.pool_data_page_hits (database) - Data pages usage.
- func:db2.pool_xda_page_hits (database) - XML storage object (XDA) pages usage.
- func:db2.pool_physical_reads_per_transaction_ratio (database) - Pool physical reads per transaction.
- func:db2.pool_physical_writes_per_transaction_ratio (database) - Pool physical writes per transaction.
- func:db2.pool_dirty_steal_triggers_per_transaction_ratio (database) - Number of ‘dirty steal’ triggers per transaction.
- func:db2.sorts_per_transaction_ratio (database) - The amount of sort operations per transaction.
- func:db2.all_transactions.count (database) - All transactions including internally initiated commits and rollbacks.
- func:db2.response_time (database) - CPU and wait time divided by commits and rollbacks.
- func:db2.tbsp_page_utilization (tablespace) - Percentage of tablespace in use by tablespace.
- func:db2.total_log_utilization (transaction_log) - Percentage of transaction log space used.
Metric event
One metric event for alerting configuration is included: 'DB2 - Instance Unavailable'. Find this is Settings > Anomaly detection > Metric events and enable it to have availability events created on the database instance entity if no data is collected for 3 out of 5 minute intervals.
Compatibility information
IBM supported versions of IBM DB2 for Linux, UNIX, and Windows (LUW) running on AIX, HP-UX, Linux, Solaris, or Windows are supported. DB2 for IBM i is not supported.
Refer to the IBM documentation for details on DB2 end of support dates.
FAQ
What if I need to connect using SSL?
The DB2 data source supports SSL connections as of ActiveGate version 1.269. Monitoring configurations will need to be updated to enable SSL and connect to the SSL port.
Licensing
There is no charge for obtaining the extension, only for the data that the extension ingests. The details of license consumption will depend on which licensing model you are using. This will either be Dynatrace classic licensing or the Dynatrace Platform Subscription (DPS) model.
Metrics
License consumption is based on the number of metric data points ingested. The following formula will provide approximate annual data points ingested assuming all feature sets are enabled.
(9 + (49 * members) + (3 * member tablespaces)) * 60 minutes * 24 hours * 365 days data points per year
Classic licensing
In the classic licensing model, metric ingestion will consume Davis Data Units (DDUs) at the rate of .001 DDUs per metric data point.
Multiply the above formula for annual data points by .001 to estimate annual DDU usage.
Logs
If you use the 'top_current_sql' feature set you can ingest up to 50 log records per monitored instance every 5 minutes. Exact values will depend on currently executing queries at the time the extension runs.
License consumption is based on the volume of data (discussed here).
Classic licensing
In classic licensing DDUs are consumed at the rate of 0.0005 DDUs per record.
Extension content
Feature sets
Below is a complete list of the feature sets provided in this version. To ensure a good fit for your needs, individual feature sets can be activated and deactivated by your administrator during configuration.
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Availability | db2.availability | Availability based on ability of extension to connect to DB2 instance (100% reported on success, no data on failure). | Percent |
| CPU usage | db2.cpu_usage_total | The overall processor usage on this host including kernel processing time, expressed as a percentage. | Percent |
| Memory free | db2.memory_free | The total amount of physical memory on this host that is not allocated to a running process. | MegaByte |
| Memory total | db2.memory_total | The total amount of physical memory on this host. | MegaByte |
| Memory usage | db2.memory_usage | The total amount of physical memory on this host allocated to a running process as a percentage. | Percent |
| Database manager instance status | db2.db2_status | The current status of the instance of the database manager. | State |
| Member status | db2.db_status | The current status of the database member. | State |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Tablespace total pages | db2.tbsp_total_pages | Total number of pages in a table space. | Count |
| Tablespace used pages | db2.tbsp_used_pages | The total number of pages that are currently used (not free) in a table space. | Count |
| Tablespace state | db2.tbsp_state | The current state of a table space. | State |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Log write time | db2.log_write_time.count | The total elapsed time spent by the logger writing log data to the disk. | MilliSecond |
| Transaction log used | db2.total_log_used | The total amount of active log space currently used in the database. | Byte |
| Transaction log available | db2.total_log_available | The amount of active log space in the database that is not being used by uncommitted transactions. | Byte |
| In doubt transactions | db2.num_indoubt_transactions | The number of outstanding indoubt transactions in the database. | Count |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Active connections | db2.active_connections | Current active connections to database. | Count |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| HADR connect status | db2.hadr_connect_status | The current high availability disaster recovery (HADR) connection status of the database. | State |
| HADR state | db2.hadr_state | The current high availability disaster recovery (HADR) state of the database. | State |
| HADR log gap | db2.hadr_log_gap | The recent average of the gap between the PRIMARY_LOG_POS value and STANDBY_LOG_POS value (how far the standby is behind the primary). | Byte |
| Metric name | Metric key | Description | Unit |
|---|---|---|---|
| Apps currently connected | db2.appl_cur_cons | Indicates the number of applications that are currently connected to the database. | Count |
| Dynamic SQL statements | db2.dynamic_sql_stmts.count | The number of dynamic SQL statements that were attempted. | Count |
| Failed SQL statements | db2.failed_sql_stmts.count | The number of SQL statements that were attempted, but failed. | Count |
| Total app commits | db2.total_app_commits.count | Total number of commit statements issued by the client application. | Count |
| Internal commits | db2.int_commits.count | The total number of commits initiated internally by the database manager. | Count |
| App rollbacks | db2.total_app_rollbacks.count | Total number of rollback statements issued by the client application. | Count |
| Internal rollbacks | db2.int_rollbacks.count | The total number of rollbacks initiated internally by the database manager. | Count |
| CPU time | db2.total_cpu_time.count | The total amount of CPU time used while within the database system. Represents total of both user and system CPU time. | MicroSecond |
| Wait time | db2.total_wait_time.count | The total time spent waiting within the database server. | MicroSecond |
| Request time | db2.total_rqst_time.count | The total amount of time spent working on requests. | MilliSecond |
| Rows returned | db2.rows_returned.count | The number of rows that have been selected and returned to the application. | Count |
| Rows read | db2.rows_read.count | The number of rows read from the tables. | Count |
| Rows updated | db2.rows_updated.count | This is the number of row updates attempted. | Count |
| Rows deleted | db2.rows_deleted.count | This is the number of row deletions attempted. | Count |
| Rows inserted | db2.rows_inserted.count | The number of row insertions attempted. | Count |
| Lock wait time | db2.lock_wait_time.count | The total elapsed time spent waiting for locks. | MilliSecond |
| Deadlocks | db2.deadlocks.count | The total number of deadlocks that have occurred. | Count |
| Lock timeouts | db2.lock_timeouts.count | The number of times that a request to lock an object timed out instead of being granted. | Count |
| Buffer pool data writes | db2.pool_data_writes.count | The number of times a buffer pool data page was physically written to disk. | Count |
| Buffer pool index writes | db2.pool_index_writes.count | Indicates the number of times a buffer pool index page was physically written to disk. | Count |
| Buffer pool XDA writes | db2.pool_xda_writes.count | Indicates the number of times a buffer pool data page for an XML storage object (XDA) was physically written to disk. | Count |
| Local buffer pool index pages found | db2.pool_index_lbp_pages_found.count | Indicates the number of index pages which have been synchronously read by agents and asynchronously read by prefetchers from the local buffer pool (logical) for regular and large table spaces. | Count |
| Async local buffer pool index pages found | db2.pool_async_index_lbp_pages_found.count | The number of times an index page was present in the local buffer pool when a prefetcher attempted to access it. | Count |
| Buffer pool index logical reads | db2.pool_index_l_reads.count | Indicates the number of index pages which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces. | Count |
| Buffer pool temp index logical reads | db2.pool_temp_index_l_reads.count | Indicates the number of index pages which have been requested from the buffer pool (logical) for temporary table spaces. | Count |
| Local buffer pool data pages found | db2.pool_data_lbp_pages_found.count | Indicates the number of data pages which have been synchronously read by agents and asynchronously read by prefetchers from the local buffer pool (logical) for regular and large table spaces. | Count |
| Async local buffer pool data pages found | db2.pool_async_data_lbp_pages_found.count | The number of times a data page was present in the local buffer pool when a prefetcher attempted to access it. | Count |
| Buffer pool data logical reads | db2.pool_data_l_reads.count | Indicates the number of data pages which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces. | Count |
| Buffer pool temp data logical reads | db2.pool_temp_data_l_reads.count | Indicates the number of data pages which have been requested from the buffer pool (logical) for temporary table spaces. | Count |
| Local buffer pool XDA pages found | db2.pool_xda_lbp_pages_found.count | Indicates the number of data pages for an XML storage object (XDA) which have been synchronously read by agents and asynchronously read by prefetchers from the local buffer pool (logical) for regular and large table spaces. | Count |
| Async local buffer pool XDA pages found | db2.pool_async_xda_lbp_pages_found.count | The number of times a data page for an XML storage object (XDA) was requested by a prefetcher from and found in the local buffer pool. | Count |
| Buffer pool XDA logical reads | db2.pool_xda_l_reads.count | Indicates the number of data pages for XML storage objects (XDAs) which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces. | Count |
| Buffer pool temp XDA logical reads | db2.pool_temp_xda_l_reads.count | Indicates the number of pages for XML storage object (XDA) data which have been requested from the buffer pool (logical) for temporary table spaces. | Count |
| Buffer pool data physical reads | db2.pool_data_p_reads.count | Indicates the number of data pages which have been synchronously read by agents and asynchronously read by prefetchers from the table space containers (physical) for regular and large table spaces. | Count |
| Buffer pool index physical reads | db2.pool_index_p_reads.count | Indicates the number of index pages which have been synchronously read by agents and asynchronously read by prefetchers from the table space containers (physical) for regular and large table spaces. | Count |
| Buffer pool XDA physical reads | db2.pool_xda_p_reads.count | Indicates the number of data pages for XML storage objects (XDAs) which have been synchronously read by agents and asynchronously read by prefetchers from the table space containers (physical) for regular and large table spaces. | Count |
| Buffer pool temp data physical reads | db2.pool_temp_data_p_reads.count | Indicates the number of data pages read in from the table space containers (physical) for temporary table spaces. | Count |
| Buffer pool temp index physical reads | db2.pool_temp_index_p_reads.count | Indicates the number of index pages read in from the table space containers (physical) for temporary table spaces. | Count |
| Buffer pool temp XDA physical reads | db2.pool_temp_xda_p_reads.count | Indicates the number of pages for XML storage object (XDA) data read in from the table space containers (physical) for temporary table spaces. | Count |
| Buffer pool victim page cleaners triggered | db2.pool_drty_pg_steal_clns.count | The number of times a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database. | Count |
| Total sorts | db2.total_sorts.count | The total number of sorts that have been executed. | Count |
| Time since backup | db2.time_since_backup | Time since last backup was completed | Minute |
| Backup time | db2.backup_time.count | The elapsed time that was spent doing online backups | MilliSecond |
| Online backups | db2.total_backups | Total number of online backups | Count |
Full version history
Full version history
- Corrects source entity metadata for DB2 Host entity
- Will correct issues where DB2 host metrics were not showing up in management zones
Full version history
- fix issue that would prevent HADR metrics from displaying on the DB2 Instance Unified Analysis screen
Full version history
Minimum ActiveGate version required: 1.260
- Introduces the first 'query-level' visibility with 'the top_current_sql' feature set. Enabling this will result in a query to the MON_CURRENT_SQL view and ingestion of log records for the top 50 currently executing statements (based on elapsed time) once every 5 minutes. As this may result in large amounts of additional data ingestion be sure to use with caution.
- Includes backup metrics in the existing 'database' feature set:
- Time since last backup
- Backup time
- Number of online backups
Full version history
Minimum ActiveGate version required: 1.260
- 1.1.2 was a major change so be sure to review those earlier release notes as well if updating from an earlier version
- Fixes an issue with the 'source entity type' for several metrics (hadr and db2 status) that would cause issues with metrics missing from management zones
Full version history
Minimum ActiveGate version required: 1.260
Note: this version has changes to the tables and views that are queried. Review the latest requirements on the Hub page for details on what permissions are needed prior to updating
- Connection metrics now in dedicated feature set
- Be sure to enable this when updating if you want connection metrics
- HADR metrics and properties are now collected
- HADR feature set must be enabled
- Tablespaces now have their own entity type rather than having tablespace metrics associated with the member entity
- If you have rules such as metric events for tablespace metrics that were filtered by the member entity be sure to review if you need to update these
- The new tablespace entity has various new properties that can be used such as their state and whether auto resize is enabled
- New in-doubt transaction metric collected in transaction_log feature set
- Basic host resource metrics (CPU and memory) are now collected
- The db2.metadata metric is no longer used/needed
- This has been consolidated into the host resources query
- DB2 status is now reported
- Fix to instance level metric metadata that would have prevented management zone filtering from working for these metrics
- Various screen fixes and adjustments
Full version history
- Collects active connection metric
- Requires enabling the 'connections' feature set
Full version history
- Initial GA release
- Three feature sets:
- Database
- Transaction logs
- Tablespace
- UI Screens for DB2 entities
- Overview dashboard
- 'Availability' metric event