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?

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

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.

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, go to:

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

Figure 1: When a record is updated, DB2 10 automatically adds a copy of the old row to the POLICY_HISTORY table.

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 SELECT coverage_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).

Figure 2: The original insurance policy includes mileage, coverage, and start/end dates.

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):

Figure 3: The SQL command requires DB2 to split the rows for the policy, adjusting for the new coverage
amount and date information.
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.