What's New: DB2 10 for z/OS

Dramatic CPU savings and data availability enhancements, check. But have you seen the new temporal data support?

This article outlines the new features and resulting benefits of DB2 10, especially the out-of-the-box savings, scalability, and new application programming improvements.

This article was originally published in IBM Data magazine.

Share:

Terrie Jacopi, Program manager, IBM China

Terrie Jacopi is program manager for DB2 for z/OS



10 October 2010

Also available in Chinese

The latest release of DB2 for z/OS delivers the most aggressive package of CPU savings and performance improvements from IBM in more than 20 years. Not enough? Let's add a tall stack of new ways to keep your data continuously available, no matter what. Still want more? We've also got a big set of enhanced application-development capabilities.

Just listing every improvement in DB2 10 for z/OS would take more pages than we have. So in this article, we'll focus on the capabilities that tie to your top concerns: saving money and strengthening your data infrastructure. We'll also dig into a sharp new feature that will make it possible for you to manipulate time-based data faster and easier than ever before.

Out-of-the-box savings

IBM internal testing and reports from the beta program are showing some impressive numbers: Simply migrating to DB2 10 and rebinding will deliver significant savings for transactions, queries, and batch processes. Most customers can achieve CPU savings of 5 to 10 percent for traditional transaction workloads and up to 20 percent savings for nontraditional workloads, such as those using SQL stored procedures and some distributed relational database architecture (DRDA)–based workloads. DB2 10 reduces CPU usage by optimizing processor times and memory access, and by taking greater advantage of the latest processor improvements, larger amounts of memory, solid-state drives, and z/OS enhancements.

Of course, your results may vary. For example, customers that currently have virtual storage constraints or latching issues are likely to see greater improvements. Concurrent sequential single inserts that have many indexes can be reduced by 5 to 40 percent. Customers moving from DB2 9 can expect a small (up to 7 percent) reduction in CPU usage for utilities, while customers moving from version 8 will see reductions in CPU usage as high as 20 percent. DB2 10 also includes a number of new database design capabilities, such as inline large objects (LOBs), hash access, and RELEASE(DEALLOCATE), that can improve performance and cut CPU usage.


More scalable and more available

Getting to DB2 10

DB2 10 supports direct migration from DB2 8 and DB2 9. Customers can choose which version is best for their business needs and plan their migration accordingly. The end of service for DB2 8 has been set for April 2012. Customers still on version 8 need to build a plan to move.

If you're building a migration plan and need more information about DB2 8 migration steps click here.

DB2 10 sports an array of features designed to take data scalability and availability to new heights.

Improved scalability

DB2 10 substantially increases the amount of virtual storage that can be addressed by moving most DB2 working memory from 32-bit to 64-bit. One of the benefits of this increase is that a single DB2 10 subsystem supports 5 to 10 times more concurrent users than previous releases supported—as many as 20,000 concurrent threads. This greatly improves the vertical scalability of your DB2 subsystem.

Continuous availability enhancements

Online schema enhancements allow you to make changes to database objects (indexes and table spaces) while maximizing the availability of the altered objects. ALTER statement enhancements let you change indexes and table spaces without needing to unload the data, drop and re-create the objects, regenerate all of the security authorizations, re-create the views, and reload the data. The changes materialize when the altered objects are reorganized.

DB2 10 can now automatically reorganize disjoint partition ranges of a partitioned table space. This new feature, along with improvements to SWITCH phase performance and diagnostics, increases the usability and performance of online reorganization. DB2 10 also removes restrictions on the online reorganization of base table spaces that use LOB columns.

The DB2 catalog has been restructured to reduce lock contention by removing all links in the catalog and directory. New row-level locking functionality improves the lock avoidance techniques of DB2, and improves concurrency by holding acquired locks for less time and preventing writers from blocking the readers of data.

DB2 10 lets you access currently committed data to dramatically minimize transaction suspension. Now, a read transaction can access the currently committed and consistent image of rows that are incompatibly locked by write transactions without being blocked. This concurrency control can greatly reduce timeout situations between readers and writers accessing the same data row. DB2 10 also provides flexibility and increased performance to applications that only require available and committed data to be returned from DB2 tables.


Application programming improvements: Temporal tables and versioning

Raw performance and availability improvements will warm the heart of any DBA. But DB2 10 also brings a number of new application programming features to the table. One of the most exciting is built-in support for temporal data.

