Which DB2 9.5 client connectivity option is right for you?
In the past few releases of IBM DB2 for Linux®, UNIX®, and Windows® (DB2), there's been quite a churn of DB2 clients and drivers that allow you to connect applications to DB2 data servers. From full size deployments, to lightweight runtimes, to minimal runtimes that have no tools whatsoever and simply require a connection string, our clients' range of preferences is as broad as the options available.
Over the past few releases, the evolution of the DB2 client connectivity options have tried to match what gets delivered "on disk" to our clients' preferences. In this article, I want to bring you up to speed on the DB2 clients and drivers available as of the DB2 9.5 release, as well as provide a history of changes that affect client connectivity so you don't get confused on your way to DB2 9.5. While I realize that some of you may not be at the DB2 9.5 release, this article serves as a good starting point for understanding what connectivity option you should deploy in the future and the direction of the connectivity software.
One final introductory note: you should also be aware that as of DB2 9, the server component of the DB2 product set only receives (for the most part) new functionality with the full version or significant upgrades (for example, DB2 9.5). In contrast, the client connectivity portion of DB2 (clients or drivers) can be continually updated and is likely to receive new functionality through maintenance (fix packs) as well as new versions and point releases. The development laboratories work very hard to ensure backward compatibility. This allows you to gain the benefits of newer client-side functionality in a more dynamic nature. For example, Visual Studio 2008 support is part of the IBM Database Add-ins for Visual Studio as of DB2 9.5 Fix Pack 1. We were able to deliver this timely support for Visual Studio 2008 because of this new client delivery architecture.
A brief history of DB2 client connectivity
Client connectivity for DB2 data servers has undergone an evolution of sorts over the last couple of releases. While the name and feature churn can be somewhat confusing, the end result is a range of more streamlined, sophisticated, and flexible connectivity options for your data servers. I've written this article to help you better understand your options as of DB2 9.5.
What's in a name?
As previously mentioned, with respect to names and functionality, the client connectivity options have undergone quite a few changes in the previous releases of DB2.
In DB2 8, the smallest client available was called the DB2 Run-Time Client. In DB2 9, this client was renamed the DB2 Runtime Client (note the descriptive part of this client's name moves from Run-Time to Runtime). In DB2 9.5, this client gets renamed again and is now called the IBM Data Server Runtime Client (I'll explain why later in this article). In this article, if I refer to the DB2 Run-Time client, I am referring to the DB2 8 version of this client code. If I refer to the DB2 Runtime Client, I am referring to the DB2 9 version of this code, and it stands to reason, it I refer to IBM Data Server Runtime Client I am referring to the DB2 9.5 version of this code. I'll follow this naming framework throughout this article for all the different client connectivity components.
As another example, if I talk about the "IBM DB2 Driver for JDBC and SQLJ," I'm talking about what shipped with DB2 9. If I talk about the "IBM Data Server Driver for JDBC and SQLJ," I'm talking about that same driver (though it's been enhanced) at the DB2 9.5 level.
To make things easier to follow, and if you ever get a little stuck keeping track of all these changes, you can use the following table which summarizes the naming evolution of the DB2 client connectivity options over the last few releases:
Figure 1. The evolution of the DB2 client connectivity options in the last couple of DB2 releases
Life as a client in DB2 8
In DB2 Universal Database Version 8 (DB2 8) there were three installable clients; namely, the DB2 Administration Server Client, the DB2 Application Development Client, and the DB2 Run-Time Client. I won't get into the exact components of each of these clients, but it's sufficient to say that their names accurately represent their function and intended use.
In addition, for lightweight Java deployments, the Java Common Client (JCC) was an approximately 2 MB .JAR file that you could deploy for applications requiring SQL Java (SQLJ)- or Java Database Connectivity (JDBC)-based access to DB2 data servers. The JCC didn't require installation or maintenance. You simply included it as part of your application, or referenced it within your system’s configuration — from there, you just specify a connection string in your code, point to the driver, and you're there. When you use a thin driver such as the JCC, you should be aware that it is for connectivity only. For example, there is no DB2 command line processor (DB2 CLP) or tools. Finally, this driver includes a royalty free redistribution license (like all the other lightweight drivers) allowing you to embed IBM data server connectivity within your applications at no cost.
While the DB2 8 connectivity options met the functional needs of our clients, if you weren't running a Java program, you had to install a DB2 Run-Time Client. At this time, the DB2 8 Run-Time Client had an arguably larg footprint in consideration of its intended use. This potentially created challenging deployment issues for mobile users. For example, consider a mobile employee with a slow Internet connection trying to download hundreds of megabytes to simply connect an application to a target DB2 data server. This doesn't even account for the size of the application. In short, the DB2 Run-Time Client wasn't conducive for a nimble deployment environment where connectivity was the sole requirement and your application wasn't Java-based.
The DB2 8.2 release introduced the DB2 Run-Time Client Lite for Windows-based deployments. The DB2 Run-Time Client Lite had a Windows-based installation program; in addition, a set of Windows Installer Merge Modules were made avialable so you could directly embed this client in your application’s Windows Installer-based installation program. However, there was no migration support for this client and it couldn't coexist with other DB2 clients.
As its name implies, the DB2 Run-Time Client Lite had a smaller footprint than the DB2 Run-Time Client. This footprint reduction was made possible since this client came without any tools. Rather, it just came with the files required for data access through supported APIs such as .NET, JDBC, SQLJ, ODBC, CLI, and OLE DB.
DB2 8.2 also introduced the db2iprune command. You can use this command to prune a Windows-based installation image such that the image doesn't include certain features. The need for this utility came about because even though you could customize a DB2 client installation, you still had to have access to a full installation image in case those preferences changed. For example, let's assume you were deploying a custom installation of a DB2 Application Development client. Furthermore, let's assume that you customized your installation such that you didn't want to install some graphical tools but wanted some others. For example, in DB2 8.2, you may have wanted the DB2 Development Center, but not the DB2 Client Configuration Assistant (DB2 CCA). Using a customized installation, you could specify to not install the DB2 CCA. Typically, for broad scale deployments, you would provide a response file to your software distribution software to remotely install the DB2 Application Development Client on a set of target workstations. These target workstations would either get the installation through a PUSH (your system software forces the installation on target workstations) or PULL (clients connect to a server and ask for updates) methodology. Either way, you had to mount the entire installation image, which would include the DB2 CCA even if the response file you provided the accompanying installation wasn't going to install this component. This was done to safeguard against broken installations where the user preferences might change. You can see how this might create some distribution issues, as you would needlessly have to mount an image that may be significantly larger than its intended use. Quite simply, when performing an installation, the installation image has to be a superset of all the possible features that could be installed by any user or any response file.
Using db2iprune, you now had the ability to trim an installation image to match a given response file such that the image would only include those specified components that you intended to install. Even if you were to interactively run an installation using the DB2 graphical installation program and select a Custom installation, the components that were pruned out of the installation image would not appear. db2iprune is a great tool to help trim the size of any DB2 installation image (It isn't just limited to a DB2 client. However, it is limited to Windows as of DB2 9.5.). There are still some space-consuming components that you can't eliminate within a DB2 client installation image using db2iprune. This topic will be discussed in a future article.
DB2 8 also supported a special installation of the DB2 client software called the DB2 Thin-Client. While generally not considered a mainstream client, for some environments it may just suit your needs. This client is covered at the end of this article in the So you heard about this thing called a Thin-Client? section.
Life as a client in DB2 9
DB2 9 simplified the client spectrum and added a lightweight driver for Open Database Connectivity (ODBC) and Call Level Interface (CLI) applications. Specifically, the DB2 8.2 DB2 Administration Server Client and DB2 Application Development Client merged to simply become a DB2 Client. As of DB2 9, a client is used anytime you need application development features, tooling, and pretty much anything not associated with simple run-time connectivity. The DB2 Runtime Client retains its name, but some of its components have been stripped out to make it smaller. If you were familiar with the DB2 Run-Time Client Lite in DB2 8.2, you can pretty much use this to properly frame the DB2 Runtime client in DB2 9.
For example, the Configuration Assistant (CA) is a tool that can be used to graphically configure client connections to local and remote data servers, configure CLI settings, test data server connections using any supported API, and more. The CA used to be part of the DB2 Run-Time Client in DB2 8 (it was called the Client Configuration Assistant back then), but was removed in DB2 8.2. This was the first step in trying to ensure the name of the client represented its functionality. To me, this makes sense since the name runtime implies all you're getting is a simple runtime environment. Quite simply, the client structure in DB2 9 kind of resembles a Java environment from an options perspective. If you simply want to run a Java program, you minimally need a Java Runtime Environment (JRE). A JRE is typically very small and installed with your Java application. If you want to develop a Java application, you’re going to need a Java Developer’s Kit (JDK) to get full access to the rich features required for application development.
The JCC driver available in DB2 8.2 was renamed to IBM DB2 Driver for JDBC and SQLJ in DB2 9. It's a longer name, but one that more accurately represents the capabilities of this driver. More importantly, a new thin-driver, called the IBM DB2 Driver for ODBC and CLI, was introduced. This extended the lightweight driver deployment value proposition, once solely reserved for Java-based applications, to those applications that used ODBC or CLI interfaces to connect to a remote DB2 data server.
In DB2 9, the only way to get the IBM Database Add-ins for Visual Studio 2005 is through a Windows-based DB2 Client or a server image.
Life as a client in DB2 9.5
DB2 9.5 brought yet another evolution for client connectivity. You'll notice the prefix DB2 has been removed in all the client and driver names and replaced with IBM Data Server. The reason behind this (and this is the exciting part) is that as of DB2 9.5, there is one set of code for the entire IBM data server family (specifically, DB2 for i5/OS, DB2 for z/OS, DB2 for Linux, UNIX, and Windows, and Informix Dynamic Server [IDS]). This is a terrific innovation, as it considerably lightens the deployment burden for the customers that leverage multiple members of the IBM data server family. What’s more, integrated development environment (IDE) plug-ins are also common, and you can program to a single set of data access APIs and connect applications to any member of the IBM data server family (you still need a DB2 Connect license to get to data that resides on a DB2 for i5/OS or DB2 for z/OS data server).
Beyond savings in deployment costs, the new DB2 9.5 streamlined client infrastructure drives down the total cost of ownership of your IBM solution because of the common IDE integration and drivers. For example, let's assume you were in charge of a group of Java developers and your company was running DB2 for z/OS, Informix for AIX, and DB2 for Windows. As of DB2 9.5, you can simply distribute the IBM Data Server Driver for JDBC and SQLJ on each of your workstations and all of them will be instantly enabled to connect to any of them. In addition, you could leverage the IBM Data Studio to build business logic and even use mouse clicks to build Web services across any of the previously mentioned IBM data servers. This is all due to a set of common drivers, toolset, a common SQL API (for DB2 data servers), common Web Services framework, common connectivity code, and more. There are many more examples I could give, but that’s outside the scope of this article. Suffice to say, the new client architecture in DB2 9.5 allows IT programming and deployment staff to move from one position to another, giving CIOs the benefit of a more fluid workforce. When you consider that some studies estimate that 70 percent of IT budgets are focused on people costs, compared to 30 percent going to hardware and software, you can see there's a tremendous amount of labor capital involved with all the piece parts of any solution. The new client infrastructure in DB2 9.5 is a key component to lowering the personnel costs associated with an IBM data server solution.
DB2 9.5 also introduces a new thin-driver called the IBM Data Server Driver for ODBC, CLI, and .NET. It’s only available on Windows and was designed for Windows clients. It allows a lighter weight deployment for enabling Windows-based clients to connect .NET applications to DB2 data servers. In DB2 9, the IBM Data Server Driver for ODBC and CLI didn't support .NET applications. Therefore, if you were writing a .NET application, you had to deploy the significantly larger DB2 Runtime Client. The new IBM Data Server Driver for ODBC, CLI, and .NET allows you to easily deploy connectivity for .NET applications with a minimal footprint. Simply put, consider it an expansion to the previously (and still) available IBM DB2 Driver for ODBC and CLI in DB2 9 (this driver is renamed in DB2 9.5). If you’re connecting a Linux- or UNIX-based client, and want a thin driver, you would use the IBM Data Server Driver for ODBC and CLI since you obviously aren’t running .NET applications on these workstations.
Choose the right client connectivity option in DB2 9.5
As previously mentioned, DB2 9.5 comes with a number of DB2 client and driver connectivity options. Depending on what you’re trying to do, one option may be better than another. Figure 2 provides you with my personal decision matrix for deciding what DB2 client or driver to deploy for the connectivity requirement at-hand in consideration of the most common factors customers talk to me about:
Figure 2. Tips for selecting a DB2 9.5 client connectivity option
The IBM Data Server Client
The IBM Data Server Client is the full installation client available with IBM data servers. In DB2 9.5, it has full support for all supported APIs. For example, it includes drivers for Ruby. In DB2 9, you had to add Ruby support through post-installation downloads and configuration steps. This client also includes the full gambit of graphical tools. For example, if you install an IBM Data Server Client, you also get the Configuration Assistant, the IBM Database Add-ins for Visual Studio, language support, and more.
If you are planning to support OLE DB applications with this client to a backend IDS data server, you should be aware that this interface isn't supported for this purpose. However, you can use the ODBC support included in this client to compensate for some of these scenarios.
Quite simply, if it's available in a DB2 client, it's here. A DB2 Client is a superset of all things connectivity-related for DB2 connectivity. As the previous figure illustrates, if you need any tooling support, you pretty much need to install this client.
The IBM Data Server Client is about 330 MB. You can further customize this client and get it down to about 200 MB using the db2iprune utility mentioned earlier. In a future article, I'll show you how to make it even smaller!
The IBM Data Server Runtime Client
The IBM Data Server Runtime Client is the choice for lightweight client deployments. It's smaller than its DB2 9 counterpart and now includes support for IDS in the same manner as the IBM Data Server Client. There are no tools provided with this client — as previously mentioned, this is a change from its predecessor in DB2 8. (The CCA was removed with the DB2 8.2 Run-Time client. It was never part of the DB2 9 Runtime Client.). If you want to use the Configuration Assistant to graphically configure connections in DB2 9.5, you’re not going to find it here. However, this client does include the DB2 CLP. This client includes all the interfaces for DB2, for example, a Ruby driver, a .NET driver, and so on. Just like the IBM Data Server Client, this client doesn't support an OLE DB connection to an IDS data server. The IBM Data Server Runtime client is about 60-70 MB on disk after installation.
This client may not be appropriate if you have stringent footprint requirements because of its obvious size, but it does offer full support for any application connectivity scenario. If you are supporting applications that are built with a myriad of different programming languages, then this is likely the best client for you if you don't need any tools. If you are supporting open source applications like Ruby on Rails and you don't need tools, this is your only option, as the drivers don’'t include the pre-compiled binaries for open source APIs like Ruby, PHP, and so on.
The IBM Data Server Driver for JDBC and SQLJ
This driver is pretty much the same driver from DB2 9 with some technical enhancements and fixes (you also use this driver in a pureQuery environment). Like all the IBM data server connectivity options, this driver has support for the IDS data server too. It's delivered in a single package, called db2jcc.jar (you'll need a valid DB2 Connect license if you want to connect it to a DB2 for i5/OS or DB2 for z/OS data server) and was delivered at the JDBC 3.0 specification. DB2 9.5 also supports the JDBC 4.0 specification with a single package called db2jcc4.jar.
If you need a lightweight royalty-free redistributable driver for Java-based applications, and don't need any tools, this is your panacea since it supports advanced features like connection concentration, automatic client reroute, and more. With an approximate size of 2 MB, I refer to the space this driver consumes as a fingerprint. Just how ubiquitous is this driver? Billions of dollars per minute depend on this driver (or a previous version of it).
The IBM Data Server Driver for ODBC, CLI, and .NET (for Windows only)
This driver is delivered in the same manner as it was in DB2 9 and includes support for ODBC and CLI interfaces, in addition to the newly added support for .NET. This is a great deployment option for .NET applications because you no longer require a large footprint for the sole purpose of providing connectivity to .NET applications. In addition, binary versions (so you don't have to compile them yourself) for interfaces such as Ruby, Perl, and PHP have been added (despite this driver's name), which cuts the time for deployment curve (as well as deployment efforts) for these applications as well.
The IBM Data Server Driver for ODBC, CLI, and .NET also has some nice Windows enhancements too. For example, an installation wrapper is shipped that can greatly enhance its deployability characteristics on Windows.
This driver also provides a set of merge modules, like the DB2 Runtime Client. I recommend using these merge modules for installation because it more naturally fits into whomever is developing your application's installation paradigm. If you don't need the CLP, or any tools, I recommend using this driver because this driver doesn't have the burden of any instance management.
It should be quite obvious by now when you would use this driver. However, one important thing to note is that support for OLE DB is not included in this driver. I thought I'd make specific mention of this because typically ODBC and OLE DB support go hand-in-hand. If you need to support an OLE DB application, you will minimally have to install a DB2 Run-Time Client.
Note: Despite its name, from an IDS perspective, this driver only delivers support for the .NET, PHP, Perl, and Ruby programming interfaces (it's recommended that clients continue to use the Informix SDK for C/C++ applications). This drive comes with a royalty free distribution license.
The new .NET deployment option in DB2 9.5 is great beyond providing a runtime environment for your applications. If you want to deploy the lightest weight .NET development environment possible, you can download the standalone IBM Database Add-ins for Visual Studio (it's about 30 MB) and this driver (it's about 10 MB) and you've got a Visual Studio integrated IBM data server development environment for .NET. Keep in mind, if you follow this approach, that the IBM Add-ins for Visual Studio and the IBM Data Server Driver for ODBC, CLI, and .NET must be at the same code level. For example, if you plan to support the Visual Studio 2008 IDE, you must install these components at the Fix Pack 1 level. If Fix Pack 2 were to bring other functional changes to either of these components, then you would have to ensure that both components were at Fix Pack 2.
The IBM Data Server Driver for ODBC and CLI
This driver essentially provides the same function, feature, and benefits as the IBM Data Server Driver for ODBC, CLI, and .NET, except that it obviously doesn't support .NET or have the Windows-centric deployment enhancements. Use it if you need to support the same scenarios outlined in the previous section, only realize you can't use it for .NET applications. This driver was available in DB2 9 and it also comes with a royalty free distribution license.
The IBM Data Server Add-ins for Visual Studio
The IBM Data server Add-ins for Visual Studio used to only be shipped for general availability (in other words, I'm excluding technical previews here) as part of a Windows-based DB2 Client or a server image. In DB2 9, this add-in used to be called the IBM Database Add-ins for Visual Studio 2005. In DB2 9.5, the name has changed to reflect the fact that the same add-in can be used for Visual Studio 2005 or Visual Studio 2008, so the version information has been removed from this Add-in's name.
In DB2 9.5, while a DB2 Client and server image still ship with this add-in, it is now available as a separately downloadable image, which is about 30 MB.
So you heard about this thing called a Thin-Client?
In a DB2 for Windows environment, you can install an IBM Data Server Client or DB2 Connect PE on workstations and have these workstations act as code servers to DB2 Thin-Client or DB2 Thin-Connect workstations in your enterprise.
Note: I use the term "Thin-Client" to represent a DB2 Thin-Client or a DB2 Thin-Connect workstation when discussing this architecture. I've found that the term thin gets overloaded these days because many use it to refer to the drivers that I've discussed in this article. It shouldn't be in this manner, and I specifically use the word driver when talking about drivers and Thin-Client when talking about this architecture.
Thin workstations load client connectivity code (including DB2 Connect PE) across a network connection from their respective code servers. A thin workstation functions like any other client. The architecture is transparent to the user. The main difference is that the code is installed on a code server, and not on the individual workstations. In this architecture, no processing in done at the code server. The client connectivity code is simply loaded from a code server. Each thin workstation needs only a minimal amount of code and configuration to establish links to a code server. This is in sharp contrast to a locally installed IBM Data Server Client or an IBM Data Server Runtime Client architecture, which is sometimes referred to as a Fat-client architecture.
To install a Thin-Client on a DB2 for Windows workstation you have to use the IBM Data Server Client (obviously, you would use the DB2 Connect PE image for a Thin-Connect configuration). After some initial configuration, this machine is known as a DB2 Thin-Client code server.
Figure 3 shows a typical DB2 Thin-Client and DB2 Thin-Connect environment. The red filled arrows represent the code that is being loaded on the DB2 Thin-Client from a respective code server. The yellow filled arrows represent the connection to the database (or optionally a DB2 Connect gateway that in turn makes the connection to a target database). Once the code is loaded, all processing and activity is locally handled on the DB2 Thin-Client or DB2 Thin-Connect workstation.
Figure 3. A Thin-Client configuration
It’s outside the scope of this article to go into details about this sort of information. However, "Installing DB2 Products Across Your Enterprise" (developerWorks, Feb 2002) discusses this option. Of course the client names have changed from the time that article was written. If you use this type of environment, you should realize that the share has to be available all the time if you want those Thin-Client or Thin-Connect workstations to be able to load the client connectivity code from their respective code servers.
Paul's link list
Never seem to be able to find the right download? These are the most popular links when visiting customer sites:
- For all things application development related.
- For Java-related downloads.
- For all things .NET related.
- PHP or Perl? Find it here.
- IBM Data Studio for free!
- Get the IBM Data Server Client.
- Get the IBM Data Server Runtime Client.
- Get the IBM Data Server Driver for JDBC and SQLJ.
- Get a free copy of DB2 Express-C.
- Need DB2 Connect Developer's Edition for Windows?
- Need DB2 Connect Developer's Edition for Linux?
- Get the DB2 Net Search Extender for free.
- The DB2 Information Center, for local installation.
Ready... set... deploy...
In this article, I tried to give you the evolutionary rundown of DB2 connectivity from the DB2 8 release, to DB2 9, and up until DB2 9.5. You should now have a firm understanding as to what client or driver to deploy, enlight of the most common considerations I hear from our customers: toolset support, API support, and footprint.
If you’re in a position where you want to install an IBM Data Server client, and you already used db2iprune to reduce this client’s size from approximately 330 MB to 200 MB, but you still need to get this image smaller, stay tuned. In my next installation article, I'm teaming up with installation guru Frank Ning and we'll show you how to make this client image even smaller for English-only installations — about 160 MB. Why would you want to do that? Perhaps you need to deploy graphical tools with your client deployments, yet want to minimize footprint. As previously noted, the IBM Data Server Client is the only client that includes any graphical tools.
- "Installing DB2 Products Across Your Enterprise" (developerWorks, Feb 2002): Find the advantages and disadvantages of various methods of installing DB2 products in a distributed environment. Interactive, response file, Citrix or Windows Terminal Server, and code server or thin-client installations are discussed.
- "Compare the distributed DB2 9.5 data servers" (developerWorks, March 2007): In a side-by-side comparison table, author Paul Zikopoulos makes it simple to understand the basic licensing rules, functions, and feature differences between the members of the distributed DB2 9.5 data server family.
- "Which distributed edition of DB2 9.5 is right for you?" (developerWorks, March 2007): Learn the details on what makes each edition of DB2 9.5 unique. The author lays out the specifications for each edition, outlines licensing considerations, historical changes from DB2 9, and describes some interesting things customers are doing with each edition of DB2.
- "DB2 and IBM's Value Unit pricing" (developerWorks, Nov 2006): This article describes how to license DB2 using the new Value Unit metric as well as a number of helpful everyday scenarios.
- "Licensing distributed DB2 Universal Database (DB2) Version 8 servers in a high availability environment" (developerWorks, Jan 2003): Are you trying to ensure you're licensing your DB2 servers correctly in a high availability environment? Don't have the time nor the will to read through the announcement letters, PLETs, or your licensing sheets? Author Paul Zikopoulos explains it all in plain English.
- "Licensing distributed DB2 9.5 data servers in a high availability environment" (developerWorks, Dec 2006): Are you trying to ensure you're licensing your DB2 9.5 data servers correctly in a high availability environment? Don't have the time nor the will to read through the announcement letters, PLETs, or your licensing sheets? Author Paul Zikopoulos explains it all in plain English and covers some important changes in DB2 9.5 too!
- Download a free trial version of DB2 Enterprise 9.5.
- 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 Edition and provides a solid base to build and deploy applications.
- Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.