Jun 21 ’10

Top-10 Features of DB2 10 for z/OS: A Preview

by Willie Favero in z/Journal

On March 12, 2010, IBM announced a closed beta for DB2 10 for z/OS and a few select customers are now putting it through its paces. We don’t yet know when DB2 10 will be Generally Available (GA), nor has IBM announced when DB2 Version 8 (in service for six years) will go out of service.  

This article offers a DB2 10 for z/OS “Top-10 list,” previewing new functions of a system that offers improved performance with fewer CPUs.

10. Productivity improvements

DB2 10 reduces the need for REORGs by:

DB2 10 uses a new set of stored procedures and catalog tables to determine what statistics need to be collected and when. It also records what activities are being performed and does it all via an automatic scheduling process. That’s a significant improvement over RUNSTATS, which is challenging to use and can result in collecting unnecessary statistics or not enough statistics, a waste of resources that may not be available.

9. Better access to data

DB2 10 will take advantage of hash access, which should be faster and provide a nice CPU savings. Hashing is a technique that turns a key into a physical location of a row, resulting in one-page access for a matching row on an equal predicate. If you’ve worked with IMS/VS, you may already have some exposure to hash access. If not, no problem. You’ll quickly learn to like it.   

There’s also a nice index improvement called index includes. A unique index exists to enforce a unique constraint on a table. Sometimes, a unique index doesn’t contain enough columns in the key to give index-only access. One possible solution is to create two indexes. One index enforces uniqueness and a second index, with additional columns in the key, gives index-only access. DB2 10 will let you "include" additional columns in the index that are not included in the key to enforce uniqueness. One index gives you uniqueness and index-only access.

Both hash access and index include won’t be available until the release of DB2 10 New Function Mode (NFM).

Then there’s access path stability. This is a bit more than what was delivered via an APAR to DB2 9 and V8. Although you still get all the package versioning in DB2 10, you also get:

DB2 10 will allow an upgrade from DB2 9 or V8. If you’re one of the few looking to jump directly to DB2 10 from V8, you can. However, if you envision migrating to DB2 9 this year or next, we strongly suggest doing so.

8. XML performance

DB2 10 offers insert and query performance improvements such as binary XML support and multi-versioning for XML data. However, there are also XML query language enhancements, including subdocument update, data and time support, and the use of XML in native SQL stored procedures and User-Defined Functions (UDF). DB2 will use the z/OS XML System Services for XML validation with 100 percent of that validation being redirected to specialty processors: the System z Application Assist Processor (zAAP), and System z Integrated Information Processor (zIIP). Among other XML data integrity improvements, DB2’s CHECK DATA utility will perform document structure and schema validation.

7. SQL enhancements and improved portability

DB2 10 adds further SQL improvements to those introduced in prior releases. One of the handier improvements is an increased length of timestamps taking the stored value out to the nanosecond. Another is a new special register SET CURRENT TIMESTAMP, which allows the precision of the timestamp to be set. Several enhancements allow the control of time zones, including a data type, two new special registers, a new ZPARM keyword, and a new operator.

An application program will be able to use indicator variables when determining whether a target column of a host variable should contain a default value or an unassigned value. If the value is unassigned, the column is treated as if it was never part of the statement. This capability will be enabled on an individual PREPARE statement for dynamic SQL or with a new bind option for static SQL.

DB2 10 extends DB2’s support for data warehouse and Business Intelligence (BI) applications via the moving sum and moving average functions, a subset of the Online Analytical Processing (OLAP) specification. Moving sum and moving average divide the final result of a query (after applying the WHERE, GROUP BY, and HAVING CLAUSES) into partitions. The rows in the new partitions are sorted and aggregates are applied to sets of rows.

DB2 10 also has more indexable/stage 1 predicates and SQL pagination (mapping of predicates to a single index).

The various SQL enhancements improve DB2 family consistency and simplify portability from other platforms, including other relational database managers.

6. Temporal (or versioned) data

Temporal, or versioned data, involves the ability to give your data two-dimensional history. Consider an example. Let’s say you have a table with customer policy information. This table has a row for the customer describing his coverage. This row has a special kind of column identifying when the policy was opened (its effective time) and when it expires (its end time). However, now the customer changes his coverage.

Rather than simply updating the existing row and effectively destroying any information originally known about the policy, the organization can make a change, causing a new row to be created with a new effective time but with the same end time. Every policy change would be reflected with a new row with a new effective time. If they close the policy, a final row is added that updates the end time. You may say, “We already do this.” Well, what if you could do this all through the database engine with no need for complex application coding? In addition, the database engine could retrieve any piece of data between any set of times. It’s available in DB2 10. DB2 will also give you system date capability for start time and end time representing when a row was initially inserted and modified or deleted and giving you a history table. Abstracts such as AS OF, FROM and TO, and BETWEEN times are translated into business time and system time paired columns managed by DB2, enforcing business rule integrity.

5. Improved security

DB2 10 provides some of the most significant security and compliance enhancements ever made to DB2:

Security in DB2 will be more flexible, duties to be performed will be better separated, administrators will be able to perform their jobs without fear of accessing data, and it will all be simpler to control. There’s even a new clause that will prevent cascading.

4. More online changes

Unlike previous versions of DB2, this time it’s not just about Data Definition Language (DDL); it’s about subsystem changes, utility changes, and data definition changes. In today’s fast-paced world, who has time to recycle DB2 just because something changed?

