DB2 9.7: It All Adds Up

Automation, compression, and management improvements in the new version of DB2 save valuable time—and money

Time is money. But in the data management business, this saying might be better phrased as “Time costs money… and so does everything else!” Servers, storage, power, administrative time, development time, and support time all show up on the balance sheet these days, and pretty much everyone from the data center team to the CEO is trying to figure out how to spend less.

This environment is both the backdrop and the driver for IBM® DB2® 9.7 for Linux, UNIX, and Windows (LUW) data management. Announced in April, this version takes direct aim at reducing costs across the board with a broad range of features and improvements. Some of the headliners include new Deep Compression technology to help organizations reduce the amount of storage needed, as well as new and improved tools to streamline administration and workload management, accelerate development, and automate repetitive tasks.

Unlocking XML data

By enabling DB2 to store XML data in its native format, the pureXML engine has given organizations that depend on XML tremendous advantages in performance and flexibility. A major enhancement in DB2 9.7 is the ease of moving back and forth between SQL data and XML data—developers don’t need to know which is which and can develop with whatever tools they prefer.

More organizations are capturing XML data but aren’t sure what to do with it. For example, suppliers may communicate via XML with an organization’s EDI system, so that a single system can handle the interaction with all suppliers. Until now, the organization’s transactional system has had to convert XML data to relational data or simply delete it from the warehouse. But DB2 9.7 can store XML in a warehouse and can scale to accommodate high volumes of data—even a terabyte or more. By taking advantage of this capability, organizations can now use their BI tools directly against XML to discover business insight previously locked in their XML data. The enhancements to Deep Compression for XML have led to great results, with compression rates over 65 percent and performance acceleration of more than 1.5 times.

The additional compression also leads to better performance with indexes and temp tables. Companies involved in the beta testing program for the most recent version of DB2 are consistently reporting compression rates of 70 percent or more, which translates into storage savings of up to 50 percent with no performance penalties.

Virtualization on the march

Most databases continue to command dedicated physical servers, but virtualization is on the rise—so IBM has announced support for virtualization in all editions of DB2 9.7 for LUW, from IBM DB2 Express to IBM DB2 Enterprise, as well as the IBM InfoSphere® Warehouse for DB2 editions. IBM supports a broad array of virtualization environments for x86 and x64 architectures, including both full virtualization (VMware ESX, Red Hat Enterprise Linux, and SUSE Linux Enterprise Server), and OS virtualization—Solaris Zones, Parallels Virtuozzo Containers. For a complete list of virtualization support, visit

IBM has also optimized DB2 9.7 for VMware. As workloads or virtual partitions change, DB2 will react and dynamically allocate memory. You can also take advantage of the VMware vMotion feature to move a virtual machine from one physical server to another with no downtime. IBM is certified by VMware on this capability.

To help reduce costs, IBM offers flexible virtualization license costs, where you pay only for the number of virtual resources that you have deployed. Processor Value Unit (PVU) sub-capacity licensing lets you license DB2 for less than the full capacity of your server or group of servers. It provides the licensing granularity needed to leverage various multi-core chip and virtualization technologies. Also, IBM has provided additional usage options by announcing DB2 availability through the Amazon Elastic Compute Cloud (EC2) infrastructure service.

DB2 stays solid on security

Making sure that the right people—and only the right people—have access to specific data is a critical challenge for data management professionals.

IBM tackles this issue in the new version of DB2 with enhanced security and audit features. DB2 9.7 for LUW increases access control granularity, making it possible to give DBAs full control over a database without access to the actual data. This enables DBAs to do everything to the database that they would ordinarily do—perform regular administration tasks, load data, use utilities—but not see or alter the data. Separating the ability to administer the database from the ability to access the data it carries makes it easier for organizations to establish and maintain security policies without interfering with necessary maintenance and administration functions.

Auditing was also recently tweaked for DB2. The audit facility was redesigned to improve performance and to provide fine-grained auditing (FGA). The audit facility now produces all audit records based on the audit configuration specified, controlling critical information about who is accessing DB2, when, and from where. The audit facility still provides the ability to audit at both the instance and the individual database level, independently recording all instance- and database-level activities in separate logs. The improvements help data managers track connections and authorizations, statement text, application IDs, and the originating request’s IP, along with timestamps for important events.

Rounding out the security improvements in DB2 9.7 is Encryption Expert, which allows users to encrypt data at rest, onsite, and offsite, and to store passwords at a central security server. In-transit data is also secured by passwords so that even if a tape is lost during delivery, the data on it is not accessible to unauthorized users.

How to control time

Saving DBA time and effort is another big part of the DB2 9.7 feature set, which expands on the familiar DB2 controls and automated tools for configuring, optimizing, and protecting the database. One of the most important new administrative features expands on the workload management capabilities of DB2, which gives database administrators the ability to prioritize workloads and be sure that their databases are processing the right job at the right time.

In DB2 9.5, users gained the ability to prioritize workloads and assign rankings to different users, roles, groups, application names—any combination of factors. In DB 9.7, this capability is enhanced with time-based functionality. Integrated tooling from IBM Data Studio Base, in the IBM Optim Performance Manager for DB2 for Linux, UNIX, and Windows tool (previously IBM Data Studio and IBM DB2 Performance Expert; see sidebar, “Optim development environment grows”), allows users to automatically boost job priorities at specific times of day to meet deadlines. Users can also assign a high priority to batch jobs during off-peak hours so they can be completed within designated windows.

A waterfall workload management ability also enables DBAs to automatically lower the importance of some workloads when certain thresholds, such as CPU used or rows read, are hit. This helps ensure that rogue queries do not take over a database. DBAs can use the same technology to increase the priority of certain workloads at key thresholds, such as time, to make sure important queries that must meet service-level agreements get resource priority.

The new version of DB2 9.7 for LUW also makes it easy to manage mixed workloads on your system, such as a high-volume transactional system with reporting or a BI system with occasional trickle feeds. A newly added “currently committed” locking syntax eliminates the conflict between read and write workloads so the database can deliver a true point-in-time response.

Figure 1: The PL/SQL compilation process with IBM DB2 9.7 for Linux, UNIX, and Windows has three basic steps.

In addition to new features for managing database time, DB2 9.7 also helps administrators minimize downtime by making it possible to move database schemas without taking the system down. Changing tables—even changing column type—can be done with no downtime, as can moving a table from one table space to another. In fact, you can transfer an entire schema, including all the tables and associated objects, from development to QA to performance-testing and incur no downtime.

DB2 9.7: Simplifying life for both DBAs and developers

With advances in compression, development, administration, virtualization support, and development, DB2 9.7 is poised to help database managers use server and storage resources more efficiently, automate workload management, and simplify application development. Application developers and ISVs familiar with the Oracle DBMS can more rapidly get applications and tools running on DB2 with new PL/SQL support and flexible concurrency model and data-typing technology (see Figure 1). The new DB2 9.7 release also offers organizations more options in security management and high availability.

With businesses keeping a close eye on both time and money these days, the new features in IBM DB2 9.7 for LUW will help get the most out of both.