Setting Up the CLI Environment

Several steps are involved in setting up a DB2 UDB CLI environment. Slight differences are necessary depending on whether a development environment is needed or just the run-time environment. However, the basic steps are as follows:

• The appropriate DB2 UDB package must be installed. If a developer's environment is needed then the SDK is necessary; otherwise, at a minimum, the DB2 UDB Client Application Environment (CAE) is needed.
  
  
• The database being accessed is cataloged properly. If the database is remote, a node must also be cataloged.
  
  
• DB2 UDB CLI bind files must be bound to the database.
  
  
• Configure the CLI environment using the CCA or edit the db2cli.ini file directly. It is important to remember to examine the CLI environment settings in the db2cli.ini file, or by using the CCA. The settings affect the execution behavior of all CLI applications executing on the system.
  
  

DB2 UDB Installation

The DB2 UDB Quick Beginnings Guide is a very good reference for installing DB2 UDB, the specifics of which are outside the scope of this book. The only concern here is that the appropriate DB2 UDB product is installed so that a CLI or ODBC application can be either developed or executed. As has already been mentioned, to execute the application, at a minimum, the DB2 UDB CAE must be installed. Alternatively, the entire DB2 UDB server can be installed including the DB2 UDB SDK.

To develop a CLI application, you must install the DB2 UDB SDK. All of the CLI APIs are contained in a static library. The names of the libraries on the various DB2 UDB development operating systems are:

• db2cli.lib on OS/2 and Windows platforms

• libdb2.a or libdb2.so on UNIX platforms

Also, the SDK includes various header files that are needed for CLI development. These include sqlcli.h and sqlcli1.h.

To develop ODBC applications, an ODBC SDK must also be installed in addition to (at minimum) the DB2 CAE. The DB2 SDK is not needed because the ODBC SDK provides its own static libraries and header files for successful compilation of an application. In Windows, Microsoft supplies this kit. For its configuration for use with DB2 UDB, please see the upcoming section, “Accessing a DB2 Database via ODBC.”

Database Cataloging

Again, the DB2 UDB Quick Beginnings Guide is an excellent reference for cataloging a local or remote database. This can be accomplished by either using the command-line prompt with DB2 commands or by using the CCA, the details of which will be left to that manual. The exception to this is cataloging a database for use with ODBC. See the upcoming section, “Accessing a DB2 Database via ODBC.”

CLI Bind Files

The bind files required for CLI applications are no longer automatically bound to the DB2 server if it is discovered that they are not present. The DBA is now responsible for ensuring that the needed packages are bound to the database server. When a workstation database is created, the necessary packages are automatically bound for the current level of DB2. Therefore, the database administrator might be required to bind the necessary files manually using the DB2 UDB BIND command or the CCA. Each of the supported DB2 servers use different bind files. See Table 11.1.

For example, to manually bind the CLI packages from a DB2 UDB command window on Windows NT against a DB2 UDB for AIX database, you would issue the following command after connecting to the database:

DB2 BIND @db2cli.lst MESSAGES db2cli.msg GRANT PUBLIC

Likewise, if the DB2 database resides on OS/390, you could use the command:

DB2 BIND @ddcsmvs.lst BLOCKING ALL SQLERROR CONTINUE MESSAGES mvsbind.msg GRANT PUBLIC

The syntax for the BIND command can be referred to in the DB2 UDB Command Reference Guide.

Configuring CLI

Usually, it is not necessary to modify the DB2 UDB CLI configuration file (db2cli.ini). It is important to understand that the file exists and might require small modifications. Some of the reasons for modifying the CLI configuration file include:

• to increase CLI application performance

• to change default CLI behavior

• to enable workarounds for specific applications

The db2cli.ini file is located in the sqllib/cfg directory of the instance owner in UNIX systems, or the sqllib directory for OS/2 and Windows operating systems.

An example db2cli.ini file is shown here. There are many more options that can be specified in the CLI configuration file.

; Comment goes here
[DB2MALL]
CURSORHOLD=0
DEFERREDPREPARE=1
PATCH1=2

The first line is a comment about the section of the file. Multiple databases can be configured in this file. The second line contains the database alias name in the brackets, [DB2MALL]. The DB2MALL database can still be accessed from a DB2 UDB CLI application without an entry in the db2cli.ini file, but if there is no section for the DB2MALL database, all of the default values for the parameters will be used. This may not be desirable. The three lines below the database name contain keywords and corresponding values. The supported keywords are defined in the DB2 UDB Call Level Interface Guide and Reference.

The DB2 UDB CCA or ODBC Administration Tool on Windows and OS/2 platforms allow you to configure the CLI environment without editing the db2cli.ini file directly. The interface is easy to use and explains each parameter that can be modified.

Accessing a DB2 Database via ODBC

To access a DB2 database from ODBC, the following are required on the DB2 UDB client where the ODBC application executes:

• DB2 UDB run-time client or server must be installed. If the database is on a remote DB2 system, it should be cataloged correctly and be accessible for connecting to.

• The ODBC Driver Manager must be installed.

• An ODBC driver for DB2 UDB must be installed and registered with the ODBC Driver Manager.

• The DB2 database must be registered as an ODBC data source with the Driver Manager.

An ODBC Driver Manager must be installed on the computer where the ODBC application has been installed. For all Microsoft operating systems, Microsoft provides the ODBC Driver Manager.

The IBM DB2 UDB ODBC driver or another ODBC driver for DB2 must be installed and registered. The Microsoft ODBC Driver Manager and the DB2 UDB ODBC driver are automatically installed on the Windows platforms during DB2 UDB installation as long as the ODBC component, highlighted by default, is not unchecked. The DB2 UDB ODBC driver is also registered with the driver manager during installation of DB2 UDB on Windows platforms. On Windows platforms, you can run the Microsoft ODBC Administrator from the Control Panel to verify that “IBM DB2 ODBC Driver” is shown on the list. On UNIX platforms, the DB2 UDB ODBC driver and databases are specified using odbc.ini and .odbcinst.ini files in the home directory of the user running the ODBC application.

The database must be identified to the ODBC Driver Manager as an available data source. The data source can be made available to all users of the system (a system data source), or only for the current user (a user data source). In Windows, you can register the data source with the Driver Manager using the CCA. Databases configured through the CCA are selected as system ODBC data sources by default, unless you explicitly uncheck the selection. For non-Windows platforms, this is accomplished by using the appropriate ODBC Administration tool or by configuring the Driver Manager manually. For the exact configuration methods, refer to the documentation provided with the ODBC Driver Manager.

ODBC Development Considerations

For ODBC application development, you must obtain an ODBC SDK just as you would the DB2 UDB SDK for developing CLI applications.

The steps of binding the CLI packages and customizing CLI using db2cli.ini or the CCA are also applicable to ODBC applications that will access DB2 data sources. These steps are performed in the same way they are for CLI applications.