DB2 Connect® Usage and Editions
27 March 2007
Updated 23 May 2012
The primary use of DB2 Connect is to allow DB2® clients (PC’s, Application Servers, and DB2/LUW Database Servers) to connect to DB2 on zSeries and iSeries servers. It translates character data between EBCDIC encoding used on zSeries or iSeries and ASCII encoding used on Linux, UNIX or Windows. This sounds simple enough, but in reality DB2 Connect provides your applications, whether written in Java, .NET, ODBC, Ruby, PHP, CLI, etc., to be tightly integrated with the powerful zSeries and iSeries servers! It can even take advantage of Workload Manager and Sysplex implementations. In addition, it allows you to place your data on the platform that makes the most sense to your organization while making the physical location invisible to your applications. There are various editions of DB2 Connect that all do the same thing, but the terms of how you are allowed to use them differ. If you use Type-4 JDBC drivers you can connect to DB2 on zSeries and iSeries servers, but you still have to license DB2 Connect. I’ll explain the licensing terms (Editions) later in this document.
The four major parts of this document are:
First, I’ll define a few abbreviations that I will use throughout this article:
LUW – Linux, UNIX and Windows
DB2/LUW – DB2 on Linux, UNIX and Windows
DB2/z – DB2 on a System Z Server (a.k.a. zSeries)
DB2/i – DB2 on an IBM I Server (a.k.a System I a.k.a. iSeries, a.k.a. AS400)
The DB2 Connect Server is a robust product that can be used in several different ways. I’ll illustrate those different ways here. I’ll show DB2/z in these examples, but if you are using iSeries, then it works the same way. These are basic examples that demonstrate how to connect a DB2 client to a DB2/z or DB2/i database. They do not provide all of the information that you need to perform a robust configuration of your DB2 Connect system. To learn how to do that you should consult the DB2 Connect User’s Guide. I have also created another document called zSeries DB2 Connect Preparation that describes how to gather the catalog information from your zSeries host for the catalog commands. All of the Editions of DB2 Connect provide a DB2 Connect Server except Personal Edition.
I’ll start with an example of a simple DB2/LUW client connecting to a DB2/LUW database server to illustrate the simplest type of connection in the DB2 distributed world. DB2 Connect is not needed or shown in this example.
In this example the application servers “AppServA” and “AppServB” run applications that use the DB2 Client to talk to the database on server “dbServ1” that has a database called “LUWdb1” in instance “db2inst1” that is listening on port 50,000. I have shown the catalog commands that would be used to create a database alias called “LUWdb1” on the application servers. The catalog entry “LUWdb1” is then just referenced by the application to connect to the database rather than having to provide all of the information about where the database is.
In example 2 the application servers each have DB2 Connect installed and they each connect to the DB2 database on zSeries independently:
In this example the application servers “AppServA” and “AppServB” run applications that use the DB2 Connect to talk to the z/OS database server “dbLPAR1” that has a database called “Zdb1” that is listening on port 500. It should be noted that the DB2 Client is installed when DB2 connect is installed, so the same APIs would be used by the application in Example2 as were used in Example 1. I have shown the catalog commands that would be used to create a database alias called “Zdb1” on the application servers. Note the additional “CATALOG DCS” command that must be executed for DB2 Connect connections to DB2/z. The catalog entry “Zdb1” is then just referenced by the application to connect to the database rather than having to provide all of the information about where the database is.
In example 3 only the gateway server has DB2 Connect installed. The application servers all use it to connect to DB2/z:
In this example the application servers “AppServA” and “AppServB” run applications that use the DB2 client to connect to the database on z/OS through DB2 Connect installed on the gateway server, “gwServ1”. The DB2 Connect instance db2inst1 on “gwServ1” is set up to connect to the z/OS database server “dbLPAR1” that has a database called “Zdb1” that is listening on port 500. In this example, the applications servers think that they are just talking to a DB2/LUW in instance db2inst1 database just as the application servers in Example 1 do. Using a gateway allows you to manage your connections to DB2/z from a single location. You can create a cluster of gateway servers for high availability if needed. I have shown the catalog commands that would be used to create a database alias called “Zdb1” on the application servers as well as on the gateway server. Note the additional “CATALOG DCS” command that must be executed for DB2 Connect connections to DB2/z is only needed on the gateway server. The catalog entry “Zdb1” is then just referenced by the application to connect to the database rather than having to provide all of the information about where the database is.
In example 4 the LUW database server has DB2 Connect installed along with the DB2 database software.
In this example the application servers “AppServA” and “AppServB” run applications that use the DB2 client to connect to both the DB2/LUW database “LUWdb2” as well as the z/OS database “Zdb1”. In this case both the LUWdb1 database and the Zdb1 catalog entry are in the same instance, db2inst1, but they could have been created in different instances if that had been desired. In this diagram, DB2 connect on dbServ1 is used by application servers to talk to the z/OS database server “dbLPAR1” that has a database called “Zdb1” that is listening on port 500. Here the application servers think that both of the databases are actually on dbServ1, when in reality one of them is on z/OS. In this example, dbServ1 is both a database server and a DB2 Connect gateway server. I have shown the catalog commands that would be used to create a database alias called “Zdb1” on the application servers as well as on the gateway server. Note the additional “CATALOG DCS” command that must be executed for DB2 Connect connections to DB2/z is only needed on the gateway/database server.
In example 5 the database server has DB2 Connect installed along with the DB2 database software. Further the application accesses the DB2/z tables while only connecting to the DB2/LUW database.
Example 5 is very similar to example 4, except that the application servers do not directly reference Zdb1. When you purchase DB2 Connect, you get a feature called DB2 Homogenous Replication which means that you can “federate” your DB2 and Informix databases on all platforms. In this case you would create nicknames in the LUWdb2 database that reference tables in the Zdb1 database. To your application nicknames are like views in that they are indistinguishable from tables. You use the “CREATE NICKNAME” command to create nicknames in the LUWdb1 database1 on the Zdb1 tables.
In this example the application servers “AppServA” and “AppServB” run applications that use the DB2 client to connect to just the DB2/LUW database LUWdb2. After connecting the application sees a bunch of objects that all look like tables, but some are local tables, some are local views and some are local nicknames that really point to tables and views on the DB2/z database called Zdb1. Federation is a very large topic and you can read more about in Redbook: Data Federation with IBM DB2 Information Integrator V8.1 Please note that this Redbook was written before the product was renamed WebSphere Federation Server.
In example 6 the database server has had only the DB2 database software and the gateway server has only DB2 connect Installed.
In this example there is one database server and one DB2 Connect gateway server. Both application servers have only the DB2 client software installed. The application on AppServA needs to get data from LUWdb2 as well as Zdb1. As in previous examples, the data in Zdb1 only resides on the z/OS database server. For some reason we only want DB2 Connect installed on gwServ1. To allow AppServA query Zdb1, we catalog the Zdb1 database on AppServA and on gwServ1 to allow the connection to happen through a chain of servers. Depending on network speeds and server speeds, this may or may not be a good idea in reality. This can be done through any number of DB2/LUW servers and can end on a DB2/LUW database rather than a DB2/z database if desired.
AppServB also needs to get data from both LUWdb1 and Zdb1. This illustration shows how that can be accomplished by chaining database and gateway servers together. It would probably be a better idea to catalog dbServ1 directly on the application server, but the point of this example is to show what can be done. This stuff is like tinker toys – the ways that you can connect the nodes is only limited by your imagination.
Federation was not used in this example, but you could federate the database here if desired. If that was done, it may eliminate the need to catalog Zdb1 on AppServ1 because then AppServ1 could just access the Zdb1 tables as nicknames in LUWdb1.
Each combination of Operating System (OS), DB2 Version and DB2 fix pack on the DB2 client and DB2 Connect server that connect to the zSeries or iSeries server must be bound to the database. That just means that for each combination you must run the “BIND” command using certain input files from the client while connected to the database. To illustrate, assume that you had the 4 types clients in the table below that you wanted to connect to a database on DB2/z or DB2/i. They would have slightly different contents in the local bind files and would each have to run the bind commands that I will show later.
Fix pack 12
Fix pack 12
Fix pack 13
Fix pack 13
Even if you have 100 clients, but they all ran on one of the above 4 configurations then you would only have to run the binds 4 times – once from each software stack. Running the binds puts objects in the database that allow the clients with various software combinations to be recognized by that database. If you have multiple databases on your DB2/z system then you would have to run the 4 binds while connected to each database. This topic is covered more completely in Chapter 6 in the DB2 Connect User’s Guide, but I will provide a summary of what needs to be done here because this step is often forgotten and the errors that you get when this is not done do not really indicate what is actually wrong.
The files that you will bind to the database are found in the “sqllib/bnd” directory under the client instance home directory. So when you are on your client or DB2 Connect Gateway Server and are in the “bnd” directory then you will run one of the following sets of commands depending on which platform you are binding to:
DB2 on z/OS
db2 “BIND @db2cli.lst BLOCKING ALL GRANT PUBLIC"
db2 “BIND @db2ubind.lst BLOCKING ALL GRANT PUBLIC"
db2 “BIND @ddcsmvs.lst blocking all sqlerror continue messages ddcsmvs.msg grant public
DB2 on System I
db2 “BIND @db2cli.lst BLOCKING ALL GRANT PUBLIC"
db2 “BIND @db2ubind.lst BLOCKING ALL GRANT PUBLIC"
db2 “BIND @ddcs400.lst blocking all sqlerror continue messages ddcsmvs.msg grant public
DB2 on VSE
db2 “BIND @db2cli.lst BLOCKING ALL GRANT PUBLIC"
db2 “BIND @db2ubind.lst BLOCKING ALL GRANT PUBLIC"
db2 “BIND @ddcsvse.lst blocking all sqlerror continue messages ddcsmvs.msg grant public
As I said in the beginning of this document, all of the editions of DB2 Connect perform the same function. That function is described in my long diatribe above. The different editions are just different terms under which you can license the software from IBM. The one caveat is that DB2 Connect Personal Edition does not provide a DB2 Connect Server. Here I will attempt to summarize the different license terms that correspond to each edition. Please keep in mind that this is Dean Compher’s interpretation of these terms and may not represent the official IBM terms and conditions. They are my best attempt to describe them in easily understood language, but may not be 100% accurate so don’t yell at your friendly sales rep if I made any mistakes. You can tell that I’ve been working for large corporations for way too long because I feel compelled to put disclaimers in everything that I write! Anyway if you want to see for yourself the exact terms and conditions of each edition please click one of these links: DB2 Connect 9.7 Advanced Editions Announcement Letter (USA) or DB2 Connect 9.7 Announcement Letter for Other Editions (USA).
To reduce confusion about how DB2 connect is licensed, you really need to keep in mind that IBM is attempting to license DB2 connect based on how you will use it and that has very little to do with where it is installed as you can see by the above examples. If you keep in mind that we are trying to charge you based on a measurable way to use the product, then the editions will make more sense. If you don’t keep this in mind then they are just plain confusing. These definitions are for DB2 Connect v9.7 only. The v8.x editions used similar names, but in some cases the license terms for the names have changed significantly. If you purchased and began using DB2 Connect v8.x prior to the data of the above announcement letter then you may continue to use the license for the original purpose under the original terms. However, even if you are using DB2 Connect v8.x license purchased after the announcement letter date of DB2 9 then you are bound by the DB2 v9 terms.
Some of the editions are based on Processor Value Units or PVUs. This is a way of licensing software based on the power of the processor running it. In general, single core processors are worth 100 PVUs, but the power of cores in multi-core processors vary greatly. To see the full explanation of a PVU please visit the Processor Value Unit page. The v9.7 editions are:
Enterprise Edition Concurrent Users (called Enterprise Edition Floating Users Single Install as of v10.1)
Like DB2 9.7, the fp 3 and later from your Passport Advantage Account. You can use this tool to develop SQL and Stored procedures on any platform of DB2.now comes free with DB2 Connect v9.7
This edition is licensed based on the processing power of your zSeries or iSeries database servers. For the zSeries server the price is based on the MSU rating of the physical machine(s) or LPARS running DB2. On iSeries the price is based on the Processor Value Unit (number of processor cores) rating of the iSeries Server(s). This edition is a way for IBM to license the product based on the maximum value that you could possibly get from the product based on the database server capability. In this case the limit is imposed by the maximum processing power of the zSeries or iSeries system(s) where DB2 is running, so when you purchase DB2 Connect Unlimited Edition, you can install DB2 Connect on as many application servers or workstations as you like. If you contact IBM and request it, you can also install it on third part servers that connect to your System Z or System I servers. With this edition you are also licensed for Personal Edition which is described below for no extra charge.
For zSeries when purchasing this edition you always purchase one Host License if you are running one server and enough MSU licenses for the server itself or the LPAR where DB2 is running. If you are running DB2 in a single SYSPLEX environment, then you purchase one Host License and enough MSU licenses to cover all of the servers or LPARS in the SYSPLEX running DB2. If you are running multiple independent System Z servers or multiple independent SYSPLEX environments, then you must purchase one Host License for each server or SYSPLEX and enough MSU licenses to cover the MSUs available on the environments where DB2 is actually running.
As of DB2 v9.7 fix pack 3 the deployment of DB2 Connect license files to application servers, work stations and other DB2 Connect installations (except DB2 Connect Gateway Servers) has been greatly simplified. The license can now be enabled on z/OS eliminating the need to place the DB2 Connect license on some machine. See this License Activation link for all of the details.
This edition is licensed exactly as described above in the “Unlimited Edition for iSeries or zSeries” section. The main difference is that Unlimited Advanced Edition packages our IBM Optim pureQuery Runtime tool with DB2 Connect and is only available for System Z. Sorry iSeries users, there is no Unlimited ADVANCED edition for you, but you can purchase pureQuery separately and use it with DB2 Connect Unlimited Edition for iSeries. Many zSeries shops with DB2 have traditionally only run static SQL in the database. Static SQL is SQL that has already been prepared with the access plan to the data already determined and stored in the database. This allows for a much more consistent and often faster access to the data in the database. pureQuery allows you to capture the SQL from a JAVA or CLI application on Linux, UNIX or Windows and then bind it to the database. You do not need the source code to capture the SQL, so it’s great for purchased applications too! Now your applications that connect from clients of DB2/z can have all of the advantages static SQL just like your old COBOL programs have. You can even set a switch in pureQuery that says that it will block any SQL that it has not seen in the capture process, thus preventing SQL Injection hacks. The IBM Optim pureQuery Runtime tool has many other features that are described in this link.
This edition is licensed based on the combined processing power of the LUW APPLICATION servers that are accessing the DB2 databases on your iSeries or zSeries servers – NOT the number of processors where DB2 is installed. To illustrate using Example 6 above, you would determine the total number of processors in AppServ1 and AppServ2 as part of determining how many licenses of the Application Server Edition that you need to purchase. “dbServ1” and “gwServ1” do not run applications so they do not count in determining your processor count. DB2 Connect is a fairly lightweight application and you can support lots of application server processing through a single small DB2 connect gateway. To determine the quantity of licenses for this edition you just determine the total number and type of processors in your LUW application servers accessing the DB2/i or Db2/z databases and convert them to PVU using the chart on the Processor Value Unit page.
This edition is licensed exactly as described above in the “Application Server Edition” section. The main difference is that Application Server Advanced Edition packages our IBM Optim pureQuery Runtime tool with DB2 Connect. Good news iSeries users, you can get this “advanced” edition just like your zSeries brothers and sisters can! Many zSeries shops with DB2 have traditionally only run static SQL in the database. Static SQL is SQL that has already been prepared with the access plan to the data already determined and stored in the database. This allows for a much more consistent and often faster access to the data in the database. pureQuery allows you to capture the SQL from a JAVA or CLI application on Linux, UNIX or Windows and then bind it to the database. You do not need the source code to capture the SQL, so it’s great for purchased applications too! Now your applications that connect from clients of DB2/z or DB2/i can have all of the advantages static SQL just like your old COBOL programs have. You can even set a switch in pureQuery that says that it will block any SQL that it has not seen in the capture process, thus preventing SQL Injection hacks. The IBM Optim pureQuery Runtime tool has many other features that are described in this link.
This edition is based on the number of known individuals who use the databases on iSeries or zSeries either directly or through an application. You must be able to give the names of the individuals who directly or indirectly access the database through DB2 Connect. No other users should use the applications that access DB2/i or DB2/z. Because you must be able to identify the individuals who are using this application, it is generally only applicable to internally used applications. It does not lend itself to internet applications. You purchase licenses in increments of 25 known users. For organizations of any size it takes a lot of work to ensure that you remain in compliance with the terms of the agreement. Anyone considering purchasing this edition should give this fact serious consideration.
The terms of this edition only allow it to be used for two-tier applications. Therefore, it could not be purchased to support any of my above examples because the examples all use application servers and application servers imply 3-tier or larger-tier applications. However, Examples 3-6 would be allowed if they were modified such that “AppServ1” was changed to “EndUserPC1” and “AppServ2” was changed to “EndUserPC2” where “EndUserPCx” represents a desktop or laptop with an application and DB2 Client installed. This edition lends itself to having a standard desktop installation that includes the DB2 Client and catalog entries for connect gateway databases, but where no more than the licensed number of users will be connecting through DB2 Connect at the same time. You also purchase this edition in increments of 25 users. For organizations of any size it takes a lot of work to ensure that you remain in compliance with the terms of the agreement. Anyone considering purchasing this edition should give this fact serious consideration. It is also a license requirement that you only run DB2 Connect as a “Server” and that all connections are made through the server. A DB2 Connect server means that at DB2 Connect instance is up and running and servicing connections. A DB2 Connect Gateway is a DB2 connect server. However, for the terms of the license, the DB2 Connect server may be on the same machine as the application.
This edition is for single users to use on their desktop or laptop and does not accept connections from outside of the machine where it is installed like the other editions do. This edition is typically used for developers and individuals who do ad-hoc queries against their zSeries or iSeries databases.
If you use DB2 Connect or the JDBC driver to connect to DB2
on z/OS or i5/OS then you can access non-DB2 data through DB2 stored procedures.
Anything that you can program in a DB2 stored procedure you can access through
DB2 Connect by calling DB2 stored procedures from your application. For
example, on z/OS you can access VSAM, CICS, IMS, and MQ data by calling stored
procedures that you have written running in DB2/zOS that call the appropriate
functions. This makes it very easy to access these data sources for small
to moderate volume applications. For high-volume applications IBM
provides other tools that may perform better.
In DB2 Connect v9.7 and its related JDBC driver you can also consume industry standard Web Services. On any flavor of DB2 databases there are functions that you can call from stored procedures. Some functions provided allow you to call web services. Conversely, you can use DB2 Connect or your appropriately licensed JDBC driver to easily allow stored procedures on DB2/i and DB2/z to be called from web services through DB2 Connect. You can even use IBM’s free Data Studio tool to create those web services that call your stored procedures.
The connection concentration feature has been available through the JDBC type-4 driver since a later fix pack of DB2 Connect v8.2 for connections to DB2/zOS! Also you no longer need to have a running DB2 Connect instance to get Connection Concentration. That is, you can just call the JDBC Type-4 driver or the DB2 Connect Driver as an API and still concentrate your connections This means that you no longer need to run a DB2 Connect server to concentrate a large number of connections into a few static connections to your DB2/z database server! Now that you can do connection concentration from JDBC Type-4 drivers and CLI applications, IBM recommends that you eliminate DB2 Connect Gateways if you need maximum performance. However, there are still architectural reasons you may want a Connect Gateway. Unfortunately, this feature is not yet available for DB2/i.
Here are a variety of other points.
DB2 Connect v10.1
To learn more about how to install and configure DB2 Connect please see the following documents:
OS Requirements for DB2 Connect 9.7 (DB2 Connect below Clients)
For more general information about DB2 Connect please see the following links: