Database, heal thyself

IN ANY ENTERPRISE, simplifying database administration while increasing developer productivity is a highly worthwhile quest. On both counts, shops that currently use IBM’s DB2, as well as sites considering a database addition or change, will want to inspect the latest release of DB2, Version 8.1.

We examined DB2 8.1 on Unix, Linux, and Windows platforms and were quite impressed with the changes and additions found in this release. In particular, IBM has added a number of improvements that are focused on increasing the autonomic (i.e., self-configuring, self-optimizing, self-managing) capabilities of DB2.

Chief among these is the Configuration Advisor, available from the Control Center, which asks basic questions about your database and provides tuning recommendations for queries, transactions, or both. For example, we could improve transactional performance by specifying the average number of SQL statements per unit of work and the estimated number of transactions expected per minute, or better manage database connections by specifying the average number of local and remote application connections. We also were easily able to set the isolation level for our databases in the Configuration Advisor.

Once a DBA has completed the Configuration Advisor setup, DB2 provides a summary of the recommendations for the database to maintain optimal performance. The recommendations can be saved for later implementation (pending management approval, for example), executed immediately or on a scheduled basis, or saved to the Task Center for manual execution.

A useful companion to the Configuration Advisor is the Design Advisor, which helps boost performance by recommending and creating indexes. Design Advisor’s interfaces are similar to the Configuration Advisor, and we had no trouble stepping through the various panels. After specifying a workload — a set of related SQL statements and their frequency — the Design Advisor provided index recommendations and allowed us to selectively or completely implement them. As with the Configuration Advisor, we could execute the recommendations immediately, as a task, or on a scheduled basis.

Exercising self-control

After configuring databases, administrators can turn to the included monitoring tools, which we found simplified ongoing database management considerably. Among these are the Health Center, the Memory Visualizer, and the Event Analyzer.

The Health Center provides an easy-to-use GUI that will help administrators better manage a number of key database attributes. Among these is an indicator to monitor the operational state of the database. For example, we could monitor the percentage of our applications that were waiting on locks by setting both warning and alarm conditions.

Other available metrics that can be monitored include lock escalation rate, lock list utilization, logging and heap utilization, cache and workspace hit ratios, and sorting utilization. No doubt IBM will add more indicators in subsequent releases, but the available indicators currently number fewer than 20, and we could not find a way to add custom indicators.

Nevertheless, although in its first release within DB2, the Health Center already is supplying a lower level of monitoring capabilities than most system management tools, while reaching the granularity currently found in midtier analyzers. As the capabilities of the Health Center expand, enterprises can expect a decrease in downtime as critical databases can be closely monitored and proactively managed.

Beyond sending out alerts and alarms, the Health Center also let us specify actions to take when certain thresholds were encountered. For example, we created a task to process log file maintenance when utilization reached a certain threshold. Likewise, we created a script that saved logging data and restarted a DB2 instance when an inoperable state was reached. Such capabilities will allow administrators to be extremely proactive in database management.

The other included monitoring tools, Memory Visualizer and Event Analyzer, are useful for gathering a variety of statistics. We particularly liked the Memory Visualizer, which let us track memory usage on an hourly and daily basis. DBAs can use this data to periodically review DB2 configurations and plan for growth.

The autonomic capabilities that IBM has added in DB2 v8.1 will provide a good productivity boost to experienced DBAs and stretch the capabilities of admins new to managing databases. We particularly liked the fact that, in adding autonomic features, IBM has not removed the ability for seasoned DBAs to make lower-level customizations when necessary.

Another improvement in DB2 8.1 that will save administrators time are the online maintenance capabilities. Now many DB2 management tasks that previously required scheduled downtime can be performed on active databases. Among these are table and index reorganization and load and index creation.

Simplifying integration

With this release, IBM continues to add capabilities to DB2 that will help developers complete business process integration projects more easily. For example, using DB2’s XML Extender support, we found it easy to store XML documents in the database, as well as access and update them. IBM currently supports XPath in DB2 8.1, but support for XQuery will come in a later release.

DB2 8.1 enables XML validation using XML Schema files instead of DTDs (Document Type Definitions). In addition, DB2 supports XSLT (XSL Transformations), and the XML Extender gains new support for three additional data types in this release.

MQSeries integration in DB2 8.1 allows XML documents to use message queuing functionality to extend the range of potential application designs. For example, we used the MQSeries Assist Wizard to create a table function that would read messages from our MQSeries queues, allowing us to pull data from legacy apps and make it available via the Web or as a Web service. Finally, taking another important step toward more flexible e-business, DB2 can now act as both a provider and a consumer of Web services based on Apache SOAP.

These XML and Web services enhancements should speed up enterprise integration projects. The autonomic capabilities in DB2 8.1 are also a good leap forward for enterprise customers, as they enable a significant reduction in the cost of managing the database while also increasing uptime.