A look at the new functions in DB2 Universal Database V8.2

DB2 V. 8.2 is full of new features that cut the time-to-value for your on demand solutions. It features enhancements for application development, vast improvements in lights-out operation and manageability, high availability, security, and a whole lot more.

Paul Zikopoulos (paulz_ibm@msn.com), Senior Database Specialist, IBM Toronto

Paul Zikopoulos photoPaul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 and has written over sixty magazine articles and several books about it. Paul has co-authored the books: Information on Demand: Introduction to DB2 9 New Features, IBM DB2 9: New Features, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë - his new daughter. You can reach him at: paulz_ibm@msn.com.

08 April 2004

Also available in Japanese


If you’ve opened an IT magazine or participated in an IBM® DB2® Universal Database™ (UDB) briefing lately, it would have been hard to miss the buzz around DB2 UDB Version 8.2 (DB2 V8.2), the newest release of DB2 UDB for Linux, UNIX®, and Windows®).

DB2 V8.2 is full of about 175 new features that cut the time to value of a DB2 UDB solution with enhancements for application development, vast improvements in ‘lights-out’ operation and manageability, high availability, security, and a whole lot more. DB2 V8.2 packs more features than many vendors put into a new version. In this article I hope to give you a high-level view of some of these features and how they can help you.

Application development enablement

DB2 V.8.2 accelerates the application development lifecycle by simplifying and minimizing the development cycles and costs associated with building and deploying on demand e-business database applications.

A great deal of work has gone into the development plug-ins for the world’s most popular integrated development environments (IDEs) like Microsoft Visual Studio .NET, WebSphere® Studio Application Developer, the Eclipse framework, and the Rational® Developer toolset (including enhancements for both .NET and Java™) for application and data modeling. The DB2 UDB development team puts so much effort into these plug-ins so that developers don’t need to learn a new IDE or programming model to develop DB2 UDB applications.

DB2 V.8.2 enriches the DB2 UDB .NET developer experience with support for .NET stored procedures based on the Microsoft common language runtime (CLR). CLR is a virtual machine that provides code execution services; for example, memory management, thread execution, code execution, code safety verification, etc. CLR is to .NET what the Java virtual machine (JVM) is to Java.

In DB2 V.8.2, developers can write encapsulated business logic in C# or Visual Basic .NET and can execute and manage that logic natively in the database. If you’re not familiar with what’s already in DB2 UDB for .NET developers (and there is a lot), check out the plethora of articles and tutorials by Abdul Al-Azzawe.

A new DB2 Class Library object has been added to the Visual Basic and Visual C#® project folders to help build .NET routines. This object is based on the standard C# Class Library template (the Visual Basic DB2 Class Library is obviously based on the Visual Basic template) with an enhancement for automatic project reference to the IBM.Data.DB2 managed provider assembly, and a built-in example of a simple CLR method.

Figure 2. DB2 Class Library icon

When you create a new class library project using the DB2 Class Library project template, a DB2Class1.cs file is also created. This file contains a default CLR method that has the DB2 required signature for a CLR procedure. Once you have defined this, you are ready to build your .NET-based stored procedure. Of course, there is a wizard to help you do this and that wizard can automatically detect the list of CLR projects in your solution, and from there the classes and methods that qualify for a valid DB2 CLR procedure.

Figure 3. Default CLR wizard

Once you have defined your CLR methods, you also need to ensure that the assembly containing the CLR procedure is deployed to the local or remote DB2 UDB server. As you’re probably expecting (or hoping for), the DB2 database project also supports the management of assemblies that are automatically deployed to the server at project build time as required. This means that only when the assemblies are modified (or the first time they are built) they are deployed to the server.

Figure 4. CLR assemblies

Native CLR support is just the tip of the iceberg for .NET developers. The IBM Explorer plug-in is being enriched to include very powerful schema operations, such as the ability to create tables, views, indexes, the auto-generation of DDL for DB2 UDB objects, reusable data adapters, and automatic discovery of DB2 UDB databases on the Network.

You can learn more about the new .NET enablement features at http://www-106.ibm.com/developerworks/db2/library/techarticle/0311alazzawe/0311alazzawe.html.

Developers who program logic in SQL/PL will be delighted to find out that they no longer require a native C compiler on their distributed workstations to develop their code. DB2 V8.2 gives you the option to run SQL/PL as byte code in the engine. This implementation should result in quicker performance (the byte code sits in the DB2 UDB catalog and can be as efficient as the compiled version, if not more so) with less complexity with respect to setting up a compiler or using the GET and PUT routine functions for routine deployment. Now, building a SQL/PL routine is now as simple as installing DB2 UDB on a distributed platform (future support for the removal of the compiler prerequisite for z/OS is in the works) and running the CREATE PROCEDURE statement.

The SQL/PL architecture today looks like:

Figure 5. SQL/PL architecture

In DB2 V8.2 it will look even simpler:

Figure 6. SQL/PL architecture in DB2 V8.2

