The expression “time is money” is true when it comes to IT. Businesses depend on their IT departments to keep applications performing optimally around the clock. When a critical application isn’t performing well, the business is impacted in two key ways. First, the IT organization is unable to meet service-level objectives, impacting end-user productivity and customers. Second, poorly performing applications directly impact cost-ofownership by increasing resources required, often leading to costly upgrades.
Consider a brokerage house where a trading system depends on DB2 on the mainframe. These applications are extremely response-time sensitive. An online user attempting a trade will quickly go to a competing site if the service level provided isn’t the best. And once the customer goes to a competitor, getting him back is more expensive than initially acquiring the customer.
Keeping applications performing at peak efficiency is one of the biggest challenges for IT organizations. Tuning production DB2 applications is a continuous task–-one that’s expensive, time-consuming, and error-prone. This effort begins early in the development lifecycle and continues through the life of the application.
Industry experts agree that 70 to 80 percent of performance problems that occur in DB2 applications are SQL-related. In most cases, these problems usually can be attributed to the specific SQL coding syntax used in the application. In other cases, the problems can be attributed to DB2 object design, locking problems, DB2 pool configurations, or other factors. SQL is a powerful language that continues to evolve; however, tuning SQL is a greater challenge today than yesterday. This increased difficulty is caused by several factors, including:
- Increased functionality and flexibility of the SQL language brings many more options and more room for error.
- SQL development outside the IT organization’s span of control makes quality control much more challenging. This is a problem with ERP and other third-party applications.
- Changing nature of workload characteristics affects traditional tuning methodologies. Dynamic SQL now makes up an increasing percentage of the overall workload. This SQL is now coming into DB2 from distributed connections.
The IT organization’s goal then is to proactively tune application SQL to address the two issues previously discussed. Efficient DB2 applications with optimized SQL provide the best chance for meeting stringent service-level objectives. This same efficiency ensures that applications optimize usage of scarce mainframe computing resources. IT organizations recognize that time and money spent tuning SQL performance is easily justified. The tuning process is still a challenge, due in large part to the following:
- Shortage of qualified personnel: SQL and DB2 tuning in general take extensive experience that’s difficult to develop and more difficult to find in the marketplace.
- Large volume of performance data to analyze: Any tuning effort requires large amounts of performance data that must be collected, organized, and analyzed on an ongoing basis.
- Complexity of the task: DB2 has many moving parts with many implementation and execution options that must be considered in the tuning effort. SQL is a very complex language with many options as well.
Vendor tools help identify and correct SQL performance problems. They collect and analyze DB2 performance metrics. This analysis often leads to increased SQL efficiency and improved DB2 object design, helping improve application response times and overall DB2 system performance while lowering DB2 cost of ownership. A SQL performance management solution should provide capabilities that allow you to:
- Quickly identify and analyze problem SQL in your production environments
- Proactively correct SQL statements that fail to meet quality standards before an application reaches production
- Provide extensive, rules-based analysis and recommendations for improving DB2 access paths.
These kinds of tools make DBAs and performance specialists successful in tuning DB2 applications to reduce application cost and improve overall service levels to the IT customers in their organization. Ultimately, improving SQL performance can greatly affect the impact IT has on a company’s business goals. Z