As much as we hate to hear it, there’s no silver bullet for improving overall large system performance. We can tune the DB2 subsystem parameters, logging and storage, but often we’re only accommodating a bad situation. Here, the “80/20” rule applies; you’ll eventually have to address application performance.
There’s a way to quickly assess application performance and identify the significant offenders and SQL statements causing problems. You can quickly identify the “low-hanging fruit,” report on it to your boss, and change the application or database to support a more efficient path to the data. Management support is a must, and an effective manner of communicating performance tuning opportunities and results is crucial.
Setting the Appropriate Accounting Traces
DB2 accounting traces will play a valuable role in reporting on application performance-tuning opportunities. DB2 accounting traces 1, 2, 3, 7, and 8 must be set to monitor performance at the package level. Once you do that, you can further examine the most expensive programs (identified by package) to look for tuning changes. This reporting process can serve as a quick solution to identifying an application performance problem, but can be incorporated into a long-term solution that identifies problems and tracks changes.
There’s been some concern about the performance impact of this level of DB2 accounting. The IBM DB2 Administration Guide states that the performance impact of setting these traces is minimal and the benefits can be substantial. Tests performed at a customer site demonstrated an overall system impact of 4.3 percent for all DB2 activity when accounting classes 1, 2, 3, 7, and 8 are started. In addition, adding accounting classes 7 and 8 when 1, 2, and 3 are already started has nominal impact, as does the addition of most other performance monitor equivalent traces (i.e., your online monitor software).
Summarizing the Accounting Data
To effectively communicate application performance information to management, the accounting data must be organized and summarized up to the application level. You need a reporting tool that formats DB2 accounting traces from System Management Facilities (SMF) files to produce the type of report you’re interested in. Most reporting tools can produce DB2 accounting reports at a package summary level. Some can even produce customized reports that can filter only the information you want out of the wealth of information in trace records.
You can process whatever types of reports you produce so that a concentrated amount of information about DB2 application performance can be extracted. This information is reduced to the amount of elapsed time and CPU time the application consumes daily and the number of SQL statements each package issues daily. This highly specific information will be your first clue as to which packages provide the best DB2 tuning opportunity. Figure 1 shows a typical package-level report and the fields of interest.
If you lack access to a reporting tool that can filter out just the pieces of information desired, you can write a simple program in any language to read the standard accounting reports and pull out the information you need. REXX is an excellent programming language well-suited to this type of “report scraping,” and you can write a REXX program to do such work in a few hours. You could write a slightly more sophisticated program to read the SMF data directly to produce similar summary information if you wish to avoid dependency on the reporting software. Once the standard reports are processed and summarized, all the information for a specific interval (say one day) can appear in a simple spreadsheet. You can sort the spreadsheet by CPU descending. With high consumers at the top of the report, the low-hanging fruit is easy to spot. Figure 2 shows a spreadsheet derived from the summarized package-level accounting report.
Looking for some simple issues will help you to identify the first programs to address. For example, package ACCT001 consumes the most CPU per day, and issues nearly 2 million SQL statements. Although the CPU consumed per statement on average is low, the sheer quantity of statements issued indicates an opportunity to save significant resources. If just a tiny amount of CPU can be saved, it will quickly add up. The same applies to package ACCT002 and packages RPTS001 and RPTS002. These are some of the highest consumers of CPU and they also have a relatively high average CPU per SQL statement. This indicates there may be some inefficient SQL statements involved. Since the programs consume significant CPU per day, tuning these inefficient statements could yield significant savings.