To also help with SQL/PL stored procedure development, DB2 UDB will support statements up to 2 MB in size, a nice increase from the previous limit of 64 KB.

DB2 V8.2 also enables you to call a stored procedure from a trigger. This improvement simplifies logic code in that developers can encapsulate and share it amongst triggers, instead of having to recode them for each trigger.

Other enhancements in DB2 V8.2 include toggleable generated columns, nested save points, the ability to set wait time at the statement level to override global timeout settings, and the ability to dynamically optimize a statement at run time (useful for key tables whose statistics change often).

DB2 UDB information

The DB2 UDB Information Center has been completely revamped to use the Eclipse framework for online help. This fresh new interface delivers a lightning-fast search mechanism with an easy-to-use interface:

Figure 7. DB2 UDB Information Center

You can actually use this help system today; it’s available at: http://publib.boulder.ibm.com/infocenter/db2help.

In addition to this, the DB2 UDB help is now cataloged and searchable with the Google search engine, such that searches on DB2 UDB will result in hits that link to this online help system.

Figure 8. Google search for DB2 UDB data


DB2 V8.2 comes with an extensive plug-in framework that expands the DB2 UDB underlying operating system or the Kerberos-supported security system. The new plug-in architecture allows you to completely customize the authentication mechanism to handle any group membership, client-side authentication, or server-side authentication operation. Essentially, security administrators can code to the GSS-API and implement virtually any supported security mechanism that supports this interface. For example, you could write a plug-in to leverage LDAP authorization lookups.

DB2 UDB for Windows users will appreciate some of the changes for security in DB2 V8.2. For instance, the characters that DB2 UDB wouldn’t previously let you include in user names will now be supported.

More importantly, DB2 UDB will support the Windows Local System Account (LSA) and recognize it as a system administrator. This allows DB2 UDB applications (including the DAS scheduler) to run under this account when accessing DB2 UDB, rather than having to specify or create an externalized user account.

Finally, DB2 V8.2 will support two-part user names. This support allows DB2 UDB to understand the <domainname>\<username> format for authentication. Previously, if DB2 UDB was operating in a multiple-domain environment, the user name passed to DB2 UDB for authentication had to be broadcast to all the domain servers to try to find where the user name was defined. This caused performance issues at connection time as well as creating the potential for authorization conflicts. All this is solved in DB2 V8.2.

DB2 V8.2 also has the ability to encrypt data on the wire between the client and server. The implementation will be 56-bit encryption to start, to keep compatibility across the DB2 UDB family into the DB2 UDB for z/OS® platform. DB2 V8.2 will also be testing for the Common Criteria certification with an accompanying new security manual that brings all the relevant concepts together.

Manageability and autonomics

With respect to manageability and autonomics, there are many new features in DB2 V8.2. These features’ main goal is to push more and more automation into deployment tasks and improve manageability, thereby freeing up skilled database administrator (DBA) resources to focus on business-critical issues and further reduce the total cost of ownership (TCO) of a DB2 UDB solution.

DB2 V8.2 includes automated policy-based monitoring that can alert you to the need for, or perform, automatic maintenance operations, such as backup (which, by the way, is self-tuning in DB2 V8.2 and, for online backups, will include the log files), table statistics collection (which uses sampling and is 100% transparent to users and applications), and online table reorganizations.

Figure 9. Configure automatic maintenance

The Configure Automatic Maintenance wizard allows you to define periods of null or low activity levels for a specified database, choose support maintenance utilities that DB2 UDB will handle automatically for you, choose a set of Staff and Power users to be notified if maintenance is unsuccessful (or needed if you don’t want DB2 UDB to run the utilities for you when it determines a need for them).

Figure 10. Control Center Personas: Basic, Advanced, and Customized

The Control Center has also been greatly enhanced so that it runs faster, it can have different personas (Basic, Advanced, and Customized), and it has a built-in dashboard displayed in the main window pane. For example, table space container information and a database’s health status will be highlighted in the Control Center in this dashboard when a database is selected, with no need to drill down or expand menus.

Figure 11. Database dashboard

To further help for ‘lights-out’ management, DB2 V8.2 also includes an enhanced memory model that can steal from other heaps to accommodate needed shifts in memory for health management.

Greatly enhanced advisors help with health alerts, and more utilities can be throttled (added RUNSTATS to BACKUP and REBALANCE).

DB2 V8.2 also contains some first steps toward automated schema evolutions too, with features that simplify changes to the schema, such as renaming columns, removing them, increasing their size, and changing default column values.

The DB2 UDB Design Advisor wizard has been enhanced to include recommendations for materialized query tables (MQTs), partitioning key selection in a partitioned database environment, and multidimensional clustering (MDC) candidates, in addition to indexes. These advisors consider a workload and recommend a schema to support it.

Figure 12. Design advisor in DB2 V8.2

