DB2 11 for z/OS: Technical Overview

by John Campbell and Gareth Jones

This IBM® DB2® for z/OS® white paper provides a high-level overview of the changes introduced in DB2 11 for z/OS, including the following topics:

• DB2 11 performance expectations and improvements

• Availability and resilience enhancements

• Data sharing improvements

• Security enhancements

• Utility enhancements

• Analytics improvements

• New and enhanced application features

• Easier version upgrade, including the new Application Compatibility feature

The performance expectations section concentrates on improvements that can be expected with and without REBIND and describes which improvements require DBA or application programmer effort. We discuss other features in more detail in subsequent sections.

DB2 11 Performance Expectations

IBM recognizes that performance improvements can also result in cost savings for customers, making the IBM System z® platform more attractive and helping customer investment to deliver value.

DB2 10 for z/OS provided some significant application performance improvements by reducing CPU consumption for many online transaction processing (OLTP) applications running simple SQL queries. This theme continues in DB2 11 for z/OS, with the focus now on complex queries. In this paper, "simple queries" are queries that retrieve data using a primary key lookup; for the purposes of this discussion, you can regard all other queries as complex queries. While some performance regression for a small number of queries is possible, most DB2 11 customers can expect to see reduced CPU consumption for a significant proportion of their complex queries.

The performance improvements customers can expect from DB2 11 might vary depending on many factors. Changes to the DB2 Optimizer mean the access path chosen by the DB2 11 REBIND process could differ from that chosen by DB2 10. For example, DB2 11 can choose a nested loop join instead of a sort merge join, or vice versa. The read/write ratio is also important because DB2 11 reduces the logging overhead for write-intensive applications.

Other factors affecting performance expectations include the number of rows returned, the number and type of columns returned, the number of partitions touched, and the number of partitions defined. You are more likely to see performance improvements when using table-controlled partitioning and data-partitioned secondary indexes (DPSIs) because IBM has worked to make DPSI much more useful in this release. The BIND option RELEASE(COMMIT/DEALLOCATE) and the use of table compression are two other important factors influencing the kind of performance improvement you can expect.

Customers often want to know what enhancements they can anticipate from any new release of DB2, so it is important to be clear about which enhancements are immediately available. First, there are no Data Definition Language (DDL) changes, no Data Manipulation Language (DML) changes, and no application changes. However, this does mean that achieving the most significant performance gains for static SQL packages require a REBIND. Additional performance savings require user action in the form of DDL or DML changes or other DB2 changes.

Performance Expectations for OLTP and Batch

Table 1.1 shows the OLTP and batch CPU savings reported from IBM's own internal benchmarks. In these benchmarks, System z measures total CPU consumption — that is, the CPU consumption reported in the statistics trace as well as the accounting trace. This point is important because when you deploy DB2 11 and measure your own performance improvements, you'll need to make sure you look at the complete picture by including CPU consumption figures from both statistics and accounting.

These figures demonstrate that positive CPU savings apply across a broad range of SQL query workloads. The modest 1 percent improvement seen for the local, non-distributed workload running simple SQL queries in a data sharing environment is expected, given that in this release IBM focused on reducing CPU consumption for complex queries. However, a healthy reduction in CPU consumption is reported for the other workloads, including the IBM utility set.

Performance Expectations for Queries

The figures in Table 1.2, representing a variety of industry-standard benchmarks and customer workloads executing complex queries, are certainly impressive. Note that these workloads include not only business intelligence but also complex OLTP and batch.

Some of these workloads use static SQL, and for purposes of the test, the containing DB2 plans were rebound without APREUSE under DB2 11, opening up new and improved access path choices for these applications. Of course, those choices are automatically available to dynamic SQL at PREPARE time. Although most performance improvements are available even after a successful REBIND with APREUSE(ERROR) or APREUSE(WARN), you must rebind without APREUSE to get the new or improved access paths.

It is also important to understand that these are the sorts of workloads that are expected to benefit significantly from DB2 11. The savings you see might differ. Underlying the variation in CPU savings for these workloads is the fact that the functional usage of SQL varies from workload to workload; the savings you can expect will depend on the characteristics of the SQL requests issued by your applications and the design of your database schema.

DB2 11 Performance Expectations Summary

In summarizing the kind of CPU savings expected with DB2 11, you will notice we use the phrase "Up to" — which includes the value zero. For example, "Up to 10 percent for complex OLTP" should be understood as "From zero to 10 percent for complex OLTP." We discussed the reasons for this convention earlier, but, essentially, the savings you can expect are very workload-dependent.

To summarize, the total CPU savings you can expect for your SQL applications in DB2 11 are as follows:

• Up to 10 percent for complex OLTP

• Up to 10 percent for update-intensive batch

• Up to 25 percent for reporting queries without compressed tables

• Up to 40 percent for complex queries with compressed tables

Performance Highlights

In this section, we highlight some of the most significant performance improvements in DB2 11 for z/OS. Some of these might require user action, while others might not.

Two of the performance improvements can particularly help write-intensive batch applications — that is, applications that use INSERT, UPDATE, and/or DELETE intensively.

The first improvement in this area does not require REBIND because it is related to the log output buffer, which has been moved from the MSTR address space to the 64-bit common area. The advantage of...