IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
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:
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:
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:
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:
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:
In the area of query performance, DB2 development expects a wide range of improvement:
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.
Add a Comment