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: 907

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.

Events

Latest Activity

A blog post by Surekha Parekh was featured

Hot off the Press! Installing and Configuring IBM Db2 AI for z/OS Version 1.4 An IBM Redpaper publication

Check out this new "Complimentary" Db2 AI for z/OS Version 1.4 Red paper Installation and ConfigurationLearn MoreAbstractArtificial intelligence (AI) enables computers and machines to mimic the perception, learning, problem-solving, and decision-making capabilities of the human mind. AI development is made possible by the availability of large amounts of data and the corresponding development and wide availability of computer systems that can process all that data faster and more accurately than humans can.What happens if you infuse AI with a world-class database management system, such as IBM Db2®? IBM® has done just that with Db2 AI for z/OS (Db2ZAI).Db2ZAI is built to infuse AI and data science to assist businesses in the use of AI to develop applications more easily. With Db2ZAI, the…See More
21 hours ago
Jim Reed updated an event
Thumbnail

Build simple to multifaceted dashboards in QMF for Workstation at Online

July 22, 2021 from 8am to 9am
Today businesses need real time visual dashboards and reports across all their data, including hard to reach data across IBM Z non-relational data sources. QMF for Workstation makes visualizing virtually any data a reality, including IMS, VSAM, Adabas, IDMS and DB2 for z/OS data sources.This session will showcase and demo QMF’s dashboard/application building interface in QMF for Workstation from simple to multifaceted.See More
Jul 21
Carol Davis-Mann posted a blog post

Management Overview: DB2 Continuous Delivery Function Level Upgrades

By Julian StuhlerBackgroundWith the advent of DB2 12 for z/OS, IBM has moved to a more agile approach for delivering new function to DB2 customers, known as “continuous delivery”. Major new releases of DB2 will now happen more rarely, with smaller packets of new functionality being delivered via the routine product maintenance process. This allows IBM to develop and release new features much more frequently, thereby reducing “time to value” – a familiar DevOps message.To allow DB2 customers to absorb this new function in a flexible and efficient way, IBM has also delivered a comprehensive set of capabilities that allow the “function level” of the overall DB2 system to be easily progressed while insulating individual applications from the impact of any changes via the “application compatibility level” set for each program.Read Julian Stuhler's article…See More
Jul 14
Jim Reed posted an event
Thumbnail

Build simple to multifaceted dashboards in QMF for Workstation at Online

July 20, 2021 from 8am to 9am
Today businesses need real time visual dashboards and reports across all their data, including hard to reach data across IBM Z non-relational data sources. QMF for Workstation makes visualizing virtually any data a reality, including IMS, VSAM, Adabas, IDMS and DB2 for z/OS data sources.This session will showcase and demo QMF’s dashboard/application building interface in QMF for Workstation from simple to multifaceted.See More
Jul 14
A video by Surekha Parekh was featured

Db2 AI for z/OS Version 1.4

IBM Db2 AI for z/OS version 1.4 is a powerhouse of enhancements! Db2 AI for z/OS infuses more AI into your Db2 environment, making it operate smarter and mor...
Jul 5
A blog post by Daniel Luksetich was featured

Db2 for z/OS and JSON

I wrote a very short story about using the JSON/SQL support in Db2 for z/OS. I also wrote an introduction to JSON support in Db2 for z/OS article for IDUG.The story and link to the IDUG article is here:https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/This is a direct link to the IDUG article, but you need an IDUG login account (which is free).https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/See More
Jun 30
Daniel Luksetich posted a blog post

Db2 for z/OS and JSON

I wrote a very short story about using the JSON/SQL support in Db2 for z/OS. I also wrote an introduction to JSON support in Db2 for z/OS article for IDUG.The story and link to the IDUG article is here:https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/This is a direct link to the IDUG article, but you need an IDUG login account (which is free).https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/See More
Jun 30
William Shipley liked Surekha Parekh's blog post Live Db2 12 Client Panel 30th June 2021 - Sign Up today!
Jun 23
A blog post by Surekha Parekh was featured

Live Db2 12 Client Panel 30th June 2021 - Sign Up today!

Live Db2 12 Client Panel 30th June 2021 - Sign Up today!HURRY SIGN UP TIME IS HURRYING OUTSummaryDb2 for z/OS is the strategic enterprise database that has evolved and adapted to meet client needs for over 35 years. John Campbell IBM Distinguished Engineer has been one of key developers from IBM Silicon Valley Lab that has been extremely influential in driving some of the innovation around the core strengths and business value of Db2 for z/OS.We are bringing together some of our Db2 for z/OS clients so that they can share some of their "use cases" and experiences around Db2 12 upgrade journey and discuss how they are exploiting, planning to exploit the new technology to leverage  their Db2 for z/OS data to…See More
Jun 22
Daniel Luksetich posted a blog post

Traditional Db2 Development and Modern Database Development

This article is the first of a four-part series addressing Db2 for z/OS and modern development utilizing a Agile methodology and DevOps processes. In this article we define traditional versus modern development and how Db2 for z/OS, while remaining the premier database for high volume system of record applications, might be left behind when it comes to best practices involving innovation. Did you notice that “Db2” follows “Traditional” in the title of this article, but “Database” follows “Modern”? It is my opinion that Db2 for z/OS is not the first database that comes to mind when considering a DevOps development methodology. However, there is no reason why it shouldn’t be!The first thing we need to do is to identify what we mean by traditional versus modern development. In this series of articles, we will be speaking specifically of the waterfall methodology versus an Agile methodology and DevOps practices.To read the entire article please follow this link:…See More
Jun 21
Gulfem Ogutgen liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Jun 21
Gulfem Ogutgen liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 21

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

Forum

ADM10501W - Health indicator "Database Automatic Storage Utilization"

Started by ONLO in Application Development and DB2 Jun 16. 0 Replies

AIX Version 6.1DB2 10.5.6Hi every body,I got the above error:MESSAGE : ADM10501W Health indicator "Database Automatic Storage Utilization" ("db.auto_storage_util") breached the "upper" warning threshold of "80 %" with value "88 %" on "database"…Continue

DB2 Future

Started by Peter E Saxe in Application Development and DB2 May 20. 0 Replies

Has anyone ever submitted ideas of new functions in DBMS software like DB2?   have an idea for a future enhancement to DB2 that I would like to see.  Is this discussion area a good place to discuss this ?  Thanks.Continue

Tags: Enhancements, Future, DB2

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service