IBM DB2 12 for z/OS is ready for Hybrid Transactional Analytical Processing

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.

Application Enablement

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.

DBA Function

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

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

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

Quick Hits

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

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.

Performance Focus

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.

Deeper Look

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:

  • 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


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 jonesgth@uk.ibm.com.

Views: 896

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Comment by Bill Hulsizer on January 19, 2017 at 20:44

In mid-1990, Mercantile Stores implemented the largest DB2 production database in the world in v1.3.  We were soon greatly surpassed by UPS, but for a few months, we had the record.  Thanks to a lot of tuning, we ran 5 concurrent, high-volume, batch update jobs at night against the same tables and partitions with no locking issues (using page locking, not row locking).  The largest tables had 200+ million rows in them.  These tables were used all day long by DW-style inquiries and online updates/corrections with no locking issues.  In unusual situations, we could run one of the five high-volume batch processes daytime, while the normal DW-style inquires and online updates/corrections were happening, again without issues.

My point is, replication to a separate DW/BI environment, and the ETL it requires, has never been necessary, if you are using DB2 z/OS and do it right.  "Hybrid Transactional Analytical Processing" has always been possible with DB2 z/OS, if you know what you are doing and do it right.

Latest Activity

Jim Reed liked Annette Zawacki's discussion Is your Db2 for z/OS data part of your AI strategy? Learn how you can build AI models anywhere and deploy on IBM Z
yesterday
Jim Reed liked Diego Cardalliaguet's event EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021
yesterday
Jim Reed liked Diego Cardalliaguet's event EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021
yesterday
Jim Reed liked Surekha Parekh's event Achieving Optimal Availability, Usability and Performance with Universal Table Space - Part 2
yesterday
Profile Icon via Twitter
Join the live launch webcast, "What’s New in Db2 AI for z/OS and Watson Machine Learning for z/OS" June 1 at 11 AM… https://t.co/yvz9t7dwcC
TwitterTuesday · Reply · Retweet
A blog post by Surekha Parekh was featured
Tuesday
Diego Cardalliaguet updated an event
Thumbnail

EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021 at Video conference

May 18, 2021 from 10am to 4:15pm
Monday
Annette Zawacki added a discussion to the group Data, Analytics and AI on IBM Z
Saturday
Joe Geller posted an event

TRIDEX Db2 for LUW Q2 2021 Meeting at Virtual Meeting

May 13, 2021 from 9am to 1pm
May 6
Tom Glaser posted events
May 6
Annette Zawacki posted an event
Thumbnail

Is your Db2 for z/OS data part of your AI strategy? at Eastern Time (U.S. and Canada)

May 18, 2021 from 11am to 12pm
May 5
Profile Icon via Twitter
Join “The IBM Enterprise Data & AI Virtual Symposium 2021”https://t.co/uRxOVwMZu6 hear directly from our clients, R… https://t.co/SEGx6axQYf
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Live Now! IBM Enterprise Data & AI Virtual Symposium 2021. Welcome to Ibrahim Parlak from Garanti Bank, over 16 Mil… https://t.co/CtVD3yiMn7
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Join now Panel started “The IBM Enterprise Data & AI Virtual Symposium 2021” https://t.co/uRxOVwMZu6 hear directly… https://t.co/7WN1BLMV4Y
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Join now Panel started “The IBM Enterprise Data & AI Virtual Symposium 2021” https://t.co/uRxOVx4AlE hear directly… https://t.co/7KZmbGpt1O
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Join Now "The road to AI-first enterprises" The IBM Enterprise Data & AI Virtual Symposium. REGISTER NOW… https://t.co/woTAl9ExIQ
TwitterApr 27 · Reply · Retweet

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

who opened the scoring from the penalty spot

Started by long lin in What's hot ? Apr 22. 0 Replies

fotbollströja barn eget tryck Lionel Messi opened the scoring from the penalty spot, but that was as good as it got for Ronald Koeman's side as Mbappe took over to evoke memories of recent defeats to…Continue

Fact.MR redefines the way

Started by blon sin in What's hot ? Apr 21. 0 Replies

New York, April 20, 2021 - The primary reason for the failure of startups is not lack of funding or intense competition. The main reason is "misunderstanding of market demand."Therefore, for start-ups, it is important to thoroughly study the…Continue

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service