Versions of DB2 exist for a large array of platforms, of which the mainframe (z/OS) is only one. Of course, it is my favorite one since I’ve been working on mainframe technology now for decades and have worked with DB2 on the mainframe since Version 1.

It used to be easy: DB2 meant IBM’s mainframe SQL database management system based on the relational model. But you can’t just say the term “DB2” any more and expect people to understand what you mean.

Today there are variations of DB2 that run on the iSeries (AS/400), on Linux, Unix, and Windows (LUW) platforms, a free version called DB2 Express-C…. not to mention the mainframe variations that run on z/OS, VM, and VSE.

These products are all collectively referred to by IBM as the DB2 Family. Individually, each DBMS is referred to as DB2, or sometimes DB2 Universal Database Server. There was a period of time when DB2 for LUW was called UDB and DB2 for z/OS was just called DB2. Then IBM tried to rebrand both as DB2 UDB. But that seems to have gone away several versions ago now.

The proper way to refer to any individual offering in the DB2 family is DB2 for (operating system) (for example, DB2 for z/OS or DB2 for Windows).

Different Code Bases

There are four distinct code bases for the products under the DB2 brand. The mainframe has its own code base, as does the iSeries, and VSE/VM. The fourth code base is for Linux, Unix, and Windows (LUW) platforms. Having a separate code base means that each of these DB2 “products” was developed independently from the others. So, for example, the process used by DB2 for z/OS to optimize SQL differs from the process used by DB2 for Linux. Usually, though, the result is similar—an efficient SQL statement.

And IBM is doing quite a good job at trying to make all of the DB2s work similarly, at least from the API perspective. Sure, there are times when a specific version on a specific platform does not support a feature that does exist on another platform/version combination. But things are remarkably consistent given the various and disparate code bases. That said, it is always important to “keep in mind” that there will be differences between the DB2s.

Some of the Differences

It is obvious that the different DB2 products are not “plug and play” commodities simply because they all share the name DB2. There are some important differences among these products in their current releases. The biggest differences are relatively easy to detect and include the following:

  • Differences imposed due to operating system constraints (z/OS versus Linux versus IBM i)
  • Back-level compatibility issues
  • Workstation orientation differences such as GUI interfaces and drag-and-drop menus
  • Subsystem-centric implementation (z/OS) versus database-centric implementation (workstation)

Most of these differences are minor and easy to handle. Indeed, IBM has slowly but surely been making these disparate implementations of DB2 more and more alike with each new release and version. There is broad compatibility among the SQL implementations of the members of the DB2 Family (though not 100 percent). You can even peruse the compatibilities and differences in the IBM DB2 SQL Reference for Cross Platform Development if you’d like.

A misconception “out there” in DB2-land is that the LUW platform drives new features first, but a review of the changes that have been introduced to DB2 over the past several versions and releases does not bear that out. Some features are introduced on the mainframe first; others on the distributed platforms first. And eventually the two converge.

Of the basic differences mentioned earlier, the only one that might not be obvious is the focus of the DBMS implementation. DB2 for LUW is database-centric. This implies that each new database carries its own system catalog with it. Additionally, it is not possible to simply access tables across different databases without distributed access.

On z/OS, DB2 is subsystem-centric. A single system catalog spans databases. Each subsystem has a unique identification, and you can create multiple databases within it. Distributed requests are not required to access databases within the same subsystem (or, indeed, across multiple subsystems in a data-sharing environment).

Another concept that is different at the workstation level is that of a directory. The DB2 for z/OS Directory houses DBMS system-related information regarding DBD structure, skeleton plan and skeleton package tables, RBA log ranges, and utility control data. The information cannot be updated by the user but is managed and controlled by DB2.

At the workstation level, a directory is another matter altogether. For example, the directory structure used by DB2 for LUW controls the overall environment.

  • The System Database Directory identifies the databases that can be accessed from the workstation and contains an entry for each local and remote one. Each database entry contains the database name, alias, entry type, and location.
  • A Volume Database Directory is allocated per disk drive that contains a workstation database. Each entry identifies the location of a specific database on the drive.
  • The Workstation Directory is used to make a connection to a remote database server. It is used in conjunction with the Database Connection Services Directory to make a connection to a remote host server.
  • The Database Connection Services Directory is used by DB2 Connect to make a connection to a remote host server.

