New Year’s Resolutions: Help your DB2 Client Lose Weight

Nearly all DB2 DBAs are familiar with the big, heavy DB2 client - especially if you’ve had the misfortune of having to download the 400+ MB behemoth over a slow Internet connection. Some of us have started using the Runtime Client in the quest for a smaller client footprint; this provides essentially the same functionality as the full client, only excluding the Java graphical tools.

Client, Size (9.7.7), Size (10.1.2)
IBM Data Server Client, 420 MB, 580 MB
IBM Data Server Runtime Client, 130 MB, 156 MB
IBM Data Server Driver Package, 75 MB, 91 MB
IBM Data Server Driver for ODBC and CLI, 33 MB, 33 MB
IBM Data Server Driver for JDBC and SQLJ 10 MB, 11 MB

Given that the graphical Java tools like Control Center have been deprecated, continuing to use the full Data Server Client doesn’t make much sense. In fact, in DB2 10.1, the only GUI tool that is included in the full Data Server Client is Replication Center. The Data Server Runtime Client certainly helps to reduce the footprint.

However, there are other options to trim even more weight off of your install: the IBM Data Server Driver packages.  You may have tried to install the Data Server Driver packages and then discovered that they do not include the standard DB2 Command Line Processor - leaving you wondering how to set up client connections.

One of the biggest advantages the Data Server Driver packages have over the Data Server Runtime Client is that they make it much easier to handle defining, removing and updating ODBC data sources when you need to script the changes.

Choosing the Correct Data Server Driver Packages

The IBM Data Server Drivers Package is available in three different flavors:

  • The IBM Data Server Driver Package, which includes pre-compiled drivers for ODBC, CLI1, JDBC, SQLJ, PHP, Python and Ruby applications. In addition, it also includes CLPPlus.
  • The IBM Data Server Driver for ODBC and CLI, which includes connectivity only for ODBC and CLI applications.
  • The IBM Data Server Driver for JDBC and SQLJ, which includes only the driver files for JDBC and SQLJ applications.

Installing the Data Server Driver on Windows

The IBM Data Server Driver Package is packaged in a normal Windows installer package, so when you double click on the executable you can follow the normal installation procedures just like any other Windows program.

The IBM Data Server Driver for ODBC and CLI is packaged as a ZIP file that you simply extract into the directory of your choice. Once you have unzipped the file, you’ll need to manually update the PATH system environment variable to add the ...\clidriver\bin directory so that you’ll be able to execute the proper commands to configure the client.

Installing the Data Server Driver on UNIX or Linux

For UNIX and Linux platforms, both the IBM Data Server Driver Package and the IBM Data Server Driver for ODBC and CLI are packaged as compressed tar (.tar.gz) files. You should extract these files into an appropriate directory (like /opt/IBM). The steps to extract the files are the same for both the Data Server Driver Package and the Data Server Driver for ODBC and CLI (only the file name will be different).

  1  # mkdir –p /opt/IBM

  2  # cd /opt/IBM

  3  # tar xzvf /tmp/v10.1fp2_linuxx64_dsdriver.tar.gz

  4  dsdriver/

  5  dsdriver/license

 ... 

279  #

Once the files have been extracted, the installation is complete if you are using the Data Server Driver for ODBC and CLI. However, for the Data Server Driver Package, you must run the installDSDriver script to complete the installation. 

  1  # /opt/IBM/dsdriver/installDSDriver

This command extracts various drivers (PHP, Ruby, etc.) and configures the db2profile file which is necessary to initialize the DB2 environment for client applications.

Defining Connections to Remote Databases

Instead of relying on the Database and Node directories, the Data Server Drivers rely on a single XML document called db2dsdriver.cfg to store all of the connection information and options. This file is much more powerful and flexible than the standard database and node directories.

Modifying db2dsdriver.cfg using the db2cli Command

The simplest method to add databases to your db2dsdriver.cfg file is to use the db2cli command. This does not require that you even know where the db2dsdriver.cfg file is located!

To add a new DB2 database, open a Command Prompt, and execute the following command:

db2cli writecfg add -dsn SAMPLE -database SAMPLE -host db2.server.com -port 50000

This will create the db2dsdriver.cfg file in the proper location (if it does not already exist), and add the correct entries to connect to the SAMPLE database that’s available on db2.server.com, port 50000.

The db2cli command may also be used to remove entries from the db2dsdriver.cfg file by executing the exact same command, but simply replacing the add option with remove.

Creating and Editing the db2dsdriver.cfg File Manually

