Weed out costly, inefficient SQL
Find and eliminate the wasteful SQL statements that are slowing you down, with the BMC Performance for DB2 SQL solution. Designed to manage SQL performance throughout the application lifecycle, the tools in this solution will help you:
- Diagnose performance problems and track them to their source, so you can effectively tune your SQL.
- Anticipate SQL-related slowdowns so you can resolve them before they impact service levels.
- Avoid cumbersome reorganizations and costly CPU upgrades by making the most of your resources.
- Ensure your SQL is running efficiently and cost effectively at all times
Examine your SQL from every angle
With a collection of tools that range from pre-production SQL validation to real-time performance data analysis, Performance for DB2 SQL can help you dramatically improve the efficiency of the SQL across your DB2 environment. Get recommendations for your tuning your most resource-greedy SQL statements, optimizing your workload access paths, and improving your indexing strategy.
Increase availability by pinpointing resource-consuming SQL statements without running expensive SQL traces.
Target costly statements
Streamline your most expensive SQL statements for more efficient processing.
Catch problems early
Validate your SQL during development to ensure valid access paths and uphold SQL best practices.
Get smart about reorganization
Evaluate both space usage and performance metrics to determine when or if reorganization is necessary.
Choose the right access paths
Compare several versions of your workload access paths to avoid problems when making a change or moving to a new environment.
Examine your indexing approach
Perform index usage analysis—including identifying unused indexes and modeling index changes—to improve your indexing strategy.
Understand your performance over time
Gather and manage historical performance data for trending and analysis.
Reduce costs while maintaining service levels
- Save CPU costs by tuning SQL and avoiding unnecessary reorganizations.
- Improve response times for your end users, without expensive hardware or software upgrades.
- Maintain your service level agreements with healthy, efficient SQL statements.
- Give your developers and DBAs the information they need to make the most of their DB2 environment.
“We realized that if we didn’t do something to reduce consumption, we would need an additional CPU upgrade. So we began an IT-wide tuning initiative and used our BMC tools to find ways to improve performance and reduce MIPS.”— Terry Glover, Director, Technical Architecture and Support, Dillard’s Read more about Dillard’s success with BMC solutions
Products included in this solution
The Performance for DB2 SQL solution is made up of the following products, all of which are designed to eliminate performance issues caused by inefficient SQL.
Apptune for DB2
BMC Apptune for DB2 allows you to track detailed performance data in real time, identifying SQL performance issues without the service interruptions incurred by running expensive DB2 SQL traces. Pinpoint the most heavily accessed DB2 tables and indexes, and you can analyze page activity, buffer pool efficiency, and I/O response times. Apptune makes it easy to identify opportunities for tuning, concentrating on the SQL statements with the most impact. Plus, if you are paying variable software charges based on MLC, you can use Apptune to ensure there is no costly SQL executing in your 4-hour peak, which reduces software charges for everyone, not just DB2.
SQL Explorer for DB2
Ideal for development work, BMC SQL Explorer for DB2 allows developers to detect and resolve potential SQL performance issues before the application reaches production. Parse your SQL statements, check for syntax issues, and run the statements in a testing environment to track down any performance issues that might arise in a production. Run your application through a variety of simulations to determine the optimal design.
Performance Advisors for DB2
Using the data gathered by the other components of this solution, BMC Performance Advisors for DB2 will suggest opportunities for tuning and optimization. The Index Advisor will assess the structure of your indexes and make recommendations for improvement. The Workload Compare Advisor compares multiple versions of workload access paths, so you can see the impact of a change before you make it. The Reorg Advisor evaluates DB2 object performance, so you can determine when and if a reorganization is required to solve a performance problem.
Workbench for DB2
BMC Workbench for DB2 provides a secure web-based graphical interface that allows developers and DBAs access to information about their DB2 environment on the fly. Workbench supplies DB2 catalog information in an easy to use dashboard, and allows commands to be issued to any DB2 subsystem. Users can extract DDL describing object structures and potentially re-execute the DDL in other DB2 environments. In addition, Workbench for DB2 presents SQL EXPLAIN results in an easy-to-interpret format, which can be enhanced by using expert system rules to flag performance or best practice concerns. Workbench also provides “what if” tuning analysis of SQL and/or indexing changes, which will tell you whether your change make things better or worse. Finally, Workbench functions are protected by DB2's own security mechanisms – Workbench does not confer any ability that the signed on user does not already possess.