This document describes the new features and enhancements in Version 5.x of IBM® InfoSphere® Optim™ Performance Manager for DB2® for Linux®, UNIX®, and Windows® and its modifications and fix packs.
For a more detailed overview of the key features in InfoSphere Optim Performance Manager Version 5.1, see the following article:
Real-time monitoring and more cool features in the new InfoSphere Optim Performance Manager V5.1 for DB2 for Linux, UNIX, and Windows
For information about new features in previous IBM Optim Performance Manager Version 4.1 releases, see the following techdoc:
InfoSphere Optim Performance Manager, Version 5.3.1
InfoSphere Optim Performance Manager Version 5.3.1 provides the following key enhancements:
- New metrics on Overview dashboard - Data Server Time breakdown chart:
- CF Wait
- Latch Wait
- Prefetch Wait
- Reclaim Wait
- Routine Proc
- Compile Proc
- New metrics on SQL Summary dashboard - Avg Wait Times chart:
- Cluster Caching Facility Wait Time
- Extended Latch Wait Time
- Prefetch Wait Time
- Additional support for BLU acceleration metrics
Custom reports enhancements
Enhanced the web console to create and defining both predefined as well as custom reports from the same interface. For more information, see
New alerts for monitoring HADR PureScale databases:
- HADR PureScale Primary Cluster Member disconnected
- HADR PureScale Primary Cluster disconnected
- HADR pureScale Cluster Role Switch Ready alert
- HADR pureScale Standby Cluster Receiver falling behind
- HADR pureScale Standby Cluster log falling behind
- HADR pureScale Standby Cluster member log receiver buffer utilization (only applies when spooling is enabled)
Performance and Scalability improvements
If you are monitoring DB2 V9.7 or later databases by using in memory metrics, this release of InfoSphere Optim Performance Manager includes several improvements around performance and scalability, including:
- The first fetch solution is used to optimize the first fetch of SQL data from the package cache of the monitored database.
- A unique statement hash is used to reduce the size of the metadata text and compilation tables.
- The statement concentrator code consolidates the SQL statements that have string literals. Smaller and optimized set of data is stored in the repository.
- A new baseline pruning algorithm use a new method to invoke the data pruning process periodically.
- Disk I/O consumption and operation is reduced and saves space in disk.
- Java heap consumption on Repository Server is less due to these improvements.
- The Java Virtual Machine Memory footprint has reduced since, the Java heap consumption on Repository Server is less due to these improvements and it does not need to keep more history baseline data in the memory.
- Response time on SQL and EI dashboard, with all above improvements enabled gets a big performance improvement. Latency in UI has significant improvement.
- Enhanced capability to monitor especially large SQL workloads has improved.
Improved integration of with InfoSphere Optim Query Workload Tuner
The SQL dashboard and Extended Insight dashboards now allow for enhanced tuning using Optim Query Workload Tuner. A new menu called Query Tuning is also available under the Open->Performance->Query Tuning .
- End to end single query tuning, including:
- Ad-hoc single query tuning
- Re-tune a query
- Persist and share tuning results
- Run recommended action DDLs for both single query and workload
- Explain statements with Visual Explain from SQL dashboard
- Enable a monitored database for single query tuning from the Database page
- Quick filters for tuning jobs
New tuning related functions include:
Enhancements to Extended Insight
- InfoSphere Optim Data Run Time Client Bulk Deploy - New package and process for configuring client applications for extended insight monitoring
- New Flag to turn extended insight monitoring on and off via the controllerURL. For more information, see "Configuring the InfoSphere Optim Performance Manager server to support extended insight monitoring in applications that are configured with the controllerURL" at http://www.ibm.com/support/docview.wss?uid=swg21682775
Support for DB2 Big SQL
- New support for the Data Server Type "Big SQL" on Database page
- Note: Certain features are unsupported for Big SQL databases, including: Single-query Tuning and Workload-Tuning
Support for DB2 z/OS V10 and V11
- Added support for new DB2z V10 metrics to detect RID list shortages allowing DBAs to tune the RID list pool appropriately.
- Added support for new DB2z V11 statement metrics for query transformation and CPU-query paralellism which are essential for a DBA to perform SQL tuning and analyze DB2z scalability
- Degree of Secondary Log Allocation ()
- Log space used by dirty pages (The amount of space that is being consumed by system logs to store dirty log events on the database)
- Enable and disable logging and set trace levels for PerformanceMonitorLogger without requiring a restart of the InfoSphere Optim Performance Manager server (from the Services page). For more information, see "Rolling logs and trace polling for client applications that are configured for extended insight monitoring" at http://www.ibm.com/support/docview.wss?uid=swg21682998.
- View and download PerformanceMonitorLogger log files (from the Logs page)
- Add new Repository Server Logs page to view and download Repository Server log files by monitor database. For more information, see "Retrieving the log files for the repository server application and then displaying them in the web console" at http://www.ibm.com/support/docview.wss?uid=swg21683042.
InfoSphere Optim Performance Manager, Version 5.3
InfoSphere Optim Performance Manager Version 5.3 provides the following key enhancements.
Embedded query tuning in the web console
You can now tune SQL queries using a subset of the InfoSphere Optim Query Workload Tuner features that are now embedded in the web console. You can also choose to continue using the Data Studio client to tune queries. Tuning features vary depending on your InfoSphere Query Workload Tuner licensing and which method you choose. For more details, see the following information center topic:
Tuning methods for SQL statements
The following built-in alerts have been added:
- File system utilization
- HADR standby database in replay-only window
- No HADR standby database connected
- Invalid log path
- Maximum log space exceeded
- Log archive failed
Support for DB2 v10.5 for Linux, UNIX, and Windows
DB2 v10.5 is supported as both a repository database and a monitored database. New performance metrics for column-organized tables and the explicit hierarchical locking (EHL) for IBM® DB2® pureScale® Feature (available in DB2 v10.5 FP1) are available on the following dashboards:
- SQL Statements
- Extended Insight
- Buffer Pool and I/O
InfoSphere Optim Performance Manager, Version 5.2
InfoSphere Optim Performance Manager Version 5.2 provides the following key enhancements.
Stored procedure monitoring for DB2 for Linux, UNIX, and Windows
You can now use the SQL Statements dashboard to analyze statements for stored procedures. Use the controls on the dashboard to see top stored procedures by aggregated execution metrics over all executed statements and nested stored procedures, and drill down into a stored procedure to analyze the executed statements
This feature is supported for DB2 v10 FP2 for Linux, UNIX, and Windows as the monitored database.
Report scheduling, emailing, and retention
You can now store reports and schedule and manage them from the web console.
- Report scheduling and emailing:
You can create and schedule report jobs without having to use external job scheduling tools. Report jobs are a useful way to store parameters that you use to create reports that you want to run more than once. You can also define email addresses to be notified about report job execution success or failure.
- Report retention:
Stored reports are useful to review and analyze performance data and share the data with other team members. You can browse through or download reports that are stored in the repository database. You can specify how long to retain stored reports.
For more information about these new reporting features, see the information center.
Usability improvements on the Overview dashboard
- If you set a baseline on the Overview dashboard, normal range bars are now displayed to visually indicate how the current value compares to the specified baseline.
- For Solaris and HP/UX running DB2 V10 FP2 for Linux, UNIX, and Windows, OS metrics are now displayed on the dashboard.
Changes to the Databases page
The Databases page has a new look and feel and some new features.
- Some actions have been moved or added under the Monitoring and Blackout drop-down menus.
- The term "automatic data collection" or "automatic collection" is introduced to refer to the collection and retention of monitoring data that enables historical monitoring. This term is used throughout the menus and in the performance monitoring status column.
Blackout event scheduling
You can more flexibly schedule blackout events to disallow monitoring and job execution on a database for a period of time that you specify. Use the Blackout Events page to define the schedule. Operational alerts are triggered when a blackout event is started or stopped. The web console server controls the start and stop of each blackout event, so it must be running for the blackout event to start or stop.
New and changed system templates
The list of predefined system templates has been revised to include categories of templates that allow you to implement deployment stages for monitoring a database. The new categories are:
Collects basic information with short retention times recommended for initial deployments. Upgrades to more comprehensive monitoring configurations should be undertaken after validating available resources for the DB2 performance repository using the Monitoring server template.
- Best practices
Collects the information needed for steady state monitoring, reporting, and occasional problem determination according to best practices. In general these templates are derived from the previous 'low overhead’ templates.
- Detailed diagnostics
Collects the information needed for detailed problem analysis and reporting. These templates might be turned on during certain time periods for detailed problem determination or reporting scenarios. In general these templates are derived from the previous ‘all details’ templates and can impose noticeable overhead on the monitored server.
In addition, a new system template ("Monitoring server") was added for monitoring the InfoSphere Optim Performance Manager repository database.
For descriptions and definitions of each template, see the information center.
New and changed privileges
The following new privileges- or changes to existing privileges - are available in this release. If you are upgrading to this release from a previous release, you might need to grant these privileges to existing users so that they can continue to perform privileged tasks.
- Is Database Owner (new web console privilege)
This privilege provides authorization to manage the monitored database, for example, to configure monitoring.
- Can Enable Automatic Data Collection (new monitored database user privilege)
This privilege provides authorization to enable or disable automatic data collection, which allows for historical monitoring of the database.
- Operator (changed web console privilege)
In previous releases, Operator and Viewer roles were the same. Now, the Operator privilege provides authorization for adding database connections.
For more information about new and changed privileges, see the information center.
On the Alert Notification panel, you can define alert actions to be executed when the alert occurs. These actions can be configured on the Job Manager page by users with the appropriate privileges.
Connection profile sharing
You can use InfoSphere Optim Configuration manager to share database connection definitions across tools. An administrative user can subscribe to database connection changes that are sent by InfoSphere Optim Configuration Manager. You must have InfoSphere Optim Configuration Manager to use this feature.
For more information about connection profile sharing, see the InfoSphere Optim Configuration Manager information center.
New support features
- IBM Support Assistant (ISA) framework
New scripts are available through the ISA framework that you can run to collect files for troubleshooting and send them to the support team. The scripts (collector.bat and collector.sh) are located in the "support" sub-directory of the main InfoSphere Optim Performance Manager server installation directory. This feature replaces the use of petrace.
- New command to determine license status
A new script is available that you can run to determine the type and version of the license file that is applied on the InfoSphere Optim Performance Manager server. The scripts (opmlicm.bat and opmlicm.sh) are located in the "bin" sub-directory of the main InfoSphere Optim Performance Manager server installation directory.
- DB2 v9.1 and v9.5 for Linux, UNIX, and Windows are no longer supported to be used as the repository database.
- DB2 v9.7 and 10.1 can be used as the repository database, with specific fix packs applied.
For more details, see the system requirements techdoc.
InfoSphere Optim Performance Manager, Version 184.108.40.206
InfoSphere Optim Performance Manager Version 220.127.116.11 provides the following key enhancements.
Stored procedure monitoring for DB2 for z/OS
You can now use the SQL Statements dashboard to analyze statements and calling paths for stored procedures. Use the controls on the dashboard to see top stored procedures by aggregated execution metrics over all executed statements and nested stored procedures, and drill down into a stored procedure to analyze the executed statements and calling paths.
The following prerequisites are required to use this feature:
- IBM Tivoli Omegamon XE for DB2 Performance Expert on z/OS V5.1.1 plus PTFs including OPM V18.104.22.168
- DB2 V10 on z/OS + PTF (PM53243)
- If Extended Insight is used: Data server client DB2 V9.7 FP6
User-defined table functions (UDFs) are now provided so that you can extract historical monitoring data from the repository database and create your own reports, using external tools such as IBM Tivoli Common Reporting. UDFs are provided to extract the following types of data:
- Operating system metrics and statistics
- Table space metrics and statistics
- Database metrics and statistics
- Database memory consumption
- Workload management metrics
- Summarized SQL statement execution statistics
Other reporting enhancements
- The new Cross Database Overview report shows key performance data for multiple monitored databases for a specified period of time. You get an overview of how these monitored databases are performing in one report. If you notice that a particular database has a problem, you can analyze that database further.
- The Table Usage report now includes statistics for partitions and members.
- The Performance Overview report now shows a summarized alert list with the option to open a detailed alert report per alert type. There are also layout improvements in this report.
Monitoring configuration improvements
If you are monitoring DB2 V9.7 or later databases using the in-memory metrics data collection method, you can now specify data collection filters in the Configure Monitoring wizard to reduce the amount of collected data. For example, you can choose to filter out all catalog tables, or only collect locking data for specific applications. Specifying these filters can reduce the overhead on the monitored database.
- For monitored databases DB2 v9.7 FP4 and higher: The restriction to run only one statistic event monitor on the monitored database is removed. A statistic event monitor that is started manually on the monitored database and a statistic event monitor that is started by InfoSphere Optim Performance Manager can now coexist.
- New statistics event monitor metrics for service classes, workloads, and queues that were introduced in DB2 V9.7 and DB2 V10 are now collected by InfoSphere Optim Performance Manager and are accessible via the custom reports table functions.
You can now select multiple alert notification definitions or alert types on the Alert Notification panel to make changes to all selected items in a single step.
- HP-UX 11i v3 IA64
- Solaris 10 x86-64
InfoSphere Optim Performance Manager, Version 5.1.1
InfoSphere Optim Performance Manager Version 5.1.1 provides the following key enhancements.
New "At-a-Glance" view on the Overview dashboard
If you are monitoring DB2 V9.7 or later databases using the in-memory metrics data collection method, you can use this new tab on the Overview dashboard to see a higher level view of system activity and compare current or historical data to baseline data. On this tab you can view CPU distribution overall or within DB2, workload, plus locking, I/O, and SQL processing metrics. Use the time slider control to select the baseline time period. After a baseline is set, at-a-glance values are automatically compared to the baseline and significant deviations are indicated.
DB2 V10: Multi-temperature storage support
- Storage group metrics are now displayed on the Buffer Pool and I/O and Current Table Spaces dashboards.
- A new Storage Group report is available. This report shows, for a specified period of time, an overview of the table spaces that are used by the storage groups so that you can better understand utilization and growth.
- Additional Workload Manager improvements are available for multi-temperature storage as described below.
DB2 V10: Workload Manager
- The DB2 Workload Manager user interface in InfoSphere Optim Performance Manager is enhanced to enable prioritization based on the storage group of the data accessed.
- DB2 10 provides a new WLM dispatcher feature that allows you to manage what percentages of CPU resources are assigned to service classes. The DB2 Workload Manager user interface in InfoSphere Optim Performance Manager surfaces the options for dispatcher shares and limits in the WLM configuration panels.
- DB2 10 enables WLM for use with pureScale, and the DB2 Workload Manager user interface in InfoSphere Optim Performance Manager is enhanced to support that configuration.
Extended Insight: Support for WAS ND deployments
Extended Insight is now supported for WebSphere Application Server Network Deployment environments. Install and configure the Extended Insight client software (Data Tools Runtime Client) on the deployment manager computer, and the configuration tool distributes the Extended Insight files to remote nodes.
More granular data collection
Using the Configure Monitoring wizard, you can now specify different collection intervals for different kinds of data. For example, you can use this enhancement to collect buffer pool information every minute and table space information every hour.
Kerberos authentication is now supported for the InfoSphere Optim Performance Manager DB2 instance and for the monitored instance.
The following new alert types are built in to the product:
- For DB2 V10 - HADR health and performance alerts: These include new alerts for standby connection and readiness, alerts regarding logging issues between the primary and standby, and distinguishing a single failed standby from last remaining failed standby.
- DB2 pureScale health and performance alerts: These include new alerts that recognize cluster alerts failure, additional state changes in the cluster facility, threshold alerts on group buffer pool hit ratio and cluster cache facility virtual memory, and page reclaim rate.
- Lock Escalation performance alert
- You can now create custom, user-defined alerts using your own SQL or scripts to detect issues and link them into the standard alerting, notification, and blackout infrastructure.
InfoSphere Optim Performance Manager, Version 5.1
InfoSphere Optim Performance Manager Version 5.1 provides the following key enhancements:
Improvements in monitoring data collection and storage
If you are monitoring DB2 V9.7 or later databases, this release of InfoSphere Optim Performance Manager uses state-of-the-art DB2 in-memory metrics to access the majority of the performance data. This method of data collection provides a lighter weight monitoring infrastructure than the snapshot monitoring that is still used for DB2 V9.1 and V9.5 databases. In addition, the use of in-memory metrics facilitates real-time data collection and sub-minute refreshes for live problem diagnosis.
If you are monitoring DB2 V9.7 or later databases, InfoSphere Optim Performance Manager now uses a more efficient schema to store performance data in the repository. This schema is optimized to reduce the disk footprint for performance data, improve retrieval times, and facilitate rolling aggregations. The result is a single schema that is used for all performance data that feeds the dashboards and reports.
To further reducing the disk footprint, the use of row compression is enabled and included in entitlement when InfoSphere Optim Performance Manager and the DB2 Storage Optimization Feature are purchased together as part of a single offering such as DB2 Advanced Enterprise Server Edition or InfoSphere Warehouse Advanced Enterprise Edition.
Automatic performance history aggregation and retention
Retaining long-term performance data is important for proactive tuning, trend detection, and capacity planning. In this release, it is easier to retain performance data at 1 minute, 15 minute, 1 hour, and 1 day rolling aggregations.
When you configure a database for monitoring, you choose how long to retain each aggregation level.
The full range of retained data is viewable directly from the dashboards and from the reports. Depending on the reporting timeframe that you choose, InfoSphere Optim Performance Manager determines the optimal aggregation level to display on the relevant dashboard or report.
Analysis of performance data in real time
If you are monitoring DB2 V9.7 or later for Linux, UNIX, or Windows databases using the new in-memory metrics data collection method, you can view data collected at any point in time, whether in real time, yesterday, last week, or last year. All dashboards can display the full range of collected monitoring data.
You can switch to real-time monitoring to analyze current performance problems that require the need to see the current activity on your database in sub-minute refresh rates. Manual refreshes as well as automatic refreshes are possible on each inflight dashboard by using the time slider selection control. With each refresh, new monitoring data is collected from the monitored database and displayed but not saved in the repository database.
In-depth connection analysis
Use the new Connection Dashboard to identify and analyze top connections and their activities and resource consumption either in real time or in history mode. You can identify the top connections by your selected top metric, for example CPU time.
The details area of the dashboard displays the details for a selected connection including application details, workload and service class information, server times, I/O details, row and transaction details, locking information, and utility execution.
From this new dashboard you can directly force a connection or create an in-context connection report.
Enhanced SQL analysis
Use the new SQL Statements dashboard to identify the SQL statements that are consuming the most resources, whether they are repeated short running queries or high cost analytical queries. You can view top individual executions of SQL statements in the Top Individual Executions view or aggregated metrics across executions in the Execution Summary view. In either view you can identify the top SQL statements by your selected top metric, for example Execution Elapsed Time.
The details area of the dashboard displays the details for a selected statement including the complete statement text, workload and service class information, server times, I/O details, row activities, and locking information.
If you have InfoSphere Optim Query Workload Tuner Version 3.1 installed on the same computer from which you are using the web console, then you can use the Tune and Tune All buttons on the dashboard to either transfer a selected SQL statement or a complete list of SQL statements to the Query Tuner client for further analysis. The Query Tuner client is also included in Data Studio Version 3.1 and provides a limited set of query analysis and tuning features.
If you have InfoSphere Optim Configuration Manager Version 2.1, you can open this product from the dashboard in context to view relevant changes so that you can quickly determine whether recent changes might be the underlying cause of performance issues.
You can open the SQL Statements dashboard from the Buffer Pool and I/O dashboard if you see problematic tables. Select a table and click Show SQL to drill down to the SQL statements that use that table.
The enhanced Database Connection report is an overview of the active database connections for a given time frame. The report displays key performance indicators such as lock wait times, physical and logical reads and writes, and other connection statistics. You can drill down into a specific connection to view complete identification details, timing information, SQL activity, locks, cache, buffer pool, sorts, and agent-related activity. This report can identify applications that are not performing well or applications that are causing problems in other database applications.
The new Performance Overview report shows the overall health of the monitored database for all major metrics. You can view the complete database system or only parts of it.
The new SQL Baseline Comparison report lets you compare top SQL statements in the same database to see maximum improvement, regression, or both, within two time frames. You can drill down for a detailed analysis of a specific SQL statement. The report includes the complete statement text, general statement information, response time analysis, sort performance, I/O activity, and buffer pool activity. You can identify problems with insufficient system resources or run InfoSphere Optim Query Workload Tuner to analyze problems with the access plan or to evaluate inadequate database statistics or indexes.
The new Table Usage report lets you identify "hot" tables or fast growing tables that might cause disk contention or that might need reorganization.
The new Top SQL Package report allows you to identify top resource consumers by package name in a specified time interval. You can look at a graphic representation of the workload by day to identify heavy duty, critical, or rogue packages.
The Workload Manager Overview report is enhanced with additional drill down into Service SuperClass, SubClass, and Work Action Set reports including details on queue time, execution time, lifetime, and histograms for advanced tuning of Workload Manager configurations. The Service SuperClass, SubClass, and Work Action Set reports can now also be launched from the Report launchpad.
The Top SQL and Disk Consumption reports contain additional enhancements that have been requested by users. For example, if you are monitoring a DB2 V9.7 database using in-memory metrics, the Top SQL report now shows static and dynamic SQL.
Enhanced partition-level and pureScale performance analysis
All inflight dashboards now allow for easier analysis of performance metrics across all partitions, a single partition, or a partition role. You can configure partition categories called roles to easily view metrics across similar partitions such as coordinator, data, and ETL. Similarly, if you monitor a pureScale database, you can view details across a data sharing group or for specific members.
New pureScale metrics are introduced in this release, including Cluster Caching Facility (CF) monitoring metrics such as:
• Group Buffer Pool Hit Ratio per connection, statement, buffer pool, or table space
• CF locking information, CF requests/time on connection or statement level
• Page reclaim information
• CF configuration parameters in database and database manager reports
New health alerts can notify DBAs or others of CF or member failures.
Easy configuration of DB2 Workload Manager
In this release, you can now deploy the enhanced DB2 Workload Manager best practices template to get up and running quickly with DB2 Workload Manager for warehouse environments. Deploy the default template, tune service class boundaries based on histograms of activity mappings, and enable governance to prevent rogue queries from taking over systems. For advanced users, the Workload Manager configuration interface now accepts any existing Workload Manager configuration without modification and exposes all features and options for modification.
Operational alerts are now created if InfoSphere Optim Performance Manager detects issues during its operation that could lead to incomplete or non-continuous collection of monitoring data. These issues are now surfaced in the web console Alert list and can therefore be detected and solved quickly. The alert details and descriptions help you to either solve the issue yourself or to give the details to the IBM support team.
Extended Insight usability improvements
On the Extended Insight dashboard, bar charts are now available that show you the distribution of the average response time over the client, network, and data server layers graphically. This allows you to identify at a glance where most of the time is spent for your database transactions workload. If you specify response time alert thresholds, then you also see the percentage of normal, warning alerts, and critical alerts as bar chart which allows you to quickly identify workloads that do not meet service level objectives and drill down into further detail.
New information center
With this release, InfoSphere Optim Performance Manager has a new product-specific information center. This new information center allows you to more easily browse and search the information about the product. Use the tabs at the top to view information about related products in the suite.
|Information Management||Optim Performance Manager for DB2 for Linux- UNIX and Windows||5.1|