DB2 9.1 - Viper Edition
Home Tutorials Reviews Weblog
Book Review: DB2 9.1 - Viper Edition is a major upgrade for IBM
Feature: What's up and in the new version of IBM's stalwart database

IBM says DB2 version 9 is one of the biggest upgrades to its database line in the past decade. And there is a lot of evidence to back that statement. It starts with a huge upgrade to XML support and direct XML Querying and runs to enhancing greatly accessibility and development features. In effect, IBM is saying that ESB/SOA and associated Web Services are so important that they are going to implement the ability to store and retrieve XML in its native format including important XML/Secure data encryptions and authentications. And all of this must be parsing engine tamper-proof. In effect, IBM is betting that these XML capabilities are going to be critical for database engines going forward. This review examines the new DB2 database in this light.

What's New
lk
The Control Center's Java UI interface is the same, but notably a little faster. But most important of all it fully supports many of the new XML features including the XML Query Builder and other support wizards. In general the way that IBM has incorporated the new native XML capabilities throughout the DB2 support/utility infrastructure is one of the critical new features in this upgrade.

Db2 Viper has a number of other improvements but perhaps the next most important actually was available in DB2 version 8. That was the Express C free version of DB2. Viper continues the tradition by making available a free, full version of DB2 Viper with all the new XML, XQuery and performance improvements less the most advanced partitioning and clustering functions used largely in very big installations. This basic or Express C version of DB2 is not only available for development but also deployment free of charge. And the free deployment license allows for an unlimited number of users on dual core servers with up to 4GB of memory.

Given the advanced features of D2 Viper-this is a bargain that developers, VARs and IT organizations cannot afford to ignore. One of the most popular databases available for large scale enterprise applications can be used free of charge for development, testing and most small to medium scale deployments - easily in the 10-100 user range for many database applications. Again, this is very attractive, and not to be missed.

The new DB2 features can be classified in a number of ways targeted toward end users, feature sets, or functional attributes. We shall take the latter approach and classify the new features under four headings:
- Administration and operation enhancements
- XML Native Storage Support and integration
- New database functional features
- Runtime storage and performance improvements.
IBM claims that these new features make Viper one of the most comprehensive set of updates for DB2 in at least a decade.

Yet despite the major new XML and other improvements, the interfaces to DB2 have remained consistent. So this means that developers and DBAs will still be operating in familiar DB2 working environs. And therefore, because IBM has done such a remarkable job of integrating the new features, especially native XML, into the existing interfaces - the learning curve is reduced considerably. True with new DB2 Developers Workbench and XQuery Builder there are new features and designers to master; nonetheless IBM has made a sweeping update very approachable. So lets look at each of the DB2 updates in more detail.

Administration and Operational Enhancements

IBM is calling its improvements to automated administration Autonomic Computing. In effect the database offers, often by default, to do a number of tasks (including workflow sequences of steps) that would ordinarily be performed by a DBA or system analyst. However, the DBA can easily intervene, reconfigure and or completely takeover the tasks manually. This screenshot of the DB2 Control Center shows the direction:
Note in the lower right Data Object Info Pane some of the Autonomous tasks that DB2 manages. Some of these tasks started as early as DB2 versions 6 and 7 but many have seen enhancements through to version 9. Here are some examples of version 9's Autonomic Computing refinements:
Adaptive, self-tuning memory allocation - helps reduce or eliminate the task of configuring your DB2 server by continuously updating configuration parameters and resizing buffer pools. When enabled, this feature dynamically distributes available memory resources between different points of memory consumption. Note DBAs get to choose this option at database creation time.

Automatic storage support - automatically grows the size of your database across disk and file systems. It eliminates the need to manage storage containers while preserving the performance and flexibility of database managed storage. In DB2 Version 9.1, automatic storage support has been added for multi-partition databases. Automatic storage is enabled by default when you create new databases but DBAs can turn it off and manage it manually as required.

