In the z/OS environment, tuning a DB2 subsystem may require the DBA and systems programmers to wear multiple hats and approach overall performance tuning from several different angles. What approaches are best in what situations?
Strategy Determines Tactics
The strategy you choose for general DB2 systems performance tuning will usually determine the tactics that work best in your environment. There are three approaches to tactical performance tuning:
- Application tuning
- Component or object tuning
- Resource tuning.
Tactical application tuning usually occurs in three separate areas:
- Batch tuning: Many IT organizations run batch jobs. Sometimes, they're run whenever needed, regardless of the status of any Online Transaction Processing (OLTP). Others will segregate jobs into a batch window, usually of limited duration. Historically, the duration of the batch window has decreased over time, usually due to companies being more global, and not being limited to daylight or business hours. Batch tuning usually centers around reducing job elapsed times. The most common methods are by introducing parallelism (both with CPU and I/O), by physical data redesign (e.g., horizontal or vertical partitioning), or by introducing Database Management System (DBMS) structures that reduce processing time such as additional indexes or Materialized Query Tables (MQTs).
- Online tuning: Online applications have evolved from mainframe-centric IMS and CICS to distributed n-tier client/server systems using object-oriented languages. Here, the focus is on removing bottlenecks and introducing failover points and redundancies. Typical strategies include reducing network flows, moving SQL closer to the DBMS, and introducing asynchronous processing or messaging. Common tactics include balancing dynamic SQL and stored procedures and using MQSeries messages to queue requests to processes that may fail (requeuing unprocessed messages).
- SQL-centric tuning. Here, the DBA or developer concentrates on analyzing SQL statement access paths. While you sometimes encounter big wins (such as converting a tablespace scan into a one-fetch index lookup), the more common goals are use of the proper join method, avoiding (or encouraging) sorts, determining the best clustering sequence, and optimizing index usage. The tuner will use one or more EXPLAIN tools to measure access paths, then make adjustments using techniques such as advanced data distribution statistics, re-clustering or partitioning tables, adding, deleting, merging, or re-defining indexes, and sometimes re-writing the SQL statement.
These types of tuning concentrate on applications and are best-suited for environments where service-level agreements (SLAs) drive tuning needs. They also occur often in reactive environments, as poorly executing applications can be easily singled out. While they may result in impressive one-time gains, you should also make an effort to review the total systems performance impact before making any major changes or enhancements.
With tactical component or object tuning, the tuner concentrates on specific database objects. There are many different objects, and you may wish to concentrate your efforts on one or more of these. These tuning tactics are usually encountered in IT environments where staff have some experience in systems tuning, or have attended recent presentations on tuning topics. The risks associated with these changes are somewhat less than those previously discussed, since they usually involve analysis of multiple components. Most of these tactics will result in small but steady and measurable improvement across multiple applications:
- There's an invocation overhead of some few thousand machine instructions associated with stored procedures, so tuning tends to be centered around minimizing invocations and tuning the SQL within. Many simple distributed applications expect results returned as result sets, which sometimes leads to implementing temporary tables to hold intermediate results.
- DB2 enforces declarative referential integrity internally. Alternatives include user-managed referential integrity via application code and designer-managed via triggers. There are many performance and data integrity trade-offs among these choices. Most tuning opportunities center around creating the minimum number of indexes to support child table references during delete operations.
- Data objects usually include tablespaces, tables, indexes, partitions, MQTs, and other objects. The DBA can change the performance profile of a tablespace either by adjusting internal configuration parameters such as FreeSpace, MaxRows and Compress, by choosing the right mix of candidate indexes to support entity integrity, referential integrity, and access performance, or by choosing alternative table designs (for example, implementing large character columns as VARCHAR rather than as CLOB [Character Large Object], or by using IDENTITY columns). Other objects have similar configuration parameters or attribute options that may affect performance.
- Certain DBMS processes can be tuned, usually through DB2 subsystem configuration parameters (ZParms). The Distributed Data Facility (DDF) and virtual pools are the most common such components. DDF tuning usually centers around thread management, especially thresholds and how connectivity is managed. Virtual pool tuning includes sizing, memory management (e.g., allocating pools to dataspaces), threshold settings, and object assignment (e.g., separating tables and indexes into separate pools).
- In the operating system data environment, the tuner concentrates on performance management of the I/O subsystem. Much of this is concerned with processes waiting for I/Os to occur. Common tools include RMF and DB2PM reports such as accounting and statistics detail. Many tuning techniques center on hardware options. For example, if your DB2 data objects are stored on a Redundant Array of Inexpensive Disk (RAID) device that mimics 3390 devices, it also may include an option that allows defining multiple (logical) channel paths to the device. This may reduce I/O waits.
The resource-based approach to tuning concentrates on two aspects of the system: 1) how resource constraints cause bottlenecks, and 2) how excess resource capacity can be used to break the bottlenecks.
Let's assume the elapsed time for a particular online transaction is several seconds and your goal is to get turnaround time to be less than a second. After some analysis, you determine that the application frequently inserts and updates rows in one small, highly volatile table. Further, you note the transaction executes often during times when your system is CPU-constrained, and there's evidence of the DB2 DBM1 address space waiting for CPU dispatch during that period.
In this case, the resource bottleneck is CPU cycles. You must determine if there are any other excess resources available, and then develop one or more methods for “trading” those resources for the CPU cycles. It's possible this volatile table was implemented using data compression (COMPRESS YES). This option is usually used to reduce the total size of a table, while using CPU cycles to compress and decompress rows for storage and retrieval. By removing the compress option and expanding the table, you may reduce the CPU time used by your application for row compression and decompression. However, the total size of the table (both on DASD and in memory in the virtual pools) will be larger. So, this is a trade-off between a constrained resource (CPU) and an available resource (DASD).
Using resource constraint analysis is an iterative, mostly proactive process. It's typically used as part of a strategy for understanding system wide resource usage and for developing application-tuning methods that take into account the system as a whole.
There are several performance-tuning approaches available for the DB2 DBA and systems programmer. Circumstances may force you to begin with a reactive approach, concentrating solely on SQL and application tuning. This may produce remarkable one-time improvements, but your future efforts require a long-term strategy.
Such a strategy may lead you to additional tactical component tuning, where you concentrate your attention on DB2 objects such as tablespaces, indexes, stored procedures, and the like. While these efforts may produce results, they still force you into the context of reacting to symptoms and to focusing on DB2-specific entities.
You should pursue a strategy that relies on system resource constraint analysis. This approach focuses on measurement and analysis of system resource bottlenecks and excess capacities, which you can then apply to tuning DB2 processes. This strategy is more proactive and can be extended beyond DB2 to other subsystems such as CICS, IMS, and others.