Jul 28 ’09

Approaches to DB2 Tuning: What’s the Best Use of Your Time?

by Editor in z/Journal

Assume you’ve been given a directive by IT management to “tune” things. What strategies are available and how do you judge which ones are best? Considering the current economic climate, can any be implemented on a shoestring budget? Can any of these tasks be assigned to resources with basic DBA skills and still be productively accomplished?

A few of the many strategies for approaching and overseeing tuning efforts include:

• Tune from top to bottom. Tune z/OS (i.e., memory, dispatching priorities, WLM environment), then the DB2 subsystem, database objects (i.e., table spaces, indexes), then application systems, individual programs, and SQL. The assumption is that low-level tuning (e.g., SQL statement tuning) can’t succeed unless high-level systems are already tuned.

• Tune from bottom to top. This is the reverse of the above strategy. Start with tuning individual SQL statements, then application programs, and so forth. The assumption here is that most performance issues are due to inefficient SQL statements, poorly written applications, or poor database design.

• Tune the sacred cows. Direct all tuning efforts at those applications and systems most critical to the business.

• Tune the squeaky wheels. Spend time reacting to those customers that complain the loudest.

There are other strategies; perhaps you’re already using one or more of them. Most of these tuning strategies require higher-level DBA skills, teamwork, and coordination with other areas. However, in the current economic environment, IT management may not have the luxury of allocating sufficient DBA resources to tuning efforts that don’t produce immediate, quantifiable results. With limited time and resources you will need to focus on strategies that provide tangible benefits at a minimal cost. Goals to keep in mind are:

• Ease of cost justification. Task time and effort (and therefore cost) should be easy to estimate; results should be quantifiable.

• Little conceptual training required. No in-depth DB2 knowledge should be involved in implementing the tuning tactics.

• Short learning curve. DBAs shouldn’t have to spend a lot of time learning new functions and features or attending training.

• The presence of heuristics or best practices. There should be simple, straightforward methods of implementing tasks, including standards and best practices.

With these objectives in mind, this article takes an in-depth look at the following five tuning strategies:

• SQL tuning involves SQL review and knowledge of potential access paths, table and index data distribution, and statistics.

• Resource constraint tuning is an analysis of possible trade-offs among CPU, elapsed time, I/O, memory, network traffic, and other resources.

• Application tuning focuses attention on suites of programs that access particular tables, batch windows, and online service levels.

• Object tuning concentrates on the general definitions and configurations of tables, indexes, stored procedures, and other database objects.

• System tuning deals with the DB2 DBMS infrastructure that supports all of these strategies.

How do you tell what kinds of tuning are most needed in your environment? First, let’s discuss typical symptoms.

SQL issues always exist, although specific symptoms may be difficult to detect. Typical causes range from poor coding techniques, poor understanding of SQL coding standards, lack of understanding of the data model, and so forth.

Resource constraint issues commonly arise when a need is felt for additional CPU or DASD, or Service Level Agreements (SLAs) aren’t being met.

Since SQL issues and resource constraint issues are common, they’re natural areas for the new DBA to begin tuning.

Addressing application issues requires a more broad knowledge of application behaviors, access paths, batch windows, DB2 configuration parameters, and how all those tie together. There’s a learning curve, but the necessary information should be documented—somewhere. Typical symptoms include complaints about high-usage resources, application delays due to deadlocks and timeouts, and missed service levels.

Object issues deal with the enterprise data model and how database objects (usually table space partitions and indexes) are created and configured. This requires the DBA to do a competent analysis of several areas.

Systems issues can be extremely complex and require the DBA to have in-depth knowledge of DB2, other z/OS subsystems, hardware and software; this work isn’t suitable for a new DBA.

Let’s look at each of these categories in more detail, summarizing the areas for analysis, the tools you will use, the biggest productivity gains, and recommended first steps. You can then decide which strategy (or strategies) is best for implementation in your environment.

SQL Tuning

SQL tuning is probably the easiest tuning approach. It doesn’t require lots of DB2 systems expertise and uses minimal tools. You can get by with simple SQL statements executed from Query Management Facility (QMF) or SQL Processor Using File Input (SPUFI). In DB2 Version 8, you can use the Explain facility on either a single SQL statement in the dynamic SQL cache or on the entire cache, with access path information going into a DB2 table for later analysis. See the Application Programming Guide and the SQL Reference for more information.

What you analyze: access paths, object statistics.

Using what tools: Basic Explain, or perhaps a software tool that provides a user-friendly front-end to explain information. You also may run DB2 catalog management reports or queries, and even have access to a DB2 object statistics display or monitoring tool. One common example is the IBM Optimization Service Center.