Automated statistics collection - called RUNSTATS, is enabled by default when you create a new database. The DB2 server collects statistical information about your data in a background process as and when required. The DB2 optimizer uses this information to ensure the most efficient retrieval of information from the database. The database stats are also used for Control Center reports. This is an example of automated admin steps.

Automatic configuration of pre-fetchers and page cleaners - can be automatically determined by the DB2 database system based on environment characteristics such as number of CPUs, number of database partitions, and parallelism settings of the table spaces in the database. This allows DBAs and operators to let DB2 take care of the basic operational storage settings and then with DB2 Health Monitor, tune these settings appropriately.

Automatic table and index reorganization enhancements – provide new policy options for automated table and index reorganization. This in turn provides your database administrator with more capabilities for managing table and index reorganization on an as-required basis eliminating drudge work.
In addition, there a number of diagnostic functions/utilities that are part oof the Health Monitor that are automatically dispatched to help diagnose problems. The idea behind Autonomous Computing is that the demand for database systems is continuing to grow exponentially and the bottleneck for effective use is the huge operational and administrative load these systems put on IT staff. DB2's Autonomous Computing is designed to relieve that admin load. The key for it being effective is that Autonomous Computing is used to supplement and automate relatively simple database admin tasks alerting the responsible staff with email notices and statistics when operational performance or storage starts to degrade. But also DB2 provides convenient override capabilities as well so DBAs and operational stay in control. As we show in a separate UI review , the Control Center GUI provides a wealth of dialogs, wizards, conveniently grouped commands plus some novel help assists to expedite the effective use of Autonomous Computing's aids to DB2 management and control.
XML Integration
Five major software vendors are staking everything on the emergence of SOA-Service Oriented Architecture and its underlying XML support systems as being a major factor in software and application development going forward – BEA, Microsoft, Oracle, SAP and IBM. Each is putting its R&D money where it thinks it can get the most leverage out of XML. IBM has put its bucks into a very comprehensive delivery of native XML support into DB2. Native XML is a datatype in DB2 and it means the raw XML in validated form is stored within the database – there is no parsing and decomposition of the XML data into underlying relational datatypes for storage purposes – the full hierarchical data is stored within a row and its XML column value. This data is validated against a predefined XSchema. Even more interesting XML columns can be mixed with VARCHAR, INTEGER and the full set of DB2 relational datatypes in a table and there are some application advantages for doing so.
As IBM advises Native XML data store protects the integrity of your XML data. Shredding XML data into relational tables compromises the digital signatures and other critical meta data that accompany your data. Because DB2 native XML data store does not shred or decompose your XML data, your original XML document, including digital signatures is protected. Native XML data store also allows you to avoid the resource and performance costs associated with rebuilding the XML document every time it is retrieved. Native XML data store indexing provides even higher speed search retrieval. These capabilities can be useful for web services that exchange data in XML format, or business models that require XML documents to be stored intact.”

What is notable about Viper's implementation of the XML datatype in DB2 is that it is complete and comprehensive. XML functionality in DB2 includes SQL syntax and functions; Cobol, C, Java and other programming language-to-database interfaces and APIs; indexing and performance tuning functions and utilities; load/export, data movement, backup and replication facilities; plus Explain and optimizer support. DB2 tools, including the Control Center, Health Center, Replication Center and other tools and utilities have been updated to also provide support for XML data.

