Location of Views
All of the views for performance monitoring data are located in the schema ibm_dsm_views. The contents of each view is documented as the following.
-
Repository View BOTTLE_ related (include BOTTLE_CPU, BOTTLE_DIRECT_READS, ......)
-
Repository View MON_GET_TABLESPACE
-
Repository View MON_GET_TABLESPACE_DPF
-
Repository View MON_GET_TRANSACTION_LOG
-
Repository View MON_GET_TRANSACTION_LOG_DPF
-
Repository View SESSIONS
-
Repository View DB_SUMMARY_SESSIONS
-
Repository View DB_SUMMARY_SESSIONS_DPF
-
Repository View CF_INFO
-
Repository View GLOBAL_BUFFER_POOL
-
Repository View CLUSTER_CACHE_PERF
-
Repository View GLOBAL_BUFFER_POOL
-
Repository View HADR_ROLE_SUMMARY
View Contents
All views will be structured with the following columns:
- dbconn_int the internal ID for connection name
- dbconn_id the string version of the connection name
- prev_collected timestamp of the start of the collection interval
- collected timestamp of the end of the collection interval
- delta_msec number of milliseconds in the collection interval
- (row ID columns) e.g. buffer pool name, member, application handle
- Metric columns
The row ID columns are the identifiers for whatever object the monitoring query measures. For example, application handle or buffer pool name.
Each row presented by a view represents monitoring data for a monitored object such as a buffer pool or application handle that was accumulated during a single collection interval. Read more about collection intervals here <link TBD>. By default, collection intervals are one hour. Note that repository pruning works by merging collection intervals, so older data will have longer collection intervals. Collection intervals that span data server restarts are filtered from the views.
In its raw form, most monitoring data from DB2 consists of counters that are reset to zero when the data server is activated and increase for as long as the data server remains activated. For example, the number of rows read in a workload since server activation. When creating a report, you almost always want to know how many rows were read over a particular period of time. Such values are referred to as deltas. The views compute deltas for you on all metrics where it makes sense. Some metrics, for example number of locks currently held, are more useful in their raw (counter) form. For metrics such as locks currently held, no delta value is presented. Which metrics the views present a delta for is documented where the contents of each view is described in the child topics.
Each metric that the views compute the delta for will show in the view as two columns. For example, in the view sessions there is a pair of columns rows_read and rows_read_delta. Each row of the view sessions represents activity by a particular connection to the database during one collection interval. If you want to report a rate, such as the rows per minute that a connection was reading, you can use a formula like the following:
60000 * rows_read_delta / delta_msec
The field delta_msec gives the length of the collection interval in milliseconds. You must multiply by an appropriate value to scale the result. In this example, the formula multiplies by 60,000 ms / minute to scale the result to rows read / minute.
Alternatively, if you are interested in the number of rows read by each connection since it was established, you can look at the column rows_read. The values in all the columns represent the state of the database at the end of the collection interval, available in the column collected.
Example Use Cases
Reporting on Resource Consumption
The performance monitoring data contains resource usage recorded at various levels of granularity. This can be useful as a basis for chargeback type reports. For reporting resource usage by database, the view throughput_all is a good starting point. Much of the same information is available in a more fine grained form in the view throughput_workload.
An example:
- Report on resource consumption by database Example - Resource Usage by Database