Problem solve Get help with specific problems with your technologies, process and projects.

New DB2 V10 features and performance

DB2 V10 addresses issues that previous versions experienced and includes new features, such as temporal tables and subgroups.

Now that customers have recovered from DB2 V8 and stumbled through V9,IBM assumes they’re ready for a new version. Despite some healthy skepticism based on prior experience, this feature-rich release promises to be a leap forward in technology.

The benefits of temporal tables
In the 21st century, DASD is cheap, and most corporations have more data than they know what do with. Government regulators often require companies to keep records for years. All this extra data directly affects application performance, as DB2 has more information to wade through and application fetch loops get longer.

DB2 V10 addresses this issue with temporal tables. Temporal tables contain one or both of two periods: the system-maintained SYSTEM_TIME and application-managed BUSINESS_TIME. Both periods have a pair of timestamps indicating when a row is active.

In SYSTEM_TIME-managed tables, the original data is in the “system period temporal table” and the archive rows are in the “history table.” Through table definitions, a database administrator (DBA) may control how long data stays in the original table. DB2 then archives updated or deleted rows from the primary table into the history table.

With old data purged from the temporal table, DB2 and applications spend less time looking for needles in haystacks. Even better, the old rows are still available in the history table, although the application will need some changes to retrieve it.

The subgroup attach feature
Before DB2 V10, there were only data-sharing groups. The group names were nice, but an enterprise wasn’t able to control which member a subsystem like CICS would connect to. Now, DB2 V10 has “subgroups,” which are subsets of data-sharing groups. This feature gives a shop the ability to run several instances of the same sharing group on the same LPAR, but it segregates who uses them.

A systems programmer may want to create one subgroup for DDF, one for CICS and another for batch. Wielding the subgroup name “CICS” will attach the designated CICS-only member to any LPAR without a specific member name. Note that CICS TS 4.1 requires APAR PM25602. The CICS TS 3.2 enabling APAR is PM25688.

DB2 V10 features that improve availability
One of the biggest difficulties in running an online DB2 application is database locking. V10 has a new feature that allows readers to get to data already committed by updaters, which should allow for less contention. Note that this is different from “dirty reads” -- reading uncommitted data – which was available in earlier releases. A new bind option, CONCURRENTACCESSRESOLUTION, along with the corresponding PREPARE statement option, controls whether a user can see the data (USECURRENTLYCOMMITTED) or wait (WAITFOROUTCOME).

Database maintenance is another sore spot for DBAs. Carefully maintained databases perform better, but finding a quiet time to do that maintenance is difficult. V10 introduces row-level locking of and removed links between system tables, which should allow online re-orgs and other utilities to be able to get in. There are other enhancements for instant table schema changes that formerly require a database reorg.

Finally, there is a neat trick where a DBA can tell DB2 to recover a database through backout instead of the classic restore image copy and forward recovery. Backout recovery requires access to the active logs and works best when the recovery point is relatively recent.

DB2 V10 features for security and control
DB2 V10 introduces row- and column-level security. This finer bit of control lifts the burden of data security from applications and moves it into the administrative world. The problem will be finding a happy medium between this type of security, performance and administrative tangle.

The new version of DB2 allows for more granularity of administrative duties, which will brighten the black heart of every auditor. Only one type of user, SECADM, has control over the whole system while the other types of duties are carefully farmed out following the principles of separation of duties and least amount of ability.

New performance perks
DB2 V10 is supposed to be a “performance release,” and IBM boldly claims some customers see 5% to 10% CPU improvements out of the box for “traditional workloads” and 20% for “nontraditional.” Part of the savings come from DB2 exploiting the z9 instruction set, which is currently the lowest supported IBM processor. It’s important to note that DB2 V10 won’t work on a z990 or earlier processor. The technical overviews allude to more utilization of hardware-locking facilities, such as the compare and swap instruction, instead of software constructs, such as latches.

DB2 V10 tries to keep more information in memory and utilizes quite a bit of storage above the 4G bar, thus enabling support for up to 20,000 concurrently active threads. This increase in concurrency will be very important for customers who created parallel DB2 members for virtual storage constraint relief.

The other CPU savings most likely come from other processing and algorithmic changes that are too detailed to be outlined here. If you would like more in-depth information, I suggest looking in IBM’s DB210 for z/OS document or its DB2 V10 for z/OS Technical Overview redbook.

ABOUT THE AUTHOR: For almost 30 years, Robert Crawford has worked as a mainframe systems programmer. Crawford has experience in system administration as well as debugging and tuning applications. He has coded in COBOL, Assembler and C++ using VSAM, DLI and DB2.

Dig Deeper on IBM system z and mainframe systems

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.