You need temporal support anytime you want to ask a question with a time-based element. For example, what level of insurance coverage did a client have six months ago when they had an accident? What was the medical condition of a patient at a specific time? What changes were made to a client's financial account during the last five years?

Previously, answering questions like these required developers to hardcode complex logic into their applications. Now, developers can instruct DB2 10 to automatically maintain a history of database changes and track effective business dates. This new capability uses simple SQL statements and provides a consistent approach to tracking time-related events, and managing and maintaining versioned data.

DB2 10 introduces two new concepts—system time and business time. System time tracks when changes are made to the state of the table, such as when an insurance policy was modified. Business time tracks effective dates of certain business conditions, such as interest rates. Bitemporal tables allow you to track both system and business time in a single table, and time periods can be added to indicate start and end points.

Using system time and business time

Defining a table with a system time period instructs DB2 10 to automatically capture changes made to the state of the table and to save the "old" rows in a history table. Simple SQL queries that reference the current table but also need data in the history table will cause DB2 to transparently access the history table as needed, providing easy access to historical data without complex WHERE clauses with various timestamp and join conditions. For inserts, DB2 generates the appropriate values for system and transaction start times without having to reference them in the INSERT statement. When updating current data, DB2 automatically maintains old versions of the data in the appropriate history table—transparently.

Figure 1 shows what happens when an automobile insurance policy is updated with a new coverage amount by using the following:

    UPDATE policy 
    SET coverage_amt = 750000 
    WHERE id = 111

DB2 updates the row in the current table and moves a copy of the old row to the history table, recording the system time start and end values. Subsequent updates are handled similarly. A simple DELETE command causes DB2 to remove the data from the current table and maintain an old version in the history table, including the end time of the deleted data. Users can access this data with simple SQL containing a time period specification.

To query information about the coverage amount recorded in the database for policy 111 for December 1, 2010, you simply use SELECTcoverage_amt FROM policy FOR SYSTEM_TIME AS OF '12-01-2010' WHERE id = 111. DB2 transparently accesses the history table to retrieve the correct information.

You can also update, delete, and query data using a FOR PORTION OF BUSINESS_TIME clause. The following example shows how DB2 splits the appropriate rows for an automobile insurance policy by adjusting effective dates and coverage information through very simple SQL.

The customer's original policy ID number is 444 and the coverage amount is US$600,000 (see Figure 2).

The customer makes a request to increase the coverage amount to US$750,000 during a three-month trip, This is done with a SQL command that causes DB2 to split the row in two and insert a new row indicating the new coverage amount and the time period during which it is effective (see Figure 3):

    UPDATE policy 
    FOR PORTION OF BUSINESS_TIME FROM '06-01-2011' TO '09-01-2011' 
    SET coverage_amt = 750000 
    WHERE id = 444

Bitemporal tables allow you to manage data with both system and business time simultaneously, combining the benefits of both concepts. Bitemporal data can also be used in history tables.


DB2 and System z: A perfect match

DB2 10 delivers significant, out-of-the-box CPU savings for many workloads, and customers can see the benefits by simply migrating and rebinding. Application development has never been easier for DB2 for z/OS. With DB2 10's temporal data features, application developers and system administrators can support time-based data more easily than ever before. This is one release you don't want to miss.

Figure 1: When a record is updated, DB2 10 automatically adds a copy of the old row to the POLICY_HISTORY table.
Policy
ID VIN Annual_mileage Rent Coverage_amt Sys_start Sys_end
111 A11 10000 Y 750000 01-31-2011 12-31-9999
141 B09 14000 N 750000 11-15-2010 12-31-9999
Policy_History
ID VIN Annual_mileage Rent Coverage_amt Sys_start Sys_end
111 A11 10000 Y 500000 11-15-2010 01-31-2011
Figure 2: The original insurance policy includes mileage, coverage, and start/end dates.
Policy
ID VIN Annual_mileage Rent Coverage_amt Bus_start Bus_end
444 A44 40000 N 600000 01-01-2011 12-31-2011
Figure 3: The SQL command requires DB2 to split the rows for the policy, adjusting for the new coverage amount and date information.
Policy_History
ID VIN Annual_mileage Rent Coverage_amt Bus_start Bus_end
444 A44 40000 N 600000 01-01-2011 06-01-2011
444 A44 40000 N 750000 06-01-2011 09-01-2011
444 A44 40000 N 600000 09-01-2011 12-31-2011

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=550188
ArticleTitle=What's New: DB2 10 for z/OS
publish-date=10102010