|
Published date: 11 Feb 2008
The Mainstream -- February 2008 -- Issue 28
When it comes to propelling new business initiatives within your company, your database administrators and programmers can play a crucial role in growing the business. In addition to managing your key information, they often take the lead in ramping up new Web or online retail initiatives and in conducting business intelligence functions that drive greater sales revenues and opportunities.
Unfortunately, many of these techno-wizards spend a significant chunk of their time – often 60 percent – tracking performance issues, analyzing related data and fixing those problems. By adopting some proactive strategies, you can eliminate many potential performance glitches before applications launch – and quell problems more quickly in production – with tactics that can give time back to your DBAs and pay dividends toward your bottom line.
Test and test again
When writing queries that will interact with DB2, staff will undoubtedly test related performance before rolling them out. But when running “what-if” scenarios to find potential performance issues that may surface after launch, be sure your testing and production environments match closely. Even without access to the data you’ll use in production, you’ll still do better by relying on relevant statistics, such as those involving table spaces and indexes, as if you did.
By directing developers in this way, and requiring that they hone their code, you can eliminate most potential bottlenecks – the kind that often require time-consuming fixes by DBAs in production. Handling the problems in preproduction also keeps those costs off your bottom line.
Streamline SQL
Streamlining the pathways your SQL queries take to retrieve information from databases remains an important strategy for improving performance. Consider these approaches to save your DBAs time and effort:
In preproduction: When developing a new application that will interact with DB2 running on System z, write an initial SQL statement, then model or run that statement to determine the amount of computing resources it requires, including I/O cycles or CPU. Depending on the answer, you or your development staff can play “what-if” games to determine whether performance actually improves.
Will adding one index or another improve response time? Can you tweak statement structure to gain better performance versus the cost of resources? Directing development staff to maximize the performance of these queries in preproduction will mean far less time pulled away from your DBAs and other production staff, later on. A study by the National Institute of Standards Testing (NIST) found that fixing application defects in production costs six times more than resolving those issues in the application coding/unit test stages. Likewise, Gartner pegs the costs at four times more to resolve problems in production than in testing.
In production: When the application rolls into production, the bottlenecks and other performance problems should already be resolved if you’ve tested thoroughly. However, if problems surface in production, DBAs can run the SQL statement within a workload to monitor its performance. From there, they can use the information gleaned from various metrics to help refine the query by adding indexes or by tapping other relevant systems statistics. It’s a common fix, but one that IT shops often overlook in the rush to complete a project. If those tactics don’t improve performance, your company’s development staff can address the issue by, for example, rewriting the SQL.
Set up MQTs:
When managing queries that require aggregation, don’t forget Materialized Query Tables (MQT). Implementing these precalculated tables can dramatically improve the response time of everyday operations and data warehousing functions – and free DBAs from more time-consuming application tuning efforts.
For example, your company wants to compare internal salaries by department each month. Setting up an MQT within DB2 creates a standing table with the results (typically from complex data) that can be recalled more quickly when needed. This approach can significantly improve CPU and I/O performance when running SQL workload, and also reduce scans and sorts to improve performance. The reduction in related performance overhead can even help some companies avoid or put off the need to upgrade CPU capacity.
Of course, managing the materialized query tables requires disk space, so DBAs should think carefully about whether the trade off is justified. For more information, have users check the current DB2 Administration Guide.
Add “expert” tools
Users can further streamline and reduce the time spent on database performance management tasks by adding a new generation of “expert” tools. These products go far beyond the rudimentary software commonly used by programmers and DBAs to retrieve the performance indicators that allow them to analyze problems manually. Today’s advanced database management tools can provide in-depth metrics, analysis, and advice to help maximize SQL, table, index and other database performance. When appropriate, some tools automatically implement these actions to greatly reduce manual effort.
Proactive management: Tools that help developers write efficient SQL statements by finding the fastest and best path to the data can significantly improve workload performance. Look for software that can recommend which statistics to collect or update to help with that streamlining. Your company may also benefit from software that estimates the resource costs of SQL statements without actually executing those statements, and has the ability to reduce those so-called costs. You’ll also want to consider smart tools that can automatically help implement their own recommendations to further reduce manual effort.
Some tools offer advice to improve SQL queries, and automatically rewrite those queries if need be. Effective software will also alert users to access path problems that can hinder performance. They can recommend indexes that will enhance SQL performance and significantly reduce the time a DBA or programmer spends on these functions. Choosing tools that also help users tune related workloads for peak performance remains crucial. Look for software that proactively detects and prevents problems.
The use of database performance monitoring and tuning tools allows DBAs and developers to quickly identify and rectify performance issues. However, the investment in software tools can only pay dividends if companies establish standards to enforce their use.
Taking a more strategic approach to managing DB2 performance within the System z environment requires a focused effort from your production and development teams. But in the long term, the strategy can free your DBAs to spend more of their time advancing the needs of your business and the bottom line.
For more information:
IBM DB2 Administration Guide
IBM DB2 Optimization Expert for z/OS
DB2 SQL Performance Analyzer for z/OS
IBM DB2 Query Monitor for z/OS
|
|
|