Where to look for the largest productivity gains: For SQL coding, user training provides the best return on investment. Provide developers with clear, consistent SQL coding standards, coding examples, typical ways of accessing common tables, and descriptions of indexes and their uses for query performance. If possible, give them access to the Explain facility or the Optimization Service Center so they can analyze access path information themselves.

First steps: To get started, you’ll need several things. SQL coding standards are a good start. Also useful is a “Top-50 SQL” report, which lists the 50 (or whatever number you deem best for your environment) longest-running SQL statements. This data is available in Service Management Facility (SMF) records, and can be limited and filtered by DB2 environment (i.e., development, test, stage, production), by application, or other criteria. SMF reports are described in the IBM publication, IBM DB2 Performance Expert for z/OS Report Reference (SC18-7978). Regularly run these reports and review the SQL statements. Poorly performing statements are candidates for tuning, while those already addressed can be optionally excluded from later reports.

Finally, you’ll need a process for regular Explains and reviews of production SQL statements. One common tactic for static SQL statements is to bind all plans and packages with EXPLAIN(YES), ensuring you have current access path information available. After that, develop processes for detecting production access path changes and reviewing potential performance problems.

Resource Constraint Tuning

There are always perceived resource constraints, so there are always opportunities for resource constraint tuning. Most of this tuning revolves around “trading” short-term constraints for other resource availability. The usual trade-offs are among CPU, elapsed time, I/O, and memory usage. For more on this, see “The Shifting Sands of DB2 Performance Tuning” in the August/ September 2006 issue of z/Journal.

What you analyze: Memory usage in the DB2 virtual pools, CPU usage, the I/O subsystem, application throughput, and data availability.

Using what tools: The primary tuning tools are the performance reports available from SMF records, particularly the Accounting Report. You also may use the Resource Management Facility (RMF), but that tool requires more expertise.

Where to look for the largest productivity gains: This depends on your particular resource constraints. A few common trade-offs include:

• CPU for DASD. DASD allocations for large table space objects can be reduced by implementing data compression. This occurs during table space creation by using the COMPRESS YES parameter. Most character data and some numeric data can be stored in a compressed format; this shortens table rows. The trade-off is that some additional CPU may be consumed by compression and decompression of the data; the exact tradeoffs depend on your CPU configuration.

• DASD for elapsed time. It may be possible to shorten the execution elapsed times of SQL statements by adding indexes. One way is to specify an index containing sufficient table columns to allow “index-only” access paths for some queries. Of course, the additional indexes will require more DASD space. They also may result in longer-running Reorg and Load utilities and could affect any data recovery situation.

These examples of resource constraint trade-offs are generic; they may not work in some situations and may actually hurt performance. Nothing substitutes for a complete, careful analysis of the costs and benefits of such changes.

First steps: Generate resource-related performance reports from SMF records, particularly Top-n reports of CPU consumed, synchronous I/O waits, and elapsed times. Choose what window (e.g., Top-20, Top-50, etc.), what environment (e.g., development, test, etc.) and what intervals (e.g., daily, weekly) to report. Concentrate on studying the worst offenders. Remember that some long-running or resource-intensive processes—such as those that process large amounts of data or do extensive data analysis—won’t benefit from tuning. After you choose some resource “offenders,” direct your analysis toward possible resource trading as previously described.

Application Tuning

Application tuning isn’t complex, though it encompasses many things. This type of tuning requires knowledge of multiple user applications and their characteristics, such as table access, batch windows, restart requirements, SLAs, and relevant DB2 configuration parameters.

What you analyze: The short list includes online transaction processing, data warehouse activity, vendor software package performance, deadlocks and timeouts, bind parameters, information in the DB2 subsystem Job Entry Subsystem (JES) log, units of work, commit frequency, referential integrity, and batch cycles. Each category provides a window into certain performance characteristics of an application or suite of applications.

Using what tools: Your most important tool will be the performance reports generated from SMF records. The Accounting reports are useful for analyzing specific plans and packages while the Statistics reports show system-level information. In addition, you should have available documentation related to standards for application unit-of-work and commit frequency and an enterprise data model (or, at a minimum, data models for the objects accessed by the applications you’re reviewing). You should create these if you lack them.

Where to look for the largest productivity gains: The best places for productivity gains are in addressing issues with SLAs. Often, this is related to either elongated elapsed times (especially for batch processes) or resource-intensive queries in an online environment. For long elapsed times use the performance reports to determine where the time is being spent. If the time is spent waiting for resources, consider ways to reduce the wait time. For resource-intensive queries consider ways to “trade” other available resources for the constrained resource, as previously discussed.

First steps: Develop Top-n reports for high-resource usage among your batch jobs, online transactions, and stored procedures. These can then be dealt with individually or in groups. You may discover that several of your top long-running processes execute similar SQL queries against the same tables. You must then investigate more deeply to determine if this is due to poorly written SQL, highly volatile resources (where deadlocks and timeouts elongate elapsed times), unavailable resources, or other causes.

