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.
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
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:
CREATE TABLESPACE ts1 MANAGED BY AUTOMATIC STORAGE
You can also define policies for storage growth and limits:
CREATE TABLESPACE ts2 INITIAL SIZE 500K INCREASE SIZE 100K MAXSIZE 100M
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.
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:
- Support for multiple DB2 installations on the same system
- Ability to run ODBC and CLI applications without the DB2 client
- IPv6 support
- Increased table size limits and capacity
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.
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 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.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
- The article IBM DB2 "Viper" revs XML engineInfoWorld, May 2006, describes many of the new features of DB2 9.
- Read more articles about DB2 9 on developerWorks.
- Visit the developerWorks Information Management zone to expand your skills on IBM Information Management products.
- Visit the developerWorks DB2 for Linux, UNIX, and Windows resource page to read articles and tutorials, and link to documentation, support, learning resources, and more.
- Stay current with developerWorks technical events and Webcasts.
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.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.