Figure 13 shows the kind of results possible with tools like the Design Advisor. In an internal experimental test, the Design Advisor was able to increase the performance of an un-tuned DB2 UDB database by 84%! (Of course results will vary, but this test run was performed with the TPC-H query workload on 1 TB of data. The database server was an 8-way box running AIX® with 4 logical partitions). To achieve these performance enahncements, the advisor recommended the creation of 20 new indexes, a 6 dimension MDC table, 4 new partitioning keys, and 2 materialized query tables.

Figure 13. Performance improvement

High availability

DB2 V8.2 provides new features to enrich the resiliency of the DB2 UDB OnDemand database for continuity of business operations or disaster recovery services.

The biggest feature for high availability in DB2 V8.2 is high-availability disaster recovery (HADR). This implementation is based on the Informix® implementation of HDR. HADR is an easy-to-use data replication feature that provides high-availability (HA) solutions for both partial and complete site failures.

Figure 14. High Availability Disaster Recovery

Essentially, HADR is log shipping (which DB2 UDB supports in previous versions) but from the log-buffer instead of from a hardened disk log; this method provides a lot of granularity to suit your solution’s high-availability requirements.

For example, the HADR feature in DB2 V8.2 allows you to select three levels of data protection:

  • Synchronous (zero data loss) - The log data is flushed to stable storage at the standby before committing on the primary. The standby system does not write the log until it hears from the primary that that the same log is on disk there. At the same time, the primary server will not proceed on to the next log flush until it receives notification that the standby has written the log. Ultimately, a COMMIT succeeds when the log data is on disk at the primary and at the standby, thus ensuring zero data loss.
  • Near synchronous - In this mode, the log data is guaranteed to be successfully sent to the standby site, but it might not have been flushed to stable storage when the commit on the primary succeeds. The log write at the primary and the send to the standby are performed in parallel on the primary server. In this mode, a commit succeeds when the log data is on disk at the primary and it has been received by the standby server. This mode has the potential for data loss, but only in the event where something has happened to both servers pretty much at the same time.
  • Asynchronous - This mode provides that least protection from data loss. An asynchronous HADR setup will only guarantee that the log data was passed to the TCP/IP stack and the socket send call was returned successfully. What’s important to note here is that the return of the socket send call is not an acknowledgment of its successful receipt by the standby server, so if the connection breaks, the standby may not have received everything sent by the primary server. There is some protection guarantee in this mode in that TCP/IP sockets do guarantee delivery order, so while the socket stays alive there will never be missing or out-of-order packets as seen by the standby server. In this mode, the log write at the primary and the send to the standby are performed in parallel at the primary server; a COMMIT succeeds when the log data is on disk at the primary and it has been sent to the standby
Figure 15. Status of HADR

Keeping the "ease of use" mantra to all of the new features that are driven into the DB2 UDB product, the whole HADR scenario can be set up and managed (for example, resynchronizing the primary and standby servers after a site failure) using graphical wizards.

Figure 16. Configure database logging wizard

HADR also allows you to do rolling upgrades (for example, upgrading your version of DB2 UDB or the operating system) without having to suffer an outage.

Client Reroute is another new feature in DB2 V8.2 that works with any disaster recovery scheme in DB2 UDB, not just HADR. Applications that encounter an outage at the primary database don’t expose this problem to the end-user; rather, the application will switch to a cataloged secondary server and issue a reconnect. DBAs who sign up for tight service-level agreements (SLAs) are going to love this new feature.

More features in DB2 UDB DB2 V8.2

There is a whole host of other items in DB2 V8.2 that I have not covered in this article. Here’s a list of some other items that are bound to grab your attention:

  1. Q-based replication, using WebSphere MQ® for highly available and fast-performing replication schemes
  2. A further extension of a "location-aware" DB2 UDB with the new DB2 Geodetic Extender (the DB2 UDB port of the Informix Geodetic DataBlade). This extender adds richer location semantics over the traditional latitude/longitude projections available with the DB2 Spatial Extender
  3. J2EE 1.4 and JDBC 3.0 compliance with the new DB2 UDB universal driver (which includes significantly enhanced SQLJ support)
  4. Support for the 2.6 Linux kernel, allowing a DB2 UDB for 64-bit Linux to run anywhere
  5. Numerous Informix compatibility features like various OLTP performance enhancements, Informix 4GL to IBM EGL tooling and toolkit, etc.
  6. Information OnDemand anywhere and anytime, with the MobilityOnDemand feature which gives DB2 UDB servers entitlements to DB2 Everyplace (this is not new to DB2 V8.2, but it is worth noting as this entitlement was added in DB2 UDB v8.1.4)

I hope this article gives you a taste of what we in the database development teams here at IBM have been up to. I encourage you to register and download a copy of DB2 V8.2 and check out all its amazing features for yourself.


Special thanks to Abdul Al-Azzawe, Dale McInnis, Peter Kohlman, and Gwyneth Evans for their contributions to this article and their development efforts into some of the features outlined in this article.


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=A look at the new functions in DB2 Universal Database V8.2