Operating Systems

SQL Performance Tuning for Application Developers

5 Pages

What is your biggest challenge in managing applications in a DB2 for z/OS environment? Is it continuously keeping data and the application available, meeting increasingly stringent Service-Level Agreements (SLAs), managing the ever-growing complexity in application development environments, or just meeting the deadline for your next project? Chances are, you’re being asked to do all this: provide more services, in less time, with more complexity and less budget than ever. How can you get the most ROI as part of the application development team? You could increase the capacity of your mainframe, or hire more developers and DBAs, but that incurs significant cost. A more effective methodology is to improve your applications’ performance.

Improving DB2 application performance is challenging because each application is different. Fixing performance problems isn’t easy, and finding the root cause of the performance problem is even tougher.

This article provides basic information about SQL tuning, shows where to look for clues to finding the root cause of the performance problem, and describes how tuning tools can help.

SQL Performance Tuning Basics

Before you can improve application performance, you must analyze your SQL and understand what the application needs to do. You may need to make performance trade-offs based on the cost of resources and ability to test new applications. The following questions will help you determine what options are available for tuning:

  • Is the SQL static or dynamic?
  • What schema is involved in the application?
  • Have statistics been collected on application objects referenced by the SQL?

You may find that one SQL statement is causing a major performance problem. In general, the 80/20 rule applies—20 percent of your SQL statements probably use 80 percent of your DB2 resources. So, it’s important to consider the overall DB2 workload when tuning a single SQL statement.

Performance Data

Tuning SQL statements is an art form, but that art is based on concrete facts and figures. Performance data is important. SQL performance tuning data is derived from several sources, including execution metrics, IBM Visual Explain data, and object statistical data.

Execution Metrics

Be sure you have actual run-time statistics for a high percentage of the total DB2 workload. Collect execution statistics in both development and test systems. The earlier you find a performance problem, the cheaper it will be to change the application.

5 Pages