Hot off the press is a new Redbook on InfoSphere Optim Performance Manager and InfoSphere Optim Query Workload Tuner here: http://www.redbooks.ibm.com/abstracts/sg248111.html?Open
You'll find architectural overviews, planning and installation guidance, feature overviews, as well as usage scenarios. This is a great reference whether monitoring transaction processing or business intelligence workloads.
Let us know if you find this helpful!
One of the issues that users have raised to me is the impedance mismatch between our web-based tools and our eclipse-based tools, in particular, between InfoSphere Optim Performance Manager (Performance Manager) and InfoSphere Optim Query Workload Tuner (Query Tuner). Query Tuner availability on the Eclipse platform grew out of our rich investment in Eclipse and our strategy to drive improved query performance early in the development cycle. Thus, key integration with Data Studio and Rational development features were, and continue to be, highly valued.
Key use cases for performance management involve using Performance Manager to identify high cost queries and transfer them to Query Tuner for analysis and advice. Performance Manager makes it easy to identify high cost queries (either long running queries or short queries that are costly in aggregate) or to identify query workloads that are not meeting their response time targets. The handshake between Performance Manager and Query Tuner provides the full problem identification through problem resolution support. However, Performance Manager provides a web console and Query Tuner is an Eclipse-based client, so there is a transition that can be unexpected, and for some, unwelcome. With the new releases coming in June, we are embarking on delivering query tuning features directly in the web console! This release is a first step, so all features are not available, but it is indicative of our direction to make the cycle of problem identification through problem resolution a seamless experience. When the Eclipse client is installed and open, we will still transfer the queries to the Eclipse client since it has the full query tuning function.
Features available from the web will now include the ability to:
Re-explain a query or workload
Format and annotate a SQL statement
Get statistics advice for a query or workload
Get index advice for a query or workload
Generate, store, and share reports containing the advice
The screen capture below previews the query tuning tab right inside the Performance Manager web console. The embedded job manager in Performance Manager is used to execute the query tuning jobs. All the submitted query tuning jobs can be viewed and selected in the grid including their status and progress.
Features still requiring the Eclipse client are:
Visual Explain and Access Path Explorer (yes, we know this is a priority and we are working on it )
Access Plan Advice and Workload Access Plan Advice
Access Plan Compare and Workload Access Plan Compare
Statistical View Advice
Materialized Query Tables Advice
Multidimensional Clustering Advice
Partition Distribution Advice
Table Organization Advice (new in this Query Tuner release in support of BLU Acceleration)
Test Candidate Indexes
Plan Hints Support
Workload Capture (from other sources) and Workload Management
Also, you still need the Query Tuner license activated on the target database to be able to use the function from the Performance Manager web console. The license activation capability is still in the Query Tuner Eclipse client.
Hope you will enjoy this new function. Give us some feedback on our Query Tuner forum or post a question here.
Today IBM announced updates to several performance management products, all of which are available individually or as part of the DB2 Advanced Enterprise Server Edition. They include:
We all know that poorly performing applications can lead to lost revenue and customer dissatisfaction. We have made numerous improvements in all these products to make it easier to identify and resolve issues before they can impact your business. There are far too many improvements to list but some notable improvements include:
IBM InfoSphere Optim Performance Manager V5.2
We have added enhancements to better analyze stored procedure performance. Rank stored procedures and drill down to find poorly performing SQL. We have extended reporting now with scheduling, emailing, and retention management. To help you automate actions, we have provided the ability to trigger user defined jobs in response to alerts.
IBM InfoSphere Optim Query Workload Tuner for DB2 Linux, UNIX, Windows V3.2
Once you have identified SQL that could use some help, use Query Workload Tuner to provide suggestions and analyze possible improvements or use Query Workload Tuner to analyze SQL before it ever goes into production. V3.2 has many enhancements to help with "what-if" analysis. Use Query Workload Tuner and virtual indexes to analyze the impact of adding, dropping or changing an index. Use the plan comparator to see the changes to access plans.
IBM InfoSphere Optim pureQuery Runtime for Linux, Unix Windows V3.2
Suppose you have poorly performing SQL from a third party application. pureQuery Runtime can help with that. Use pureQuery to swap out poorly performing SQL. With V3.2, new configuration scripts makes it easier to deploy on WebSphere Application Server and configuration changes can go into effect immediately.
IBM Data Studio V3.2
IBM Data Studio has also been updated. Many enhancements were made to alerts and scheduling in the web console. Users can now define their own alert view preferences. The client was updated to make debugging even simpler and more robust. Debug triggers and PL/SQL anonymous blocks. You can now view global and package variables, associative arrays and array of rows in the debugger. It is also much easier to recompile a routine for debugging. It is almost automatic. Starting with V3.2 the Data Studio administration client will be available via DB2 Express-C
and refreshed as needed. The full client is available via the normal Data Studio download page
. Remember, for the full client, when using the Installation Manager option, only the options selected will be downloaded and installed on your system. If you are using the administration client, try the full client.
Finally, connection management is now easier for many of these products. IBM InfoSphere Optim Performance Manager, Query Workload Tuner, and Data Studio can now obtain and stay synchronized with connection information from the Optim Configuration Manager's database repository.
For more information, check the announcement here
At the end of August we released a new fixpack for InfoSphere Optim Performance Manager (OPM) 22.214.171.124. In addition to defect rollup, there were a few functional enhancements as well:
- Most importantly, we have improved the ability to generate custom reports from the performance repository. New documentation, table functions, and Tivoli Common Reporting support custom report generation. Or users can use any reporting tool of choice.
- Commonly requested, we have added a pre-built cross-database report for periodic reviews of your database landscape.
- We have implemented feedback to the Table Usage and Performance Overview reports
- When monitoring DB2 9.7 or later, you can further reduce the scope of what monitoring data is captured based on monitor profile filters, e.g. excluding catalog tables for table metrics collection.
- Enhancements to DB2 Workload Manager support include collection of additional metrics introduced in DB2 9.7 and DB2 10 as well as the ability to allow OPM to start another statistics event monitor when one is already started outside of OPM.
- Optim performance Manager now also runs on HP-UX 11i v3 IA64 and Solaris 10 x86-64
For Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS users, Optim Performance Manager added stored procedure monitoring enabling a view of the aggregate expense of a stored procedure, analysis of calling paths, and correlation of SQL calls with the stored procedure that issued them.
A more detailed description of the content is available in the What's New page. Existing customers can grab the fixpack from Fix Central.
Sorry for the late notice. Cliff Leung, chief architect of Optim Query Workload Tuner, and I will be presenting a webcast on Optim Performance Management this Thursday via the DB2 Tech Talk series. This setting provides an opportunity to drill down a little given that we have 90 minutes. We are hoping that it will be informative with lots of time for questions. You can register here
. I hope you will join us.
Here is my promised update on InfoSphere Optim Performance Manager (OPM) 5.1.1 that will be available April 30th. As I mentioned in my earlier posting Optim Releases Support DB2 10, the majority of the enhancements are in support of DB2 10 features, so let me go over those first:
- Multi-temperature support – DB2 10 multi-temperature data management allows you to store different portions of your data on different classes (speed and cost) of storage so that you can match access requirements to underlying storage and non-disruptively change them over time as data ages and as access requirements change. “Storage groups” are used to define the different classes of storage and tables spaces are now defined within a storage group. OPM adds monitoring support for storage groups showing the table space to storage group relationship on alerts and dashboards and provides a new storage group report to understand utilization and growth. In addition, DB2 Workload Manager (WLM) was enhanced to enable prioritization based on the storage group of the data accessed. These features are enabled through the OPM user interface as well
- Multiple HADR – DB2 10 supports configuring multiple standbys now. OPM has improved alerting for high availability and disaster recovery (HADR) environments including additional alerts regarding standby connection and readiness, alerts regarding logging issues between the primary and standby, and distinguishing a single failed standby from last remaining failed standby.
- pureScale – DB2 10 continues to enhance the pureScale feature and OPM continues to enhance its monitoring of pureScale deployments. New alerts recognize cluster alerts failure, additional state changes in the cluster facility, and threshold alerts on group buffer pool hit ratio and cluster cache facility virtual memory, and page reclaim rate. DB2 10 enables WLM for use with pureScale and OPM is enhanced to support that configuration as well.
- WLM Dispatcher – DB2 10 provides a new WLM dispatcher feature that allows you to manage what percentages of CPU resources are assigned to service classes. OPM surfaces the options for dispatcher shares and limits in the OPM WLM configuration panels.
In addition, there are a few enhancements apart from DB2 10 support I want to mention that I think you’ll be excited about.
- User-defined Alerts – We get requests for additional alerts that are beyond our ability to fit into plan in the short term. Thus we are enabling user-defined alerts which will give you the ability to write your own SQL or scripts to detect issues and link them into the OPM standard alerting, notification, and blackout infrastructure. Now you can generate alerts for errors in db2diag.log, canary queries, or other alerts important to your environment. We will have some sample scripts in the Information Center. .
- More granular collection intervals – OPM configuration is based on user-specified collections intervals. Now you can specify different collection intervals for different kinds of data. For example you might want to collect buffer pool information every minute but table space information every hour.
- At-a-Glance performance overview and baseline – Finally, and for me the most exciting, is our introduction of baseline support. This is actually just the first step in a series of planned enhancements in this area. The At-a-Glance view appears in the Overview dashboard as a new tab and provides a higher level view of system activity: CPU distribution overall as well as within DB2, workload, and specific tabs for examining locking, I/O, and SQL processing. From here you can set baselines using the time slider to select the baseline timeframe. Once a baseline is set, the At-a-Glance values are automatically compared to the baseline and significant deviations indicated. Here’s a simple example of the At-a-Glance view
More information, including the Information Center updates, will be available with the general availability.
Holly Hayes, InfoSphere Optim Tools Product Manager
Updated on December 15th to correct announcement letter URLs.
More news from Optim land to help you manage DB2 performance.
We just announced and today released a new version of IBM InfoSphere
Optim Performance Manager 5.1 (for DB2 for Linux, UNIX and Windows servers) and
a new product offering IBM InfoSphere Optim Configuration Manager 2.1, which is
available for both DB2 for z/OS and DB2 for Linux, UNIX and Windows servers.
InfoSphere Optim Performance Manager 5.1 is a major release
in which we re-architected the server to take advantage of the DB2 9.7
state-of-the-art in-memory metrics monitoring capabilities. We significantly reduced monitoring overhead
and increased scalability for monitoring partitioned systems, reduced disk
footprint for performance data, and improved retrieval times. I encourage you to read about all the new
features in the announcement
letter, but here are a few of my favorites:
- All dashboards that use in-memory metrics data collection now feature
the ability to display data collected at any point in time, whether now,
yesterday, last week, or last year. I particularly like the ability to view trends instantly by just selecting the time frame.
- Performance history needed for need to detect trends, plan capacity and
for service reporting now has
automated rolling aggregation at 4 level with user-defined retention periods so you can keep data longer at lower cost.
- List, filter, sort, analyze, and report on database connections and
resource consumption across server, I/O, row, transaction, locking,
communication, and utilities.Pretty much anything you would want to see about connections.
- Lots of improvements in SQL dashboards that let you view top
executions of SQL statements or aggregated metrics and integrate with InfoSphere Optim Query Workload Tuner 3.1 just released in October.
- Many new reports are available. My favorite is the SQL comparison report that makes it easy
to find SQL performance improvements or regressions before and after
- Dashboards now have enhanced analysis for
database partitions and DB2 pureScale members.
- With a new best practices template for DB2
Workload Manager configuration, you can get up and running quickly with DB2 Workload Manager for warehouse
For more detailed information, see the information center.
InfoSphere Optim Configuration Manager 2.1 is available for both
DB2 for Linux, UNIX and Windows and DB2 for z/OS environments. It provides centralized
management of database and client configurations to get insight into IT
environments, reduce database outages, and improve compliance to enterprise
standards. Top features enable DBAs and other staff to:
- Discover client information
for databases, such as host name, IP address, user ID, client version
information and track their access to data servers. Get a complete view of database client and DB2 server topology. Check whether your driver upgrade has been completed.
- Redirect clients from one
database to another, one subsystem to another, or one member to another. This centralized management improves DBA control over staged migrations or lets them isolate poorly
behaving applications to mitigate impact to others..
- Define and enforce official
connection definitions to support workload balancing and workload
management objectives. A disconnect between developers (who set connection properties) and DBAs or systems administrators (responsible for workload balancing and resource allocation) makes it hard to get the right level of control. Now you can push down the right properties to enable data sharing or Workload Manager to be more effective in workload balancing and resource allocation.
- Track database and client
configuration changes to help determine the root cause for performance
degradation. InfoSphere Optim Performance Manager also surfaces integration with Configuration Manager so you can see what changed while investigating performance issues.
Check out our Configuration Manager demo that highlights several of the features above. You can read more about InfoSphere Optim Configuration Manager in our announce letters for offerings on Linux, UNIX, and Windows or on z/OS
or in the information center.
If you have any feedback, please post here or to our Performance Management forum.
Holly Hayes, Optim Tools Product Manager
Take Charge of your Data Environment: IBM Performance Management Solutions
Performance issues are often at the source of declining productivity, increasing capital expenditures, and lost revenue. This Thursday, November 10, we’re hosting a webinar, IBM Performance Management Solutions,
Featuring OPM and OQWT,
to walk you through IBM Performance Management offerings. I'm joined by Bob Harbus, DB2 Technical Specialist and Danny Zilio, Software
Analyst – Optim Query Workload Tuner. We'll be discussing how you can use InfoSphere Optim Performance Manager and InfoSphere Optim Query Workload Tuner to manager performance, both during development and in production. Join us to share your questions, challenges, and insights.
Hope to hear you on the call!
Holly Hayes, Optim Tools Product Manager
I've been a development manager on database administration capabilities for Data Studio for many years, and recently took over as product manager, as well. I am very excited about our new Data Studio 3.1 release. It not only includes enhancements to the previous release of Data Studio, but, more importantly, combines the feature set of Optim Database Administrator and Optim Development Studio with Data Studio and Data Studio Health Monitor to create one comprehensive product for application development and database administration, all available at no charge! Data Studio can be found as a download from developerWorks
and soon will be bundled with a number of IBM data servers such as DB2 for Linux, Unix and Windows, DB2 Connect and IBM Informix.
Data Studio 3.1 now consists of three components:
Data Studio administration client: Reduced footprint client for administration and non-Java based development. (You may remember we referred to this previously as the stand-alone package)
Data Studio full client: Full-feature client that can shell-share with other Eclipse-based products (This was previously called the integrated development environment package) .
Data Studio web console: health monitoring and new job scheduling capability (this was formerly known as Data Studio Health Monitor)
In addition to the consolidation, we've enhanced the following areas:
The object management feature has been completely overhauled. The new UI provides for in place editing of objects and eliminates the need for a separate editor. Complex object management for Optim Database Administrator for DB2 for Linux, Unix and Windows has been incorporated into the new object management for Data Studio 3.1.
There are a number of enhancements related to navigating, monitoring and managing a DB2 pureScale environment.
There are numerous enhancements to the query editor. They include version specific validation, execution control and special register support.
You can now change the user name and password on any or all connections with one action. The Data Studio web console also provides a central place to define database connections that can be imported by the Data Studio full or admin clients.
Compare and Sync
There are enhancements to reporting and visualizing changes.
DB2 for z/OS
We added support for temporal tables and query tuning now includes set/update profile support for runstats and table data sampling
Scripts may be scheduled as jdbc or clp scripts for DB2 for Linux, Unix and Windows and jdbc scripts for DB2 for z/OS.
You can find current Data Studio product information here
. Check out and post questions, comments, or requests on our forum
or visit our information center
and information roadmap.
P.S: Today we also announced the following:InfoSphere Data Architect 7.6 InfoSphere Optim pureQuery Runtime z/OS 3.1InfoSphere Optim pureQuery Runtime LUW 3.1InfoSphere Optim Query Workload Tuner LUW 3.1 InfoSphere Optim Query Workload Tuner z/OS 3.1
We released several fix packs on June 30th and a modification release on July 1. There are a few new features and platform support worth taking time to highlight.
Optim Performance Manager – Optim Performance Manager 4.1.1 is now available. We put a lot of focus on up-and-running improvements in this release. This includes embedding a lightweight web container to simplify installation, configuration, and security, simplifying our port infrastructure for Extended Insight support, improving the out-of-the-box monitoring templates based on customer experience and more. We have added pureScale-specific monitoring enhancements including pureScale monitoring templates, group buffer pool hit ratios, cluster facility lock wait time, escalations, timeouts, etc. We provided more flexibility for monitoring large DPF environments by separating collection intervals for bufferpool versus tablespace and table statistics, which provides more comprehensive information at a lower cost. We have also included DB2 Enterprise Server Edition 9.7 for use with Optim Performance Manager. For the complete list of features and more information, see the IBM Optim Performance Manager 4.1.1 release notes.
Optim Development Studio – Optim Development Studio 126.96.36.199 now also officially supports deployment on Citrix (I know several organizations that went ahead and deployed before our formal support was announced). It also includes two very cool items:
- Integration with Optim Performance Manager – Now you can pull performance metrics into Optim Development Studio to assist in determining which SQL statements need a little more tuning and whether your tuning efforts are paying off. This integration requires Optim Performance Manager Extended Edition with Extended Insight monitoring configured.
- Support for Sybase - You can connect to a Sybase database and browse catalogs, manage objects, create and run SQL scripts, and create, deploy, run, and debug stored procedures. Plus you can use the ANTs Software product IBM® DB2 SSacSA to connect to a DB2 for Linux, UNIX, and Windows database as a Sybase database.
Remember, Optim Development Studio is available on Passport Advantage to most DB2 and Informix customers as part of their DB2 for Linux, UNIX, and Windows, DB2 Connect, or Informix investment. Find it in the Linux or Windows eAssemblies. See IBM Optim Development Studio Version 188.8.131.52 Release Notes
for a more information and download updates from Fix Central
Optim Database Administrator - Optim Database Administrator 184.108.40.206 also adds official support for Citrix deployment. It also sports a new table data editor for DB2 for LUW and z/OS databases and closed a couple gaps in the dependency analysis space for packages and procedures. It also includes catalog filtering improvements tailored to very large databases. Optim Database Administrator is available to DB2 for Linux, UNIX, and Windows customers as part of their DB2 9.7 Linux or Windows eAssembles on Passport Advantage. See IBM Optim Database Administrator Version 220.127.116.11 Release Notes for more information and download updates from Fix Central.
Data Studio – Data Studio 18.104.22.168, both stand-alone and IDE, inherit the Optim Database Administrator Citrix, table data editor, dependency analysis, and catalog filtering enhancements. In addition, this release includes some enhancements specifically for DB2 for z/OS, notably use of TABLESAMPLE keyword on statistics recommendations and catalog navigation performance improvements. For more information, see the IBM Data Studio Version 22.214.171.124 Release Notes. Download updates from Fix Central or a fresh install at developerWorks.
Check them out and post questions, comments, or requests here or on our forums