Object Tuning

Object tuning will involve one or more of the following: the enterprise data model, DB2 object creation parameter settings, standards for executable code, and options for enforcement of business and data integrity rules. Here, the focus is on object definitions and data availability. This requires an indepth knowledge of the various parameters and settings that control how and where objects (e.g., table space partitions, indexes, stored procedures, etc.) are created.

What you analyze: A typical list would include index management, table space partitioning, object configuration parameters (e.g., DASD allocations, embedded free space, etc.), data set placement, data modeling, and referential integrity.

Using what tools: For object tuning, there are few no-cost tools. Some software vendors provide “advisors” that make recommendations on objects, and some data modeling tools can be configured to generate a Data Definition Language (DDL) for objects that follow certain standards. After that, the DBA is probably reduced to using whatever tools (or raw SQL statements) are available that can query the DB2 catalog tables to get object definitions.

Where to look for the largest productivity gains: Few specific tactics apply to all IT environments. Much depends on your standards and how consistently they’ve been applied over time. Many object-related tuning issues can be traced to one or more of the following:

• Poor choice of clustering index. Often, the data architect must create a database design without knowledge of the SQL to be used against the tables. The result is indexes that support only the primary key and uniqueness constraints, without any consideration for query performance. Tables with a single index are considered to be clustered on the keys of that index, with some exceptions. Clustering by primary key isn’t always the best design for performance. To learn more, see the “Performance Monitoring and Tuning” chapters in the DB2 Administration Guide.

• Poor choice of table space partitioning scheme. Many data architects don’t consider physical partitioning schemes because they’re physical, not logical. However, physical partitioning can be used in many instances to increase performance.

A few examples:

1) Partition by date can make purging of old or unneeded data a simple matter of emptying a partition rather than executing a long row-byrow purge process; 2) Utility processing, where reorgs and recovery can occur on a partition basis; and 3) Partition by one key, cluster by another; this is table-based partitioning, introduced in DB2 V8 (see the manuals for specifics).

• Poor choice of indexes to support queries. Often, the data architect or DBA will create indexes they feel will provide good performance. However, without specific measurements of what indexes are used and how often, it’s common to end up with either too many indexes unrelated to specific performance requirements or unused indexes.

• Lack of resource consumption and resource cost metrics. DBAs sometimes spend time and resources tuning something that ends up “better” but with no way to show that they tuned the right thing.

First steps: Along with establishing clear standards for object creation and consistently applying them, the most important steps are to research and generate a cross-reference of objects accessed by plan and package, including type of access. This is commonly called a CRUD (Create, Read, Update, and Delete) matrix or, more properly, a data-to-process CRUD matrix. The CRUD matrix allows a multi-dimensional analysis of object access. One can analyze the set of processes that access one or more objects, or the objects accessed by one or more processes. This allows the analyst to understand possible points of contention and pinpoint objects whose allocation and configuration should be considered in more detail.

Systems Tuning

Systems tuning is more enterprise-based than application-based. It involves coordinating installation, configuration, and tuning of DB2 subsystems, hardware and devices, and the interface with z/OS. It also requires knowledge of some relatively complex reports, including RMF reports. A new DBA would be unlikely to be productive in this area because the work requires considerable training and experience.

What you analyze: TCP/IP, virtual pools, logging, recovery, DB2 maintenance, DB2 configuration parameters (ZParms), the Internal Resource Lock Manager (IRLM), and the z/OS interface. Additional information is available from traces that can be turned on in the DB2 environment.

Using what tools: System-related performance reports from SMF records, RMF reports, and System Modification Program/Extended (SMP/E) reports.

First steps: Disaster recovery planning, recovery standards and jobs, software maintenance strategy.

Where to Begin

No matter what areas of tuning you decide to begin with, one thing is certain— you’ll have a lot of work to do! To reduce your workload, concentrate on automating any reporting or analysis processes. A good place to begin is to set up, review, and automate your “Top-n” reports.

Choose the report or reports relevant to your tuning area and implement a regularly scheduled process to produce the reports. Schedule regular meetings with the appropriate people to review the results

For example, if you’re concentrating on SQL tuning, set up regular reports of your Top-n SQL statements. Choose your criteria for reporting: Total elapsed time, total CPU time, and total synchronous I/O wait time are common items. Run these reports daily or weekly and review them with your peers or with leaders in application areas.

Regularly run and review available health checks or develop your own. Develop plans to address issues when they arise.

Even the most frugal shops have helpful tools. Discover what IBM and third-party tools exist and determine their utility for your needs. Pick what looks like the most useful tool and learn it.