Skip to main content

IBM Db2 V12 Enhances and Integrates Data for Cloud, Mobile and Analytics

Enhancements for IBM Db2 V12 allow it to manage the new, voluminous world of cloud, mobile, analytics and Apache Spark integration.

White background with spherical blue geometric shapes.

DB2* V12 for z/OS*, which became generally available in October 2016, follows the themes of reliability, availability and serviceability (RAS). It directly integrates and is built for large amounts of data for cloud, mobile and analytics. Agile development results in lower code defect-related issues and therefore fewer problem management records compared with prior releases.

DB2 12 offers many rich features. Highlights include CPU reduction for lookups with advanced in-memory techniques, faster inserts, 2x speedup for query workload and 100x more speed for targeted queries, as well as scaling the size of a table. Along with these advances, IBM DB2 Silicon Valley Lab internal tests ran 384 million transactions per hour using RESTful web APIs. All of these enhancements allow DB2 to manage the new, voluminous world of cloud, mobile, analytics and Apache Spark integration.

Performance Increases

Critical to powering the Internet of Things as well as mobile and cloud apps, DB2 12 achieved an ingest rate of 11.7 million inserts per second in internal tests. Applying agile partition technology, a single DB2 table increased to 280 trillion rows.

Continuing the theme of improving performance with memory, a new Index Fast Traverse Block (FTB) was introduced for fast index lookups. The FTB resides in memory areas outside of the buffer pool and is controlled by the new ZPARM INDEX_MEMORY_CONTROL as well as the new DB2 catalog table SYSINDEXCONTROL. FTBs are for UNIQUE indexes only, with a key size of 64 bytes or less. DB2 technology automatically determines which indexes would benefit from FTB. The DISPLAY STATS command shows which indexes are using FTBs, and usage is tracked using new instrumentation facility component identifiers 389 and 477. Internal tests showed up to 23 percent CPU reduction for index lookup using a DB2 12 in-memory index tree with five index levels.

Insert workloads are among the most prevalent and critical to performance. DB2 12 delivers significant improvements for nonclustered insert workloads for journal table (nonindexed) patterns using universal table space with MEMBER CLUSTER. An advanced, new insert algorithm to streamline space search is controlled by new ZPARM DEFAULT_INSERT_ALGORITHM and can be overridden by the INSERT ALGORITHM table space attribute. Simulated stock exchange transactions using the new insert algorithm achieved 11.7 million inserts per second in internal IBM testing.

Significant CPU reduction was achieved for DB2 query workloads using UNION ALL as well as complex outer joins. Considerable CPU reduction was achieved for query workloads using complex reporting with large sorts. Moderate reductions were achieved for simple queries or those with large data scans.

Customized Fields

Application enablement has always been a key factor for every new DB2 release. In V12, the MERGE statement is aligned with the behavior defined by SQL standards and conforms to the DB2 family of Linux*, UNIX* and Windows*. DB2 now fully supports and allows a table to be used as input, multiple MATCHED clauses, additional predicates with [NOT]MATCHED, the DELETE operation, and IGNORE and SIGNAL.

SQL pagination dealing with complex OR statements was first introduced in DB2 V10. SQL pagination now allows for numeric-based and data-dependent pagination. An example of the numeric-based pagination is:


SELECT * FROM tab OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY 

An example of data-dependent pagination is:


WHERE (LASTNAME = ‘SMITH’ AND FIRSTNAME >= ‘JOHN’) OR
      (LASTNAME > ‘SMITH’) 
With the new equivalent syntax:
WHERE (LASTNAME, FIRSTNAME) > (SMITH, JOHN)

DELETE statements, such as DELETE FROM T1 WHERE C1 > 7, could delete millions of rows, affecting locking and logging. Piecewise data modification allows for deletion of fewer rows as shown where a maximum of 5,000 rows are deleted:

 
DELETE FROM T1 WHERE C1 > 7 FETCH FIRST 5000 ROWS ONLY;
COMMIT;

