IBM DB2 12 for z/OS is ready for Hybrid Transactional/Analytical Processing
1/11/2017 12:00:13 AM |
By Gareth Z. Jones
IBM DB2 12 for z/OS became generally available on Oct. 21, 2016. It's perhaps sobering to reflect on the fact that DB2 was first announced in 1983 and released in 1985, but its roots—and the roots of all relational databases—go all the way back to mathematician and IBM Fellow Edgar F. Codd's ground-breaking 1970 paper, "A Relational Model of Data for Large Shared Data Banks."
Many major enhancements have been introduced since those early days, to radically transform DB2 for z/OS into the premier database for reliability, scalability and availability it is today, with support for modern application programming paradigms and both non-relational and relational data. Updates by version include:
- DB2 V2.1: DB2-managed referential integrity (RI)
- DB2 V2.2: Initial distributed database support with private protocol (DB2 for MVS to DB2 for MVS)
- DB2 V2.3: Support for packages, and strategically important distributed database support for Distributed Relational Database Architecture
- DB2 V4.1: Most significantly data sharing, but also stored procedures
- DB2 V5.1: Online REORG
- DB2 V6.1: Triggers, large objects and user-defined functions
- DB2 V7.1: Unicode support
- DB2 V8.1: On-line schema evolution and 64-bit virtual storage
- DB2 9: The Universal Table Space (UTS) with partition by range and partition by growth table spaces, the native XML data type and native SQL stored procedures
- DB2 10: Large-scale 64-bit architecture exploitation, leading to almost complete elimination of virtual storage constraint, temporal tables, and performance improvements for online transaction processing (OLTP) queries
- DB2 11: performance improvements for more complex queries, transparent archive, JSON support
Trimmed down as it is, that's an intimidating list of enhancements, so how does DB2 12 match up to its predecessors? That's what we'll be exploring, starting with an overview of the themes and a look at some of the highlights of the new release, including the high-level performance expectations. We won't go into the technical details here—we'll cover those in a series of later articles.
When planning for DB2 12, DB2 for z/OS development set themselves a series of goals, based around four broad themes:
- Application enablement
- Database administrator (DBA) function
- OLTP performance
- Query performance
These themes propel DB2 for z/OS, combined with the IBM DB2 Analytics Accelerator for z/OS, into the era of the Hybrid Transactional/Analytical Processing (HTAP) database.
DB2 development set about addressing a number of key customer requirements to expand the use of the existing features of DB2, as well as delivering mobile, hybrid cloud and DevOps enablement. To continue the HTAP journey, DB2 Analytics Accelerator functionality is extended to support an increased number of use cases, and a number of incremental improvements in the SQL and SQL/PL areas make DB2 ready for the next wave of applications.
Even with the existing capability to grow partitioned table spaces to 128 TB (the actual limit is dependent on page size and partition size or DSSIZE), some customers have been constrained by table and partition scalability limits. DB2 12 raises the current limit to an incredible 4 PB, providing capacity for many trillions of rows. Large table management is already a headache for many customers, so DB2 12 simplifies this by making it easier to add partitions (e.g., it’s now possible to insert a new partition between existing ones, complementing the existing capability to add partitions at the end of the table space).
Although DB2 availability is second to none, DB2 12 removes some of the biggest remaining inhibitors to 24-7 continuous availability (e.g., by ensuring tables remain available while maintenance tasks are carried out).
OLTP performance remains a key requirement for DB2 customers, not just for improved response times, but to reduce the total cost of ownership, which is a pressing imperative for most IT organizations. Customers also need to be able to handle more throughput and higher transaction volumes. For these reasons, DB2 development set themselves a series of goals for DB2 12, building on the performance improvements already delivered in DB2 10 and DB2 11, to:
- Reduce CPU consumption in the 5 to 10 percent range by exploiting in-memory features
- Double the throughput when inserting into a non-clustered table
- Remove system scaling bottlenecks associated with high n-way systems
- Provide incremental improvements related to serviceability and availability
Query performance has become of increasing importance to customers over time, as they seek cost-effective ways to discover the valuable information often hidden in the vast amount of business and operational data. Improved analytical query performance enables them to make business decisions faster at less cost.
For DB2, query performance for online analytical processing , business intelligence and other more complex workloads has come sharply into focus customers, and DB2 12 targets four major improvements in this area, to build on the work done in DB2 11:
- A 20 to 30 percent CPU reduction for complex query workloads
- Improved efficiency delivered by reducing other resource consumption
- An 80 percent performance improvement for UNION ALL
- Simplified access path management, especially for dynamic SQL
Let’s have a look at some of the highlights of DB2 12 before moving on to discuss the high-level performance expectations in a little more detail.
Scale and Speed
DB2 development has measured over 1 million Inserts per second, and believes DB2 can scale even higher. It can also support up to 256 trillion rows in a single table, using agile partition technology.
In-memory database is a major theme for this release, and DB2 development has measured up to a 23 percent CPU reduction for index lookup with advanced in-memory techniques.
Next Generation Application Support
DB2 12 continues the journey of enabling the next generation of applications, providing JSON support, investing in mobile for z Systems to allow discovery of data as a service, enabling simpler integration and consumption, and handling up to 540 million transactions per hour arriving through a RESTful web API into DB2.
Deliver Analytical Insights Faster
As discussed earlier, response time isn’t just a requirement for OLTP workloads, but also for analytical workloads, where DB2 can deliver up to a 2x speed-up for query workloads, and up to a 100x improvement for targeted queries.
High-level Performance Expectations
This is an early view of the performance expectations for DB2 12, with a more detailed performance IBM Redbooks publication.
In the area of system and OLTP performance, the expectations are that:
- There will be a 2 to 3 percent CPU reduction without the Index In-Memory feature, the Index Fast Traversal Block (FTB) area
- There will be a 5 to 10 percent CPU reduction by exploiting Index In-Memory, the FTB area. This will be discussed in a later article.
- Further reduction is possible with contiguous buffer pools, and/or with persistent threads bound with RELEASE(DEALLOCATE)
In the area of query performance, DB2 development expects a wide range of improvement:
- Typically in the area of 0 to 20 percent without a new access path
- Typically 10 to 40 percent with a new access path
- DB2 development has observed up to a 90 percent reduction in their testing for some specific queries
Turning to concurrent insert against a table defined in a UTS with the MEMBER CLUSTER attribute, customers can expect a 5 to 10 percent CPU reduction, providing that the current bottleneck is in space search, or in space map page or data page contention.
DB2 12 has over twice the number of performance enhancements compared to DB2 11, which was itself known for impressive query performance improvements. Many of the enhancements are targeted at SQL constructs seen in both new analytics and complex transactional workloads.
Firstly, DB2 12 delivers up to a 25 percent CPU improvement for traditional query workloads through optimizations for DISTINCT, GROUP BY, reduced work-file usage, multiple index access and list prefetch.
Secondly, it delivers an up to 2x improvement for modern SQL applications, focusing on performance improvements for next-generation SAP applications, for real-time analytics and for complex OLTP workloads. These optimizations are related to outer join, UNION ALL, stage 2 join predicates, CASE expressions, VARBINARY datatype indexability, DECFLOAT datatype indexability and others.
Parallel query child tasks are now 100 percent eligible for z Systems Integrated Information Processor (zIIP) in DB2 12. In prior releases there was a complicated formula to determine which parts of the parallel query were eligible for zIIP offload. In DB2 12 this becomes much easier, with all child tasks associated with the queries now being zIIP eligible.
That wraps up the first in this series looking at significant enhancements introduced in DB2. In subsequent articles we will move on to look at the following topics in more detail:
Gareth Z. Jones has worked in IT since 1985, Until 2000, he was an IBM customer, with experience as a systems programmer and DBA. He is now works in DB2 for z/OS development, as a member of the SWAT Team, which is led by John Campbell. He has worked with many customers around the world to help them be successful in their use of DB2. He has written several technical papers and presented at many conferences and many group meetings. He can be contacted via email at email@example.com.
- Performance for traditional workloads
- Performance enablers for modern applications
- Application enablement
- Reliability, availability and scalability
- DB2 utilities
- Data sharing improvements
- Migration to DB2 12 and the continuous delivery model