Online changes to table spaces are significant. Remember how you couldn’t specify MEMBER CLUSTER with Universal Table Spaces (UTS) in DB2 9?  In DB2 10, you can specify MEMBER CLUSTER for a UTS and use ALTER TABLESPACE to turn it on.

With DB2 10, you can change DSSIZE and SEGSIZE much more easily with the ALTER command. You can also alter from simple table space to a partition by growth UTS, a simple segmented table space to a partition by growth UTS, and partitioning to partition by range UTS. REORG is going to play a huge role in all this conversion from one table space type to another. There are several other UTS enhancements planned that are too numerous to detail here.

The article “The Magic of Keeping DB2 in Order” (z/Journal February/March 2010) mentioned recommendations that were being validated because of some APAR restrictions. However, in DB2 10, the news is even better. Sort work files will be limited to DB2 managed page sets with zero secondary specified, and Declared Global Temporary Tables (DGTTs) are limited to DB2 managed page sets with a secondary (non-zero) specification.

The maximum sort key length will also be increased from 16,000 to 32,704 with a maximum record length limit increased to 65,529, along with record lengths for sorts being restricted to 32k. DB2 10 will deliver partition by growth support for work files. And there are other improvements for work files; those will be a topic for a future article.

Note: Classic table space partitioning, a concept used since DB2 V1.1, will be deprecated in DB2 10.

There have been numerous warnings in previous versions of DB2 about the removal of private protocol. Well, it has finally happened. Private protocol will not be in DB2 10. Review APARs PK92339 and PK64045 to prepare for the removal of private protocol.

If you’re hoping you can avoid index REORGs, or perform them less frequently, DB2 10 addresses your wish at least partially with:

All these enhancements are available in DB2 10 Conversion Mode (CM)—and there are other items on the list of utility improvements. Data set-level FlashCopy for COPY, REORG inline copy, and LOAD inline copy FLASH COPY backups can be used as input to RECOVER, COPYTOCOPY, DSN1COPY, and DSN1PRNT. A few new ZPARMs will help out, too.

A set of new stored procedures will use the new DB2 Admin Scheduler and facilitate automatic collection of statistics.

On the system side, checkpoint processing can now occur by both time and number of logs. Again, a few new ZPARMs will be useful. DISPLAY LOG will tell you what’s happening. You’ll also benefit from a command to dynamically add a new ACTIVE LOG to DB2. You won’t be able to dynamically delete a log, but adding one could be significant.

3. Concurrency improvements

The DB2 catalog function will be enhanced with support for changing catalog tables, not just adding more of them. Also, DB2 catalog capacity will be enhanced with more tables. Future catalog posters won’t be posters at all; they’ll be wall hangings for only large walls.

You’ll experience fewer performance bottlenecks with heavy workloads in DB2 10 due to reduced latching and system serialization contention and the use of 64-bit common storage to avoid Extended Common Service Area (ECSA) constraints. Parts of the DB2 catalog have been restructured so that DDL, BINDs, and PREPAREs can process without contending with each other. The restrictive size of the SPT01 has been addressed by using Large Objects (LOBs) for certain package parts. All this will be accomplished by:

You can avoid some REORG issues with the catalog if you get rid of the links in the catalog tables that used to use them. DB2 is also going to use more Character LOBs (CLOBs) and Binary LOBs (BLOBs) for catalog columns with long strings as well as manage the catalog tables using more DB2-managed System Managed Storage (SMS)-controlled DB2 table spaces. Catalog conversion will occur during Enabling New Function Mode (ENFM).

2. Enable 10 times the users by avoiding memory constraints

Up to 90 percent of storage for thread processing is now above the 2GB bar. This should give most customers the ability to run five to 10 times the number of threads in a single subsystem.

This change impacts many things, one of which is Logical Partition (LPAR) consolidation. Although data sharing’s major benefit is availability, many customers have moved to multiple data sharing members to spread their users across multiple LPARs. Even in DB2 9, you were pressed to run more than 500 to 700 users in a single DB2 address space. If you needed more users, you had to move to data sharing; the more users, the more data sharing members were required. Of course, reducing LPARs reduces complexity and cost and things can be easier to monitor and manage, Nevertheless, data sharing is still a great availability feature and useful if you have high-availability requirements.

1. CPU reductions for transactions, queries, and batch

DB2 lab staff members have said DB2 10 provides the best reduction in CPU for transactions and batch in more than 20 years. The lab expects most customers to see about a 5 to 10 percent reduction in CPU times when they install DB2 10 CM. Once DB2 10 is installed and customers start to take advantage of the new functionality and enhancements in NFM, they should see additional memory and CPU usage reductions.

What Isn’t Included

It’s only fitting to also mention what’s being removed with DB2 10. This list, which may grow or shrink, assumes an upgrade from DB2 9 to DB2 10. If upgrading from V8 to DB2 10, there are additional items that will be removed that are listed in the DB2 9 Installation Guide (GC18-9846):


DB2 10 offers many valuable enhancements, but there’s still much more to talk about that’s not mentioned or fully detailed here. Clearly, few data servers can top DB2’s efficiency, resilience, and growth potential.  

To learn more about DB2 10 and monitor its evolution, visit www.ibm.com/software/data/db2/zos/db2-10/. This will be an interesting year for DB2. Once GA is announced, the excitement will continue. Stay tuned!