DB2 Clients – Connect to your Database
25 September 2009
Updated 2 January 2013
I just want to connect to my database, so what should I use? I’ve heard of the Data Server Client, Administrative Client, Runtime Client, Data Studio, JDBC Driver, and other drivers. I’ve even heard of something called DB2 Connect. Which one is the best for me? Well, you’ve come to the right place. In this article I will try to sort this all out for you. You can download the latest clients and drivers or IBM Data Studio from these links.
Clients vs. Drivers
In general terms a client includes software that lets you connect to a database and issue queries from a command line interface or built in GUI. Clients also have DB2 built-in utilities such as the bind, export and import commands. In addition the client may have libraries and drivers that allow application programs to connect and issue queries natively using a CLI interface or ODBC and JDBC. Drivers generally only contain libraries and drivers that allow applications to connect to the database.
There are currently three DB2 Clients:
The IBM Data Server Runtime Client is usually used on servers and workstations that primarily have applications that need to connect to a DB2 database and possibly run some utilities, but don’t need any GUI tools to explore or administer the database. It includes many DB2 specific utilities and libraries and the DB2 Command Line Processor (CLP) tool that lets you issue queries and commands to the database. This client also allows your application to make CLI connections to your databases. It contains all of the DB2 libraries and drivers available for various programming languages. It has all of the drivers described in the “IBM Data Server Driver Package” described below.
The IBM Data Server Client has everything in the Runtime Client has and it also has the DB2 Control Center in versions prior to v10.1. Control Center is a GUI tool that allows you to view and manipulate the database objects, run queries, execute database utilities and administer the database. In prior versions this package was called the DB2 Administrative Client. DB2 v9.7 and before also contains the Configuration Assistant which is a GUI tool that allows you configure connections to your database. Starting with DB2 10.1, the Control Center and Command Center are no longer included in any clients or servers. However, some GUI tools are still included such as the Replication Center. The IBM Data Studio is now the GUI tool that should be used for most administration purposes. While you can use the v9.7 Control Center, you can not use new features like the INGEST utility from it. Most SQL queries should work from the v9.7 Command Center, but at some future server version, the v9.7 client will stop working.
IBM Data Studio is the up and coming GUI client. As of the time that I am writing this article, Data Studio has most of the features of Control Center, but lacks a few of the utilities such as Replication Center. On the plus side, Data Studio contains additional features such as a query builder, a stored procedure builder and debugger plus a facility to easily turn stored procedures and queries into web services. It also has a very nice feature that allows you to create a quick entity/relationship diagram of your physical database! Click this link to download Data Studio. Please note that there are two flavors of Data Studio. The IDE version that allows you to shell share with other IBM products and the lightweight version that does not allow shell sharing. From Data Studio, you can also connect to DB2/zOS and DB2/i5OS without installing DB2 Connect! You can read more about this tool in at IBM Data Studio.
If you install IBM Data Studio you can use it to connect to DB2 on any platform without any other tools from within Data Studio. However, if you want to connect to the database outside of Data Studio then you will need to install the IBM Data Server Runtime in addition to Data Studio. This means that if you want to run your scripts or queries from the command line (CLP) or you want the PHP, C++ or other libraries to run your application, then your need the runtime client too. Even though Data Studio has a JDBC driver, you will need to install another one if you want to run Java programs that connect to your database outside of Data Studio. In short, Data Studio allows you to connect from Data Studio and nothing else.
DB2 10.1 and future releases no longer ship with Control Center. You will need to begin using Data Studio instead for your GUI administration needs.
Drivers and Driver Packages
There are four sets of drivers:
The IBM Data Server Driver for JDBC and SQLJ provides support for JDBC and SQLJ for client applications developed in Java. It supports JDBC 3 and JDBC 4 standard. It is also known as the JCC driver. With this driver you can connect directly to DB2 on Linux, UNIX and Windows with no other licenses of files. It will allow your applications to connect directly to DB2 on z/OS (zSeries) and i5/OS (iSeries) with just the addition of the license jar file that can be downloaded from Passport Advantage if you are licensed to use DB2 Connect. It is free to download and use for connections to Linux, UNIX and Windows databases.
The IBM Data Server Driver for ODBC and CLI is the smallest of all the client packages and provides support for Open Database Connectivity (ODBC) and Call Level Interface (CLI) libraries for C/C++ client applications. It is free to download and use. If you also wish to connect to DB2 on z/OS or i5/OS then you need to be licensed for DB2 Connect as well. To Connect ot DB2/zOS or DB2/I you can connect from your client through an existing DB2 Connect gateway server, or copy the DB2 Connect license file to the client to allow direct client connectivity. DB2 Connect does not need to be installed on the client to allow direct CLI connectivity to the host when using newer versions of the DB2 CLI.
The IBM Data Server Driver Package includes everything from both of the driver packages above plus additional items. It contains drivers and libraries for various programming language environments. It provides support for Java (JDBC and SQLJ), C/C++ (ODBC and CLI), .NET drivers and database drivers for open source languages like PHP and Ruby. It also includes an interactive client tool called CLPPlus that is capable of executing SQL statements, scripts and can generate custom reports in a very similar manner to Oracle’s SQLPlus interface. It will allow your Java applications to connect directly to DB2 on z/OS (zSeries) and i5/OS (iSeries) with just the addition of the license jar file that can be downloaded from Passport Advantage if you are licensed to use DB2 Connect. It is free to download and use for connections to Linux, UNIX and Windows databases. Other interfaces require DB2 Connect for connections to z/OS and i5/OS.
The IBM Database Add-Ins for Visual Studio package contains the add-ins for Visual Studio for .NET tooling support.
The various editions of DB2 Connect allow you to connect to DB2 on z/OS (zSeries or System z) and i5/OS (iSeries). You never need DB2 Connect to connect to DB2 on Linux, UNIX or Windows – it is only needed to connect ASCII clients to EBCDIC databases. However, when you install DB2 Connect you always get at least the Runtime Client which means that once you install DB2 Connect you can connect to ALL flavors of DB2 regardless of platform. When you install DB2 Connect you can also optionally install the Control Center (Data Server Client) as well by choosing that option. DB2 Connect also installs the JDBC driver as well as the license jar file for connecting java applications to DB2 on “i” and “z” systems as well. That license jar file can also be downloaded from your Passport Advantage site too. It is part of the Activation Kit.
Your Database always comes with at least the IBM Data Server Runtime Client. This allows you to connect to remote databases from the local database server just as you would from any server or workstation with the DB2 client. When you catalog a remote database on the local server that database becomes available to clients of the local server. That means that the remote client can catalog the database that it sees on your local server that is really just a catalog entry itself pointing to the remote database server. The client can’t tell the difference between local and catalog entries on remote servers. You can even chain multiple database servers together. I don’t usually recommend chaining databases, but it is interesting none the less.
For the most part all supported versions of DB2 servers and DB2 clients will talk to each other. However, you will be limited to using only the subset of functions provided in both versions. That is, if a new feature is introduced in DB2 v9, and you connect to it from a DB2 v8 client, then you will not be able to execute the new functions of DB2 v9 using the v8 client. Here are the client versions work with each DB2 Server Version:
Fix packs and clients
Just as it is a good idea to keep the maintenance or fix pack levels relatively up to date on your database server, it is a good idea to keep your client updated with recent fix packs. This will help you avoid trouble. You should note that as long as both your database and clients are on relatively recent fix pack levels, there is no need to keep them in sync. Through my many years of being a DB2 DBA, I have never seen an instance where a problem was caused because the fix pack levels were not the same between the client and database. However, I have seen many cases where an avoidable problem was encountered because maintenance was deferred for too long on either. You can download client fix packs from Fix Central.
It is also good to note you can just install the client fix pack and have the client. This means that you don’t need to lay down the base build of the client and then apply the fix pack. You can just install the fix pack and have everything. You do not need to apply any license keys when you install a client.
Connecting to the Database
To connect non-Java drivers and clients to a database you can either CATALOG the database connection information or add a db2dsdriver.cfg file to those clients. You can update the db2dsdriver.cfg file with your favorite editor. It is an XML file that can also be used for several other client configurations as well including pureScale. It allows you to create connections for your client and e-mail it to your users instead of cataloging databases on each client. Here are some important points
Using your Clients