MAINFRAME > Administrator > Db2

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

IBM DB2 V12

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;

John Iczkovits is a member of IBM’s DB2 Advanced Technical Skills team. His background includes a mix of MVS, Storage and DB2.


comments powered by Disqus

Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

An Intuitive Approach to DB2 for z/OS SQL Query Tuning

Real-world samples provide valuable lessons.

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters