This article explores the common aspects of the DB2 Universal Database family, including SQL, administration, and query optimization.

Kent Milligan (kmill@us.ibm.com), DB2 UDB Technology Specialist, IBM Rochester

Kent Milligan is a DB2 for i Senior Certified IT specialist in IBM ISV Solutions Enablement for the IBM i platform. He spent the first eight years of his IBM career as a member of the DB2 development team in Rochester, Minnesota, and he speaks and writes regularly on DB2 for i relational database topics.



01 April 2002

Introduction

In February of 1999, IBM announced DB2® Universal Database® for iSeries (DB2 UDB for iSeries ), rounding out the IBM DB2 UDB family. This move made IBM the only database vendor to provide complete data management solutions for the highly popular iSeries and AS/400e® operating system, in addition to other user platforms scaling from the laptop to the enterprise.

A universal database extends the benefits and capabilities of modern database technology to nontraditional forms of data, such as information stored in documents, spreadsheets, and multimedia objects. Traditionally, information from relational databases, which has been evolved into "business data in a business context," has been created with the aid of tools such as documents and spreadsheets. It is therefore natural to the business user that it also be stored in this format. DB2 Universal Database provides the new and advanced functions necessary to store, manage, index, and control all forms of information, ranging from traditional database data to documents and multimedia, in a single consistent database architecture. A universal database provides the following capabilities:

  • Universal Access
  • Universal Application Support
  • Universal Reliability
  • Universal Scalability
  • Universal Extensibility
  • Universal Management

With DB2 UDB for iSeries , the DB2 Universal Database product line is composed of three different members. DB2 Universal Database (DB2 UDB for UNIX, Windows, OS/2) is the single product available across all UNIX and Linux, Windows, and OS/2 platforms. It can be viewed as the founding member. The next member to join the product line was DB2 Universal Database for z/OS® and OS/390. And the newest member, of course, is DB2 UDB for iSeries.

The most common misconception about DB2 Universal Database branding is that it infers that a common code base is implemented on all supported platforms and operating systems. In fact, each DB2 UDB brand member's code version is unique and developed by different IBM laboratories, but there is a tremendous amount of technology sharing at different levels that takes place across the DB2 Universal Database brand. The different code bases allow us to exploit the hardware, microcode and operating system of each of the platforms; thus at the lowest level each DB2 is tightly integrated into, and can thoroughly exploit, its operating environment.

At the lowest level, the IBM DB2 development teams have been able to just take code for a new database feature and port if from another code version directly into their own. This sharing helps the DB2 Universal Database brand members deliver new functionality to the market quicker.

At a higher level, IBM's investment in pure research projects has resulted in many patented technologies that benefit all of DB2. Of particular relevance are the number of patents in the field of query optimization and performance. In addition, the IBM DB2 product family also has a wealth of knowledge and experiences that it has garnered from supporting mission-critical applications with relational database management systems since the inception of the relational architecture. These patented technologies and real world experiences have enhanced reliability, performance, and scalability across the entire DB2 UDB product family.


SQL language compatibility

The most important level of sharing and standardization to our DB2 customers and partners occurs at the SQL language level. Each DB2 UDB development team places a high priority on standardizing on a common SQL language. For example, if support for a new function being added to one DB2 UDB product already exists in another, then the SQL syntax for the existing version is reused. And all DB2 UDB development teams place heavy emphasis on delivering SQL that complies with the International SQL Standard. The SQL standard of 1999 (SQL-99) is a particularly good example of how this process works. The IBM DB2 team provided input during creation of the SQL-99 standard and implemented functions adhering to the standard as it was developed. Now that the SQL-99 standard has been published, all of the DB2 UDB development teams are focused on delivering products that are standard-compliant. That being said, the current level of SQL support found in the DB2 UDB product line does not provide 100% percent portability of SQL across the DB2 UDB brand.

When looking at the issue of SQL portability, SQL statements can be broken down into two different types: Data Definition Language (DDL) for creating and managing the database objects and Data Manipulation Language (DML) for accessing and processing the data stored in these objects. DDL usually requires more modifications than DML when moving between the DB2 UDB product members due to the differences in the underlying operating systems. A good example of this is that you will not see any SQL syntax supporting table spaces in DB2 UDB for iSeries since the iSeries and AS/400 operating system automates all of the low-level space allocation and management tasks. DML has a higher level of syntax compatibility (close to 95%) across the DB2 UDB brand because operating system differences tend to have minimal impact on the retrieval and modification of relational data. Vestiges of differences in SQL syntax will persist, however, as many of these differences serve to fully exploit the target platform.

