db2Dean a.k.a. Dean Compher a.k.a “The Dean of DB2” is here to answer your DB2 Questions !
Just for Grins Watch:
Add your insights to the:
None Currently Scheduled
Welcome to db2Dean’s web site. I’m Dean Compher an IBM IT Specialist who, along with my team, helps customers and prospective customers with DB2 on Linux, UNIX and Windows (LUW) technical questions and issues. As this page makes painfully clear, I am a DBA and not a web designer, but I would be happy to get your DB2 questions answered or talk to you about the great features of DB2 or IBM Integration Products. If you are looking at a new database solution or want to compare us to your existing database vendor, please do not hesitate to contact me about getting a presentation or just to ask questions. My e-mail address is dean@db2Dean.com
I am located in Utah and primarily serve DB2/LUW customers in Utah, Nevada, Colorado and Montana, but I can forward requests to my peers in other technology and geographic areas as well. My team also covers Informix and products. There are questions that I get on a regular basis, and I will write articles relating to them here. I hope that you find them useful. I also welcome suggestions for future content. Click here for more information about me.
27 March 2007
Updated 28 March 2014
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.
In all of the following examples, you can use the IBM JDBC driver instead of the “DB2 Client” for your Java applications. To make a direct connection to an iSeries of zSeries server, you just need to add the license JAR file. You can download the IBM Clients and Drivers from this link.
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 the DB2 Client with the DB2 Connect license 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 Client to talk to the z/OS database server “dbLPAR1” that has a database called “Zdb1” that is listening on port 500. You can install the IBM Data Server Client, the IBM Data Server Runtime Client or the DB2 Connect Software. It should be noted that the DB2 Client is installed when DB2 Connect is installed and unless you start the DB2 Connect Server and use it, you will be using just the DB2 Client software portion of the install, so in this example there is really no reason to install DB2 Connect. 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 connections to DB2/z and DB2/i. 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.
The scenario shown here in example 2 is now the IBM preferred way to connect to your DB2/z and Db2/i databases. Some shops may still need to use a DB2 Connect Server as show in the following examples, but most will just want their applications to connect directly as shown in example 2. However, those licensed for DB2 Connect Enterprise Edition Floating Users Single Install may not use this direct connect architecture. In example 2 all you need to make the client work is the DB2 Connect license. Further if your application uses a JDBC Type-4 connection, you could use the IBM JDBC driver and DB2 Connect License JAR file instead of the DB2 Client shown in this example. Using the direct connect from the IBM Client of JDBC driver is more efficient because it avoids a network hop through a connect gateway and does not rely on the DB2 Connect Server instance to be up and running.
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. To configure this example and the following examples, you must also be licensed to use the DB2/LUW 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.
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 this link: DB2 Connect 10.5 Announcement Letter (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 v10.5 only. Older 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 newer announcement letters then you may continue to use the license for the original purpose under the original terms as long as you keep using it on the same old server. However, even if you are using and older version of DB2 Connect, but purchased it after a newer announcement letter then you are bound by the newer 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 v10.5 editions are:
The IBM Data Studio is a free interface and development environment that can be used with DB2 on iSeries and zSeries. It has a number of useful features including the ability to browse tables and other objects in a point-and-click interface and it provides development and debugging tools for stored procedures. It uses its own built-in JDBC driver and license to connect. You can download it here.
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 party 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.
For the DB2 Connect Unlimited editions you can apply the license key to the zSeries or iSeries system once for all DB2 Connect and DB2 Client installations. For You no longer need to apply the license to every client. You just use the “db2connectactivate” command from a distributed system connected to DB2/z. See the Activating the license key for DB2 Connect Unlimited Edition in the information center. To use this feature for z/OS you must be using a client that is at DB2 v9.7 or later and for iSeries you must be on at least v10.5 or later.
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.
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 2-6 would be allowed if they were modified such that “AppServA” was changed to “EndUserPCA” and “AppServB” was changed to “EndUserPCB” where “EndUserPCx” represents a desktop or laptop with an application and DB2 Client installed. Note that a DB2 Connect Server or Gateway is not considered a “tier” so it can be in the mix. For the purposes of this explanation an example of a 3-tier environment would be an end user workstation, tablet or other device (tier 1) connecting to an application server (tier 2) that uses a DB2 database (tier 3) to select, update and perform other DML.
This edition works well for organizations who have a small number of dedicated users using DB2 Connect. It is based on the number of known individuals who use the databases on iSeries or zSeries. 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 and consider using one of the Unlimited editions instead.
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 2-6 would be allowed if they were modified such that “AppServA” was changed to “EndUserPCA” and “AppServB” was changed to “EndUserPCB” where “EndUserPCx” represents a desktop or laptop with an application and DB2 Client installed. Note that a DB2 Connect Server or Gateway is not considered a “tier” so it can be in the mix. For the purposes of this explanation an example of a 3-tier environment would be an end user workstation (tier 1) connecting to an application server (tier 2) that uses a DB2 database (tier 3) to select, update and perform other DML.
This edition is great when you have many users who have an application on their workstation that occasionally needs to connect to DB2/i or DB2/z, but where only a few are connecting at the same time. With it you may only connect through a DB2 Connect gateway and must not connect directly to DB2 on iSeries or zSeries. Settings on the Connect Gateway allow you to automatically limit the number of simultaneous connections. This edition lends itself to having a standard desktop installation that includes the DB2 Client that connects to a DB2 Connect Gateway Server, 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. Again, it is 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.
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. Personal edition is typically used for developers and individuals who do ad-hoc queries against their zSeries or iSeries databases. DB2 Connect PE is going out of support as of 30 April 2015. The withdrawal letter states this and also has information about what licenses will given to existing customers after it goes out of support. The alternative that you should use if you are doing a new install is to install the IBM Data Server Client from the Clients and Drivers page and apply the license from any of these editions. Yes, you just need the same client that you would use to connect to DB2/LUW or Informix. The only other thing that you need is the license file.
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.
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 New Features
DB2 Connect v10.5 New Features
To learn more about how to install and configure DB2 Connect please see the following documents:
OS Requirements for DB2 Connect (DB2 Connect below data server client products section)
For more general information about DB2 Connect please see the following links:
Mary Lou’s Stained Glass