Only a trained analyst can catch this type of design problem during a code walkthrough. Although a plan analysis tool significantly reduces the effort involved in the code review process, it cannot eliminate it.
So what should you look for in an SQL analysis tool? The first feature required of SQL analysis tools is the
ability to read and interpret standard EXPLAIN or SHOW
PLAN output. The tool should be able to read the plan table or
interface directly with the DBMS command to obtain the output. It then must be
able to automatically scan the EXPLAIN or SHOW
PLAN data and report on the selected access paths and the
predicted performance. Advanced tools will provide recommendations for
improving the SQL by adding indexes or modifying the SQL.
Yet another category of tool can evaluate access paths as you REBIND programs and categorize them into changed and unchanged access paths. This helps to identify where SQL tuning may be required. Advanced forms of these tools also apply rules tot he changed SQL to indicate if the access path is better or worse than the prior access path. Such tools can be incredibly helpful for performing mass rebinds of your production programs.
An SQL monitoring solution can identify running SQL statements, filter the information, and display it in an appropriate order and configuration. For example, you can use an SQL monitor to identify the Top Ten CPU users over the past hour (or the past day, week, etc.)
Usually, there is the on-line capability, that displays what is happening right now, and the historical capability, which can display details and trends over time.
An SQL monitor is particularly helpful when working to remediate production performance issues where hundreds or thousands (or more) of SQL statements can be running at any one time.
End-to-End Performance Tools
Modern applications require multiple system components
and run across multiple networked devices to deliver functionality. When
performance problems arise, it can be difficult to determine what, exactly, is
causing the problem. Is it on the client or the server? Is it a networking
problem? Is it a database issue or a code problem?
End-to-end performance monitoring tools exist that track
an application request from initiation to completion. These solutions provide
enhanced visibility specifically into application performance—giving
organizations the power to understand both when and why performance has
degraded, and the information needed to improve matters in a
By following the workload as it progresses across multiple pieces of hardware and software, problem determination becomes possible.
Workload Testing and Estimation
Another category of SQL performance tool allows you to identify a workload consisting of programs and transactions that are to be run during a specific timeframe. The tools help to identify performance issues that crop up only when the application is running at a production volume.
Finally, no overview of application performance tools for DB2 would be complete without a brief mention of IBM's Data Studio. Data Studio is a free-of-charge tool for basic DB2 administration and development tasks.
Data Studio offers an easy to use GUI interface for the following:
- Designing data access queries and routines
- Building, running, and tuning SQL
- Building, testing, and deploying stored procedures (using SQL or
- Creating Web services in for Service Oriented Architecture (SOA)
- Developing DB2 SQLJ applications
- Managing database objects and authorizations
You can download Data Studio
at IBM’s website. It is available as a stand-alone package geared mostly for
DBAs, or as an IDE geared for both DBA and development work.
Of course, IBM sells other DB2
tools for a fee, some of whichcan integrate and work well with Data Studio. And there are other tools that compete with Data Studio that offer a lot more functionality than the basics provided by the free capabilities of Data Studio (such as Dell's Toad).
These past few posts have taken a broad overview look at the categories and types of performance tools available for managing the performance of your DB2 for z/OS environment. Many of the same categories of tools are available for DB2 for LUW (as well as other DBMS offerings).
Have I missed any important categories? If so, drop me a line or add a comment here to the blog. I'm always interested in getting feedback.
Thanks... and happy performance tuning!