Functional differences are more of an issue than syntax variances when porting to different DB2 Universal Database product members. These differences are due to the fact that not all new features are made available at the same time across all platforms. This difference in timing is caused by the fact that different release schedules and different customer requirements exist for each DB2 Universal Database product. The recent enhancements in Version 5 Release 1 of DB2 UDB for iSeries and DB2 UDB Version 7.2 greatly improved the functional equivalency across the DB2 Universal Database product line.

Stored procedures used to be a feature that had a number of differences across the DB2 UDB brand. DB2 UDB for iSeries was the first brand member to provide support for an SQL procedural language based on the PSM standard, and now Version 7.2 of DB2 UDB makes the SQL procedure language available across all of the DB2 UDB products. In addition, the iSeries with V4R5 added JavaTM stored procedures to match the Java procedure functionality found in the other DB2 UDB products.

SQL standardization across the DB2 UDB product line provides a common set of application programming interfaces for database access. Each DB2 UDB member supports the following database interfaces: ODBC, CLI, JDBC, and SQLJ. In addition, each DB2 UDB product also provides SQL precompilers which allow a developer to embed static and dynamic SQL in portable C and COBOL application programs.

The Open Group's DRDA® (Distributed Relational Database Architecture) defines a common set of flows and protocols for distributed SQL applications, enabling applications to not need to be aware of which particular flavor of DB2 is being targeted. Furthermore, IBM provides a client-side component (DB2 CAE) and DRDA connection (DB2 Connect™) that makes access entirely transparent to applications and also masks the need to know about which networking protocol is being used. DB2 Universal Database, DB2 Universal Database for z/OS, and DB2 Universal Database for iSeries all support DRDA-based access. Support for the XA protocol for distributed transactions is also available across the DB2 Universal Database members.


Administration compatibility

One component with differing levels of function is administrative controls. Again, many of the differences in this area are due to the administrative requirements and operation of the underlying operating system. Several administrative functions are not made available by DB2 UDB for iSeries since the database manager and operating system automatically handle the tasks. For instance, DB2 UDB for iSeries doesn't provide a RUNSTATS utility for optimizer statistics because its database manager keeps these statistics current at all times. Likewise, there's no concept of table spaces in DB2 UDB for iSeries. Because these differences exist, a slightly different skill set is needed for supporting the different DB2 UDB product members. Over time, DB2 Control Center will provide a single graphical interface for customers who need to administer a mixture of DB2 UDB servers in their enterprise.


Query optimization compatibility

Each DB2 UDB product features an advanced, cost-based query optimizer. Since each DB2 UDB query optimizer is designed to fully exploit its target system and hardware, each database engine has its own unique performance personality. Due to this fact, it may be necessary to do some tuning as applications are moved to a different DB2 UDB product. As mentioned earlier, however, a large amount of patented optimization technology and algorithms are common across the different query optimizers.


Middleware and tools compatibility

IBM provides a common set of middleware and tools that works with each of the DB2 UDB product members. As mentioned earlier, DB2 Connect provides common DRDA requester middleware which is used with DB2 UDB servers for iSeries and z/OS and OS/390. IBM DataPropagator™ provides data replication capabilities across the DB2 family. IBM's Websphere Information Integrator feature is a sophisticated multi-database product that permits DataPropagator and DB2 UDB brand members to transparently access heterogeneous database servers such as Oracle, Sybase, and SQL Server. IBM's DB2 Warehouse Manager automates the tasks needed to build and maintain a data warehouse environment. IBM DB2 OLAP Server™, DB2 Web Query Tool, and QMF™ for Windows can also be used with any of the DB2 UDB products to build business intelligence solutions.


Conclusion

While some differences exist, the DB2 UDB product line provides a common technology base and tools for building applications. Over time, IBM will make it easier for both customers and software vendors to leverage their skills and application investments across the DB2 UDB product family. Furthermore, demanding e-business and business intelligence solutions will continue to benefit from the fact that DB2 Universal Database, DB2 Universal Database for OS/390 and z/OS, and DB2 Universal Database for iSeries are optimized to fully exploit their native platforms.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

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


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=14050
ArticleTitle=DB2 UDB Family On Common Ground
publish-date=04012002