Not only is it possible for the user to update these directories, it is required. The workstation directories define the environment of DB2 for LUW. Without the proper information recorded in these directories, DB2 might not function in the desired manner. The information in these directories is somewhat analogous to DB2 for z/OS DSNZPARMs and some of the system catalog tables (distributed tables that control DDF).

Database Structures

Not all the objects available to DB2 for z/OS users are supported at the workstation level. For example, hardware-specific DB2 objects such as table spaces and storage groups are not available for DB2 on other platforms, at least not in the same way that mainframers are used to dealing with them. Partitioning and segmenting as it is done on z/OS is not done on other platforms.

However, DB2 for LUW does provide a feature known as a segmented table. But this is not the same concept as a DB2 for z/OS segmented table space. DB2 for LUW segmented tables are used to span volumes, enabling DB2 to get around file size limitations.

The file structure used for databases differs from platform to platform. For example, DB2 for z/OS uses VSAM Linear Data Sets (LDS). A database deployed on DB2 for LUW uses two files for table data: one for normal data and a second to store long fields. These workstation files are flat files, not VSAM files.

Although tables are basically the same for all of the DB2 environments, not all of the DDL options are provided in all of the environments.

Optimizer Differences

One of the most significant benefits of relational databases is that they provide built-in optimization. The DB2 for z/OS optimizer is well-known to mainframe DB2 users, but how similar are the other DB2 optimizers?

DB2 for LUW uses a newer optimization technology from IBM -- the Starburst optimizer (which arose from IBM’s Almaden research lab). Starburst is a database optimization research project that has been covered quite extensively in the academic press. Although some Starburst technology will find its way to DB2 for z/OS, the mainframe DB2 optimizer will not be completely replaced by Starburst technology. Doing so would not be wise because the DB2 for z/OS optimizer has been finely tuned for its environment over the course of more than three decades.

Another interesting tidbit is that DB2 for iSeries provides an access method for programmers in which they can bypass the relational engine. This is not encouraged, but it is available.

Other Differences

Other differences exist between the different implementations of DB2. Some of these are caused by the different release cycles IBM has created for the differing platforms. The bottom line is that you need to be aware that there are differences between the DB2s on different platforms. Whenever you use a specific implementation of DB2, you need to be aware of the features it supports that other DB2 platforms do not, as well as the features it does not support that other DB2 platforms do support.

Packaging and Naming Issues

The actual name of the DB2 edition can be tricky to master on non-mainframe platforms. On the mainframe you just say “I want DB2,” and that is what you get. Well, almost. You also have to decide whether you want IBM’s utilities or not, too. And you might also want to add in the IBM DB2 Analytics Accelerator (IDAA) if you are running a lot of analytical queries.

But things are more difficult in the LUW world. The following editions are all available for DB2 on Linux, Unix, and Windows as of Version 10.5… but keep in mind that these can change when new versions are released.

DB2 Express-C is IBM’s “free” DBMS offering providing most of the “core” capabilities of DB2 at no charge. So why use an open source DBMS when you can get a free version of DB2?

DB2 Express Server Edition is targeted at entry level users at a low price point. Small shops, partners, and new users can build applications on top of DB2 Express.

DB2 Workgroup Server Edition (WSE) is a multi-user, single host, DBMS at the departmental user. It should be deployed for smaller systems with a limited number of users.

DB2 Enterprise Server Edition (ESE) is the highest level of DB2 database version with intra-partition parallelism support (the database engine can process SQL statement segments in parallel), and inter-partition parallelism support (process a query in parallel across all of the nodes). ESE has Partitioning and Clustering options as additional add-on features. So, this is the enterprise DB2. This is what used to be known as EEE, sort of.

DB2 Advanced Enterprise Server Edition (AESE) sounds like a step up from ESE, and it is, kind of... but not really in terms of key DBMS technology. The advanced means that IBM integrates Optim and InfoSphere technologies into the product.

DB2 Developer Edition is marketed at a lower price point and is for use by developer as they build DB2 applications. It is not for running production applications.

A handy comparison of the editions is available on IBM’s web site at:

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.licensing.doc/doc/r0053238.html

Summary

So you see, saying DB2 is no enough these days.

You have to specify which DB2?

They’re all great, but it can take some time to wrap your arms around all of this…