Introducing DB2 9, Part 4: Autonomic and other enhancements in DB2 9

DB2® 9's autonomic features help you derive more business value from your data, while spending less time managing it. This article introduces some of the autonomic capabilities that are new in DB2 9 (formerly codenamed "DB2 Viper," and describes other noteworthy enhancements that are not covered in the other articles in the Introducing DB2 9 series.


Rav Ahuja (, DB2 Program Manager, IBM

Rav Ahuja is a worldwide DB2 program manager based at the IBM Toronto Lab. He has been working with DB2 for Linux, UNIX, and Windows since version 1 and has held various roles in DB2 development, technical support, marketing, and product strategy. He works with customers and partners around the globe helping them build and benefit from DB2 and services-based solutions. Rav is a frequent contributor to DB2 papers, articles and books. He holds a Computer Engineering degree from McGill University and MBA from University of Western Ontario.

01 June 2006

Also available in


IBM® has been a pioneer in autonomic computing. DB2 Version 8 introduced a number of self-configuring, self-optimizing, and self-healing capabilities. DB2 9 continues along this strategy of helping you derive more business value from your data, while spending less time managing it.

This article introduces a few of the autonomic capabilities that are new in DB2 9. It also mentions a few of the other noteworthy enhancements that are not covered in the other articles in this series.

Automation automatically

Newly created DB2 9 databases are now self-configuring and self-maintaining by default, which means that at database creation time, you no longer need to worry about memory management, storage management, performance tuning, or database maintenance planning. Simply create a database and let DB2 take care of everything else.

In addition to the self-tuning memory management, some of the other autonomic enhancements in DB2 9 include:

These autonomic features are designed to make database administration effortless, improve administrator productivity and effectiveness, and thereby lower maintenance costs. These features are also beneficial for application developers and vendors, as they permit them to focus on application development, rather than worry about database configuration or deployment.

Auto-configuration enabled by default

Version 8 of DB2 introduced the Configuration Advisor that detected the system and database characteristics – CPUs, memory, database size, number of tables, etc. and recommended values for configuration parameters. DB2 9 goes even further – it runs the configuration advisor automatically, and makes some basic tuning decisions by default. For example it configures the size of the default buffer pool, I/O cleaners and I/O servers, etc. This initial automatic tuning means that your database will have better performance than a database created with the previous database configuration default values, enabling better out-of-the-box operation.

Automatic database maintenance

In order to simplify maintenance operations, DB2 9 also enables automation of certain ongoing tasks by default. For example, it automatically executes the RUNSTATS utility on a periodic basis to update statistics about the physical characteristics of a table and the associated indexes. The DB2 optimizer uses these statistics to determine the most efficient access plan for any given query, which improves query performance.

DB2 9 also has graphical wizards for creating databases with automated maintenance or configuring automated maintenance for existing databases. In addition to the RUNSTATS, you can automate database backups and REORGs (defragmentation of table and index data). The way automated maintenance works is that you define maintenance windows of low or no activity, and if DB2 determines the need for maintenance using evaluation criteria, it performs the necessary tasks during the next available window.

Figure 1. Configure automatic maintenance
Configure automatic maintenance

Automatic storage management

DB2 9 extends the automated storage features first introduced in DB2 V8.2.2. Automatic storage automatically grows the size of your database across disk and file systems. It eliminates the need to manage storage containers while taking advantage of the performance and flexibility of database managed storage. In DB2 9, automatic storage is now enabled by default when you create new databases. In addition, automatic storage support has been added for multi-partition databases.

With this feature, you no longer need to worry about tasks such as creating additional table spaces for capacity, adding containers, and monitoring container growth. And when you want to restore a database backup, say on a different system (with potentially different directory or path structures), you can redefine the storage paths, such that the new paths are used instead of the ones stored in the backup image.

Previously, for example on a Windows® system, we could create a database on C. Now you can create a database on C, and D, come back later and alter the database to add E and F drives and DB2 manages the space underneath using certain policies. The following examples illustrate automatic storage usage on UNIX® and Linux® systems.

When a database is created, you can specify the storage pool for use by the database. If no storage paths are specified, the default database path (dftdbpath) is used.

CREATE DATABASE test on /data/path1, /data/path2

You can later add additional storage paths to the pool:

ALTER DATABASE ADD STORAGE /data/path3, /data/path4

Previously when you created table spaces you had to specify containers for them. You can now have them automatically use the database storage pool:


You can also define policies for storage growth and limits:


These examples highlight the simplicity and flexibility of automatic storage.

Changing default automation

In DB2 9, features such as automatic configuration, RUNSTATS, self-tuning memory, and automatic storage are enabled by default when you create new databases. This is performed with the intent of making it easy to configure and optimize your environments. However, if you prefer to configure and tune these features yourselves, DB2 provides the option to disable default automation. Likewise, if you have databases being upgraded from previous versions of DB2, these existing databases will not have these features enabled by default. This is to preserve any well-tuned settings and ensure predictability for your systems. To take advantage of the new automated capabilities, you need to explicitly enable the new automation features for existing databases.

Other improvements

DB2 9 includes too many enhancements and improvements to list all of them here. This section discusses some of the other noteworthy enhancements for this new version:

Multiple DB2 installations on the same system

DB2 9 allows for the installation of more than one copy of the data server or client software on the same system. Furthermore, you can:

  • Install anywhere: You can install DB2 database systems using any path that you choose.
  • Install any number of times: You can install two or more copies of DB2 9 on the same operating system image. Each copy can be at the same or different code levels.
  • Service each copy independently: You can update one copy without affecting any of the other ones.

This has several benefits:

  • Deployment independence: Independent copies of DB2 can be used for different purposes and groups. This independence allows different databases on the same computer to run at different fix pack levels. For example, one group, such as human resources, can apply fixes without affecting other groups, such as finance.
  • Product life-cycle flexibility: You can deploy one version of DB2 for production, and a newer llevel to test new fix packs. Then, the production instance can be rolled over to the new installation path on an instance-by-instance basis.
  • Improved embeddability: Application vendors can embed their own copies of the data server software and run independently of other applications that need to use DB2.

Running ODBC and CLI applications without the DB2 Client

Runtime deployment of DB2 9 applications that use the CLI or ODBC interfaces is now easier. A new driver -- that also ships independently of the DB2 server or client packaging -- provides runtime support and remote connectivity for ODBC and CLI applications.

There are various installation options available for this new DB2 driver for ODBC and CLI:

  • You can install the driver on a machine that already has a DB2 client installed.
  • You can have multiple installations of this driver on a single machine.

Being able to install the DB2 driver for ODBC and CLI without a DB2 client or server makes deploying database applications easier:

  • You can include the driver in your database application installation package.
  • Distribution size, installation footprint, and memory footprint are reduced.

IPv6 support

DB2 9 supports IPv6. IPv6 is an evolution of the current version of IP (Internet Protocol), which is known as IPv4, and has no practical limit on global addressability. Due to IPV4’s limited number of useable global addresses, IPv6 is expected to gradually replace IPv4.

IPv4 will continue to be supported on all platforms in DB2 9 and will remain as the default protocol. On the server, if IPv6 is not configured, then DB2 will only listen on all IPv4 addresses as in previous versions. If IPv6 is configured, then DB2 will listen on all IPv4 and IPv6 addresses. To configure IPv6 on DB2 9.1, catalog the TCPIP node using the new CATALOG TCPIP6 NODE command.

Increased table limits and capacity

A partitioned table in DB2 9 can be up to 512 exabytes in size! This is a result of supporting large record identifiers (RIDs) in DB2 9. RIDs are used to reference objects in a table space. They specify the page number and the slot on the page that a record is located in. Larger RIDs imply more pages can be referenced in table space, and potentially more rows per page, resulting in much higher table capacity.

Figure 2. Large RID support
Large RID support

Large RIDs are a default for DMS (database managed) table spaces and those created with automatic storage. A non-partitioned table (using 32KB page size) with large RIDs can hold 16 terabytes or 1.1 trillion rows of data.

In addition to increasing database capacity, large RID support can also simplify table space management (through consolidation) and improve storage and memory utilization.


DB2 9 is designed to provide a simpler, lower-cost way to administer databases, scale them to new levels, and improve development and deployment productivity. Download DB2 9 and try out the new features for yourself to see the benefits you can derive.



Get products and technologies

  • Download DB2 9 to try out the features described in this article.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management
ArticleTitle=Introducing DB2 9, Part 4: Autonomic and other enhancements in DB2 9