However, there are some gaps – partitioning and clustering have limitations with tables containing XML datatypes. In addition XML support on the iSeries and zOS versions of DB2 are not uniform with DB2 Viper for Linux, Unix and Windows.
From the application development side there are new SQL/XML functions allowing comparison, extraction and publishing of the XML data. More importantly a robust implementation of XQuery allows query and update operations on XML values. Also, like SQL Assistant, there is an automated XQuery Builder that considerably simplifies development and refining of XQuery statements. In addition, the Eclipse based Database Workbench also supports XML and XQuery wizards and dialogs. DB2 Viper is truly a hybrid database incorporating the hierarchical features of XML data within a relational framework. It is interesting, just as IBM adds DB2 XML features and functionality it deprecates and/or removes a number of object extensions in DB2 – notably for Text, Audio, Image and Video extenders (spatial extenders and functions are retained). In the process are XML objects and schema becoming the “super objects/extenders” of DB2 databases ?
New Database Functional Features
There a number of individual improvements to DB2 that are noteworthy because developers who work in the trenches will appreciate these improvements to "their" database. Of course, the native XML support dominates the feature set. But look at these as well:
- a number of SQL J and JDBC enhancements across the board
- expanded .NET and Visual Studio support including add-ins for new XML features
- C/C++, Java, and Visual Basic improvements in over half a dozen OS platforms
- Eclipse-based Developer's Workbench replace Development Center; new focus for appdev
- More trusted connections to z/OS mainframe platforms
- extension of the usage of Table() functions for user defined database interactions
- new BINARY, VARBINARY and DECFLOAT datatypes
- Rebuild database function provides new restore options
- IPv6 support added to TCP/IP connectivity
- two phase commit added to federated, non-IBM database support
- support for multiple versions and fixpacks on one development server
- extensive 32 and 64 bit support (but still limited 32 -> 64 bit migration capability)
- extensive partition op support including monitoring, load, and DB health reports
- a series of security enhancements including authentication and privilege control
These are the types of improvements, large and small, that make the database more functional to DBAs and developers. Perhaps the most important is the new Eclipse Developers Workbench. So we plan to do a separate review of that application interface.

Runtime Storage and Performance Improvements.

Usually in database development, there are storage and performance improvements or functional additions and new features. DB2 Viper has done both. For example in the storage arena there are two major improvements. First, there is improved large database management using table partitioning added to DB2 database partitioning capabilities. Viper provides three methods for table partitioning allowing better data warehousing options and performance as well as traditional OLTP-OnLine Transaction Processing applications. Even better both database and table partitioning can be used together allowing for speedier access to enormous databases. However, there are some limitations – the new partitioning features do not strictly coincide with the syntax provided in iSeries and mainframe zOS versions of DB2. Also the new Native XML datatype precludes using table partitioning on those tables.
Viper also adds row compression which can save both storage but also may speed up performance in some instances. Row compression can be done on a table basis or by selected columns in limited default value/Null compression. In the case of Native XML data, Viper performs some automatic compression on attributes and element names longer than 4 bytes. Viper's RUNSTATS utility certainly helps in tuning compression. And in addition, views can now have statistics gathered for them and that definitely improves Optimizer assessments of queries incorporating those views. In addition to these major improvements there have been a number of storage and performance enhancements:
- MQT-Materialized Query Tables important in BI apps add performance improvements
- Load operations have been facilitated with users exits and cursor based loads
- SET INTEGRITY allows fine tuning of access during OLTP queries
- use of Indexes and RUNSTATS utility can improve native XML table performance
- allowance of larger temp tables/indexes that can be defined on a greater number of tables
As of early summer 2006, DB2 had the best TPC-OLTP benchmarks and some of the best TPQ-BI benchmarks. With the Viper, IBM has managed to extend its lead in the TPC space with over 3 times the speed of Microsoft SQL Server 2005 and 2.5 times better than Oracle 10g. And the price/performance advantage of Viper is almost 25% better yet again. This is just another reason that developers should consider DB2.

Summary

DB2 has loaded itself for the big bears of XML, SOA and Web Services development. Native XML support is built across the product. But at the same time IBM has been able to expand on its Enterprise caliber clustering, partitioning and grid/parallel processing capabilities. And best of all, DB2 Viper deliver faster performance at an even better price performance ratio. Not to shabby for a database that has been consigned to "the drudgeries of automated health and maintenance support tasks" as one of my Redmond database friends noted recently. When Microsoft gets its SQL Server benchmarks within shouting distance of DB2 , I and readers should then start to take the remarks seriously. Meanwhile, give DB2 Express C a try - it is a free DVD kit or download - and definitely worth the while.


Top of Page  Home  Tutorials 

Developers Weblog  Graphics Reviews and Tips