To find the proper location of this file, open a Command Prompt and run the db2cli validate command:

 1  C:\Users\Administrator>db2cli validate

 2  IBM DATABASE 2 Interactive CLI Sample Program

 3  (C) COPYRIGHT International Business Machines Corp. 1993,1996

 4  All Rights Reserved

 5  Licensed Materials - Property of IBM

 6  US Government Users Restricted Rights - Use, duplication or

 7  disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

 8    ---------------------------------------------------------------------------

 9    [ CLI Driver Version  : 10.01.0000 ]

10    [ Informational Tokens: "DB2 v10.1.200.238","s121127","IP23389","Fixpack 2" ]

11 

12    [ CLI Driver Type     : IBM Data Server Driver For ODBC and CLI ]

13    [ db2diag.log Path    : C:\ProgramData\IBM\DB2\IBMDBCL1\db2diag.log ]

14    ---------------------------------------------------------------------------

15 

16    IBM Data Server Client packages on the current workstation :

17 

18    Copyname      Version     Installed Location

19    ---------------------------------------------------------------------------

20    IBMDBCL1[C,D] 10.01.0002  C:\Program Files\IBM\IBM DATA SERVER DRIVER

21    ---------------------------------------------------------------------------

22 

23  db2dsdriver.cfg Schema Validation :

24  Note: The validation operation utility could not find the

25  configuration file named db2dsdriver.cfg.

26  The file is searched at C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg

27 

28  The validation completed.

In the output (on line 26) shown in the example above, the db2dsdriver.cfg file will be located at:

C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg

By default this file will not exist. If you have installed the Data Server Driver Package, it will include a file named db2dsdriver.cfg.sample that contains many examples of how to set up more complicated configurations. It is good as a basic reference, but for most client connections it is a lot easier to start with your own very basic file:

 1  <configuration>

 2    <dsncollection>

 3      <dsn alias="SAMPLE" host="db2.server.com" name="SAMPLE" port="50000"/>

 4    </dsncollection>

 5    <databases>

 6      <database name="SAMPLE" host="db2.server.com" port="50000"/>

 7    </databases>

 8  </configuration>

This defines a connection to the SAMPLE database running on the server db2.sample.com, on port 50000.

You can add additional databases to the configuration file by simply duplicating and updating the <database.../> and <dsn…/> lines.

Once you have modified this file, you should use the db2cli validate command again to ensure that your db2dsdriver.cfg file does not contain any XML errors:

 1  C:\ProgramData\IBM\DB2\IBMDBCL1\cfg>db2cli validate

...

22 

23  db2dsdriver.cfg Schema Validation :

24  Success: The schema validation operation completed successfully.

25  The configuration file C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg is valid

26 

27  The validation completed.

Validating Connections

You may wish to validate that you can actually connect to the database(s) that you have added. Once again, the db2cli utility can help here, too. Open a Command Prompt, and execute the following command:

db2cli validate -dsn SAMPLE -connect -user db2inst1 -passwd db2rules

In addition to validating the contents of your db2dsdriver.cfg file, this will also attempt to make a connection to the database that you specify:

 1  C:\ProgramData\IBM\DB2\IBMDBCL1\cfg>db2cli validate -dsn sample -connect -user db2inst1 -passwd ibmdb21

...

45 

46  Connection Section :

47    ---------------------------------------------------------------------------

48    Connecting to: sample

49    Connect Status: success

50    End Connection Section

51    ---------------------------------------------------------------------------

52 

53  The validation completed.

Registering ODBC Connections (Windows)

Once you have defined and optionally validated the connections in your db2dsdriver.cfg file, you will probably want to register the DSNs with the system’s ODBC driver manager. On Windows, you can accomplish this using the db2cli utility.

To set up the database connection as a System ODBC data source2, execute the following db2cli command:

db2cli registerdsn -dsn SAMPLE –system

As soon as this has been completed, you can open the ODBC Data Sources Control Panel, and see that SAMPLE has been added as a system data source.

You can also use the db2cli registerdsn command to list or remove data sources that have already been registered with the ODBC Driver Manager.

Conclusion

Starting off the year by simplifying the process of deploying the DB2 client is a great resolution. For a non-DBA (like an developer or end-user) who only ever connects to DB2 using an ODBC or JDBC tool, there is no need to provide even the DB2 command line processor (CLP).

Using the Data Server Driver Package not only makes your install package more svelte, it can also simplify what’s required to add, remove or update DB2 connections.

  1. For database programming, the acronym “CLI” means “Call Level Interface,” which is an ISO standard that defines client/server communication protocols for databases. 
  2. Windows allows two kinds of ODBC data sources: A System ODBC Data Source, which is one that is available to all users logged in to a machine; and a User ODBC Data Source, which is only available to the particular user that registered the connection. In general, system ODBC data sources are the most common.
2 Likes
Recent Stories
An Introduction to IBM Data Studio

The Basics of SQL Performance and Performance Tuning

Index Decluttering Opportunities in DB2 for Linux, UNIX, and Windows