Today, many of us are managing DB2 running on some combination of z/OS, Linux, Unix, and Windows. Wringing optimal performance from DB2 applications is always more than simply tuning SQL; working with DB2 on different platforms complicates things. How do factors such as memory, physical design, and maintenance affect performance on different platforms? If your DBA background is primarily on one platform, how can you leverage your knowledge to other platforms? The goal of this article is to help answer those questions so you can achieve your goals of improving availability, increasing transaction throughput, and lowering response times. This article assumes a basic understanding of the DB2 terminology differences between z/OS and LUW (Linux, Unix and Windows), which are numerous.
Begin With Monitoring
Monitoring is the all-important prerequisite for tuning and maintaining a subsystem or instance. Consider the big picture of your DB2 ecosystem: Memory, physical design and SQL transactions all work in conjunction and can directly impact performance. While proper SQL coding is critical, the best-written statement won’t perform properly if there are inadequate memory allocations or a poor physical database design.
Monitoring methods for z/OS subsystems differ from those of an LUW instance. On z/OS, the most commonly used traces are the statistics and accounting classes. The statistics class gives you information at a GLOBAL level, summarizing total activity in a subsystem. The accounting class traces give you detailed performance metrics at the individual thread level. The accounting class traces give you a good level of detail for monitoring applications. Both these classes have relatively low overhead and are typically continuously on. You can typically diagnose 80 to 90 percent of your performance problems using the information provided with these traces.
When a severe performance problem occurs and you need greater detail, you can use the performance class traces. Do so with caution, however, because they can incur extreme overhead. It’s best to qualify the trace as much as possible to limit the scope of information collected. On LUW, there are two ways to monitor a DB2 instance: the snapshot monitor and event monitor.
Snapshot monitor records activity at a specific time (and so only collects performance information for transactions active at that time). It provides a great level of detailed performance information with relatively low overhead and is most useful for diagnosing immediate performance problems.
Event monitors provide the ability to collect activity over time. They collect specific events such as deadlocking, connection activity, SQL statements, etc., over a historical period. This provides the ability to do workload analysis for tracking trends and problematic transactions.
Managing Memory Management
An important element of performance tuning is to minimize physical I/o by properly allocating memory. Inadequate sort, buffer, or system memory can hamper even the best-written SQL statement. DB2 environments tend to change due to increases in data volumes, transaction rates, numbers of users, etc., so memory usage needs to be regularly monitored. Figure 1 lists memory areas that can directly affect performance.