Raising the Performance Bar

Heightened DB2 11 for z/OS performance optimizes SQL statements, automates data archive management, and more

A previous installment in this series describes the technology through which IBM® DB2® 11 for IBM z/OS® data management raises the bar on high availability.1 In a similar vein, DB2 11 enhancements also raise the bar on performance. DB2 11 for z/OS takes a same-song-second-verse approach in the way it builds on the performance gains provided by DB2 10.

Prior to the release of DB2 10, and going back to Version 3 in the early 1990s, succeeding releases of the database management system essentially held the line with respect to processor efficiency. That is, each successive release offered significant functional capabilities without requiring additional processor overhead.

DB2 10 helped raise the performance bar by offering both enhanced functionality and helping significantly reduce processor cost for data access. DB2 11 raises the bar a bit further. Through optimized SQL statements and expanded synergy with the IBM System z® server, DB2 11 is designed to offer advanced functions such as autonomous transactions, global variables, Apache Hadoop integration—and with optimized processor utilization for a given operation.

Smart SQL optimization

The SQL optimizer, which has evolved with every release of DB2 for z/OS, is the part of DB2 that determines how data is accessed so that SQL statements run as quickly and efficiently as possible. DB2 11 offers many noteworthy optimizer-related enhancements, including the following:

  • Expanded capabilities for automatically rewriting queries to help boost performance
  • Enhanced leveraging of in-memory work areas to help speed query execution
  • Highly effective use of indexes to help eliminate duplicate rows in a query result set without requiring a sort operation
  • Efficient data access for queries that target range-partitioned tables

These SQL optimizer enhancements are available with DB2 11 running in conversion mode—the initial state of a DB2 system following migration from a prior release. For dynamic SQL statements—that is, SQL statements prepared for execution at the time they are issued—smarter optimization occurs automatically when the statements are executed in a DB2 11 system. Dynamic SQL statements are typically SQL-issuing programs written in languages such as C#, Java, PERL, Python, and Ruby.

Static SQL statements such as statements prepared for execution through a process called binding, prior to being issued by an application program, help achieve enhanced optimization by rebinding a program’s SQL statements in a DB2 11 system. This process does not involve any application code changes. Various types of COBOL programs—which could be DB2-stored procedures—and Customer Information Control System (IBM CICS® transaction server) transactions, Information Management System (IBM IMS™ transaction server) transactions, and batch jobs often issue static SQL statements.

Hardware synergy

The enhanced performance of DB2 11 stems in part from an enhanced degree of exploitation by DB2 of technology built into IBM System z servers. DB2 11 deployed on System z delivers improved application performance through several hardware-related features, including:

  • Efficient access to compressed data: Hardware-assisted data compression has been a widely exploited feature in DB2 for z/OS systems for quite some time. In a DB2 11 system, the selective decompression feature provides access to compressed data and enhances processor utilization. Prior releases of DB2 had to decompress an entire row, even if only a few columns in a compressed table needed to be returned to a program or examined to determine a row’s inclusion in a query’s result set. DB2 11 now decompresses only the parts of a row that require decompression, either to be returned to a program or to be tested against a query’s search arguments. And enhanced processor utilization that is activated through rebind for existing static SQL statements can be significant. This enhanced utilization is especially evident when a query references only a few columns of a compressed row in a table with many columns and when DB2 examines many rows in a compressed table space while executing a query.
  • Exploitation of large server memory resources: Mainframe memory sizes continue to expand. DB2 11 provides advanced opportunities to help boost processor efficiency through heightened use of large page-frame sizes, including support for 2 GB page frames that can improve performance for very large buffer pools. A page frame is a unit for managing System z server memory.
  • Enhanced performance for Java stored procedures: Java is widely utilized in the development of enterprise-class applications, but there is a lingering misconception that System z and z/OS are not well suited as platforms for Java program execution. However, multiple enhancements over the past several years have made System z with z/OS an excellent platform for deploying Java programs. DB2 11 enhances scalability and processor efficiency with an advanced Java Virtual Machine (JVM) for Java stored procedures that package DB2 data access logic. A single, multithreaded 64-bit JVM is used by all Java stored procedures running in an address space. In contrast, previous DB2 releases had a 31-bit JVM for each stored procedure executing in the address space.

Data archive management

An IBM Data magazine article several years ago focused on the possible performance benefits of data archiving in a DB2 for z/OS environment.2 DB2 11 can now automatically manage data archiving on a table-by-table basis, based on the needs of an organization. There are also data sharing performance enhancements, an enhanced System z Integrated Information Processor (zIIP) engine offload for cost savings, temporary table processing efficiencies, streamlined communication for client-server applications, fast utilities, and more.

Like its predecessor version, DB2 11 for z/OS is designed to deliver significant system and application performance gains for organizations migrating from the previous release of the software. DB2 11 provides these performance improvements without requiring application code changes. Organizations can merely migrate the system to DB2 11 and run dynamic SQL-issuing programs—and/or rebind static SQL statements. Altogether, SQL optimization in DB2 11 is designed to deliver business-critical information cost-effectively.

Please share any thoughts or questions in the comments.

1Raising the Availability Bar,” by Robert Catterall, IBM Data magazine, February 2014.
2When Data Wears Out Its Welcome,” by Robert Catterall, IBM Data magazine, April 2010.