At times, DB2 clients are frustrated by the partition by range (PBR) data set maximum size and other limitations. PBR relative page number (RPN) is a new type of table space that lifts the limitations. The new RPN is mutually exclusive with absolute page number, used in prior releases of DB2 for PBR data sets. RPN data sets remove the dependency between the number of partitions and partition size. Part number is stored in the partition header page, while page number is stored in the data page, relative to start of the partition. With RPN, DSSIZE maximum of 256 GB per data set (partition) increases to 1 TB. Maximum table size increases from 128 TB for a 32K object to 4 PB. Maximum number of rows for 4K pages increased to 280 trillion. Increasing DSSIZE is supported at the partition level and no longer requires a REORG, however decreasing DSSIZE does. A new DSSIZE support exists for indexes. Attribute modifications by partition and schema changes are externalized using REORG PART.

Online schema improvements are always a DBA’s favorite for each DB2 release. DBAs can now insert a new partition between existing partitions. ALTER INDEX COMPRESS YES, which allows the user to alter an index to use compression, will no longer cause an outage by putting the index in rebuild pending. This statement is now online deferred.

Another enhancement allows multiple conflicting deferred ALTER statements at the column level, which are materialized through online REORG to be mixed without issue. Also, the TRANSFER OWNERSHIP statement now allows you to transfer ownership to a new AUTHID or role without having to first unload, drop or recreate the object.

Migration and Delivery Improvements

Migration to DB2 12 has changed considerably. The enabling-new-function mode stage has been eliminated, and new-function mode is now activated by new command -ACTIVATE FUNCTION LEVEL. APPLCOMPAT and fallback rules continue to apply. In DB2 11, conversion of the bootstrap data set from a 6-byte to a 10-byte relative byte address was voluntary but is mandatory for DB2 12. Basic row format is deprecated with the ZPARM and REORG options removed. Temporal real-time statistics (RTS) are integrated into the DB2 catalog, although the enablement is optional. This will allow clients to trend information, such as data set size, in the RTS.

DB2 direction also aligns with the mainframe software direction of continuous delivery. DB2 12 provides continuous delivery, whereby clients can apply new enhancements without waiting for the traditional DB2 three-year software cycle for a new version.

DB2 utilities benefit from improved efficiency by reducing CPU and increasing IBM z Systems* Integrated Information Processor offload for specific utilities. RUNSTATS and UNLOAD can be run with the REGISTER NO option to eliminate data sharing overhead. For FlashCopy*, managing the operation is improved by allowing for multiple Data Facility Storage Management Subsystem copy pool support. Messages related to the FlashCopy feature can now be allocated and reviewed in specific data sets, rather than milling through thousands or millions of lines of hierarchical storage management output. Storage and management classes for system managed storage are now honored in the TEMPLATE statement for FlashCopy image copy. RUNSTATS offers the option to no longer invalidate cache for dynamic statements.

The majority of DB2 performance-related problems reported are caused by skewed statistics. For example, column GENDER for a table has a skew of 90 percent men and 10 percent women. RUNSTATS TABLE(ALL) INDEX(ALL), without additional options for column GENDER, will result in an index not being used for GENDER. IBM Data Studio and IBM InfoSphere* Optim* Query Workload Tuner provide the Workload Statistics Advisor, which—based on an SQL-specific statement(s) with data skew—will display and, if requested, execute a specific RUNSTATS statement that will resolve the skew. DB2 12 allows this to be automated, so additional tools aren’t required; rather, RUNSTATS can utilize the USE PROFILE option to resolve data skew.

Competitive Features

DB2 12 has kept up with great advances in the industry, such as cloud, mobile and analytics, while providing one of the best database management systems in the industry.

DB2 for z/OS consistently provides best of breed for the long-standing RAS and strength of the IBM z* platform. Not all of the rich and incredible DB2 12 features are included in this article. Learn where to find other enhancements at ibm.co/2ixOs5K. Because of continuous delivery, more will come in the near future without having to wait for a new DB2 version.

IBM Systems Webinar Icon

View upcoming and on-demand (IBM Z, IBM i, AIX, Power Systems) webinars.
Register now →