240 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
6.1.1 Creating a database connection
Use the Databases dashboard in Optim Performance Manager to Add a
database connection. Figure 6-1 shows a standard database connection where
the DB2 instance user ID and password are used to create the database
connection.
Figure 6-1 Optim Performance Manager Database Connection page
6.1.2 Configuring database monitoring settings
In the Databases dashboard, select the database connection created and click
Configure Monitoring to start the Edit Configuration Monitoring wizard. This
wizard will guide you through the configuration steps.
Chapter 6. Managing complex query workloads in an operational warehouse environment 241
Metrics are collected for different monitoring profiles such as SQL Statements
and Connections, and Locking. Predefined templates for different database
environments, including Business Intelligence, are available and these represent
a collection of appropriate monitoring profiles and settings.
For an operational data warehouse, it is best to use one of the following
predefined system templates:
BI production system with low overhead
This system template is sufficient for normal operating conditions because it
minimizes the effect on the database being monitored.
BI production system with all details
This system template collects all details recommended for a BI production
system. It is suggested that you use this template when you are
troubleshooting a specific issue because it collects a significant amount of
data, which can have an effect on the monitored database and on the size of
the Optim Performance Manager repository database.
Figure 6-2 on page 242 shows Step 1 of the wizard where the “BI production
system with low overhead” system template has been chosen. The Activate
242 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
monitoring check box is automatically checked to enable Optim Performance
Manager to collect and process monitoring data for the database.
Figure 6-2 Using the predefined system templates to monitor a database
Step 2 of the Edit Monitoring Configuration wizard allows you to set the collection
sampling interval and choose the individual monitoring profiles for which to
collect data.
Important: It is useful to manually add the Locking and Workload Manager
monitoring profiles during this step because they are not included in this
template.
Chapter 6. Managing complex query workloads in an operational warehouse environment 243
Figure 6-3 shows the default settings determined when you choose the “BI
production system with low overhead” template.
Figure 6-3 Monitoring profile settings for selected system template
Figure 6-4 on page 244 shows the Retention times and sampling intervals dialog
box called by clicking Edit in Step 2 of the Edit Monitoring Configuration wizard.
Use of the default settings shown in this figure enables data to be collected from
244 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
the monitored database every minute, and aggregated for up to two years before
being automatically removed.
Figure 6-4 Retention times and sampling intervals for performance metrics
The “Sampling rate in minutes” configuration parameter determines how often
data is collected through DB2 in-memory metrics and snapshot table functions.
The “Aggregation level” determines when data is written to the Optim
Performance Manager repository database to support the historical analysis of
data. When data ages, it is aggregated into a table that reflects the time period of
the aggregation. The value for aggregation level one always matches the value
of the sampling rate.
The “Storage period” determines the retention period for aggregated data. When
data ages past aggregation level four, it is removed.
The sampling rate for Workload Manager and Extended Insight is managed
separately by clicking the Edit icon beside each monitoring profile on Step 2.
The sampling rate for Health monitoring is managed separately through the
Health monitor dashboard.
Get Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
