Call Level Interface Guide and Reference

Running CLI/ODBC Programs

The DB2 Call Level Interface (CLI) run-time environment and the DB2 CLI/ODBC driver are included with DB2 clients as optional components during install.

This support enables applications developed using ODBC and DB2 CLI APIs to work with any DB2 server. DB2 CLI application development support is provided by the DB2 Application Development Client which is packaged with your DB2 server.

Before DB2 CLI or ODBC applications can access DB2, the DB2 CLI packages must be bound on the server. Although this will occur automatically on the first connection if the user has the required authority to bind the packages, it is recommended that the administrator do this first with each version of the client on each platform that will access the server. See How to Bind the DB2 CLI/ODBC Driver to the Database for specific details.

The following general steps are required on the client system to give DB2 CLI and ODBC applications access to DB2 databases. These instructions assume that you have successfully connected to DB2 using a valid user ID and password. Depending on the platform many of these steps are automatic. For complete details, see the section that deals specifically with your platform.

Step  1.
Use the Client Configuration Assistant (CCA) to add the database (if you have separate client and server machines) so that its instances and databases can be made known to the Control Center, then add the instances and databases for that system. If you do not have access to this program you can use the catalog command in the command line processor.
Step  2.
The DB2 CLI/ODBC driver is an optional component during the DB2 client install on Windows platforms. Be sure it is selected at that point. On OS/2 you must use the Install ODBC Driver icon to install both the DB2 CLI/ODBC driver and the ODBC driver manager. On UNIX platforms the DB2 CLI/ODBC driver is automatically installed with the client.
Step  3.
To access the DB2 database from ODBC:

The ODBC Driver Manager (From Microsoft or other vendor) must already be installed (this is done by default during the installation of DB2 only on 32-bit Windows systems).
The DB2 databases must be registered as ODBC data sources. The ODBC driver manager does not read the DB2 catalog information; instead it references its own list of data sources.
If a DB2 table does not have a unique index then many ODBC applications will open it as read-only. A unique index should be created for each DB2 table that is to be updated by an ODBC application. Refer to the CREATE INDEX statement in the SQL Reference. Using the Control Center you would alter the settings of the table, then select the Primary Key tab and move one or more columns from the available columns list over to the primary key columns list. Any column you select as part of the primary key must be defined as NOT NULL.

Step  4.
If necessary, you can set various CLI/ODBC Configuration Keywords to modify the behavior of DB2 CLI/ODBC and the applications using it.

If you followed the above steps to install ODBC support, and added DB2 databases as ODBC data sources, your ODBC applications will now be able to access them.

After the platform specific instructions there are further details on the following topics:

How to Bind the DB2 CLI/ODBC Driver to the Database
How to Set CLI/ODBC Configuration Keywords
Configuring db2cli.ini

Platform Specific Details for CLI/ODBC Access

The platform specific details on how to give DB2 CLI and ODBC applications access to DB2 are divided into the following categories:

Windows 32-bit operating systems Client Access to DB2 using CLI/ODBC
OS/2 Client Access to DB2 using CLI/ODBC
UNIX Client Access to DB2 using CLI/ODBC

Windows 32-bit operating systems Client Access to DB2 using CLI/ODBC

Before DB2 CLI and ODBC applications can successfully access a DB2 database from a Windows client, perform the following steps on the client system:

Step  1.
The DB2 database (and node if the database is remote) must be cataloged. To do so, use the CCA (or the command line processor).
For more information refer to the online help in the CCA (or the CATALOG DATABASE and CATALOG NODE commands in the Command Reference).
Step  2.
Verify that the Microsoft ODBC Driver Manager and the DB2 CLI/ODBC driver are installed. On Windows 32-bit operating systems they are both installed with DB2 unless the ODBC component is manually unselected during the install. DB2 will not overwite a newer version of the Microsoft ODBC Driver Manager if one is found.
To verify that they both exist on the machine:

Start the Microsoft ODBC Data Sources icon in the Control Panel, or run the odbcad32.exe command from the command line.
Click on the Drivers tab.
Verify that "IBM DB2 ODBC DRIVER" is shown in the list.

If either the Microsoft ODBC Driver Manager or the IBM DB2 CLI/ODBC driver is not installed, then rerun the DB2 install and select the ODBC component on Windows 32-bit operating systems.
Step  3.
Register the DB2 database with the ODBC driver manager as a data source. On Windows 32-bit operating systems you can make the data source available to all users of the system (a system data source), or only the current user (a user data source). Use either of these methods to add the data source:

Using the CCA:

Select the DB2 database alias that you want to add as a data source.
Click on the Properties push button. The Database Properties window opens.
Select the Register this database for ODBC check box.
On Windows 32-bit operating systems you can use the radio buttons to add the data source as either a user or system data source.

Using the Microsoft 32-bit ODBC Administration tool, which you can access from the icon in the Control Panel or by running odbcad32.exe from the command line:

On Windows 32-bit operating systems the list of user data sources appears by default. If you want to add a system data source click on the System DSN button, or the System DSN tab (depending on the platform).
Click on the Add push button.
Double-click on the IBM DB2 ODBC Driver in the list.
Select the DB2 database to add and click on OK.

On Windows 32-bit operating systems there is a command that can be issued in the command line processor to register the DB2 database with the ODBC driver manager as a data source. An administrator could create a command line processor script to register the required databases. This script could then be run on all of the machines that require access to the DB2 databases through ODBC.
The Command Reference contains more information on the CATALOG command:
CATALOG [ user | system ] ODBC DATA SOURCE

Step  4.
Configure the DB2 CLI/ODBC driver using the CCA: (Optional)

Select the DB2 database alias you want to configure.
Click on the Properties push button. The Database Properties window opens.
Click on the Settings push button. The CLI/ODBC Settings window opens.
Click on the Advanced push button. You can set the configuration keywords in the window that opens. These keywords are associated with the database alias name, and affect all DB2 CLI/ODBC applications that access the database. The online help explains all of the keywords, as does Configuration Keywords.
For information on manually editing this file (db2cli.ini), see Configuring db2cli.ini.

Step  5.
If you have installed ODBC access (as described above), you can now access DB2 data using ODBC applications. Start the ODBC application and go to the Open window. Select the ODBC databases file type. The DB2 databases that you added as ODBC data sources will be selectable from the list. Many ODBC applications will open the table as read-only unless a unique index exists.

OS/2 Client Access to DB2 using CLI/ODBC

Before DB2 CLI and ODBC applications can successfully access a DB2 database from an OS/2 client, perform the following steps on the client system:

The DB2 database (and node if the database is remote) must be cataloged. To do so, use the CCA (or the command line processor).
For more information, see the online help in the CCA. (or the CATALOG DATABASE and CATALOG NODE commands in the Command Reference). (or the CATALOG DATABASE and CATALOG NODE commands in the Command Reference).
If you are using ODBC applications to access DB2 data, perform the following steps. (If you are using only CLI applications, skip this step and go to the next step.)
1. Check that there is an ODBC Driver Manager installed. The ODBC Driver Manager is not installed with DB2; we suggest you use the Driver Manager that was shipped with your ODBC application. Also ensure that the DB2 CLI/ODBC driver is installed:
  1. Run the ODBC Administration tool as described in its documentation. This is usually done in one of two ways:
    - Double-click on the ODBC Folder in OS/2, and double-click on the ODBC Administrator icon.
    - Run odbcadm.exe from the command line.
    The Data Sources window opens.
  2. Click on the Drivers push button. The Drivers window opens.
  3. Verify that "IBM DB2 ODBC DRIVER" is shown in the list.
  If the ODBC Driver Manager is not installed then follow the installation instructions that came with your ODBC application. If the IBM DB2 CLI/ODBC driver is not installed then double-click on the Install ODBC Driver icon in the DB2 folder to install the DB2 CLI/ODBC driver.
2. Register the DB2 database with the ODBC driver manager as a data source using either of these methods:
  - Using the CCA:
    1. Select the DB2 database alias that you want to add as a data source.
    2. Click on the Properties push button.
    3. Select the Register this database for ODBC check box.
  - Using the ODBC Driver Manager:
    1. Run the ODBC Driver Manager, as described in its documentation. This is usually done in one of two ways:
      - Double-click on the ODBC Folder in OS/2, and double-click on the ODBC Administrator icon.
      - Run odbcadm.exe from the command line.
    2. Click on the Add push button from the Data Sources window. The Add Data Source Window opens.
    3. Double-click on the IBM DB2 ODBC DRIVER in the list.
    4. Select the DB2 database to add and click on OK.
Configure the DB2 CLI/ODBC driver using the CCA: (Optional)
1. Select the DB2 database alias you want to configure.
2. Click on the Properties push button. The Database Properties window opens.
3. Click on the Settings push button. The CLI/ODBC Settings window opens.
4. Click on the Advanced push button. You can set the configuration keywords in the window that appears. These keywords are associated with the database alias name, and affect all DB2 CLI/ODBC applications that access the database. The online help explains all of the keywords, as does Configuration Keywords.
  For information on manually editing this file (db2cli.ini), see Configuring db2cli.ini.
If you have installed ODBC access (as described above), you can now access DB2 data using ODBC applications. Start the ODBC application and go to the Open window. Select the ODBC databases file type. The DB2 databases that you added as ODBC data sources will be selectable from the list. Many ODBC applications will open the table as read-only unless a unique index exists.

UNIX Client Access to DB2 using CLI/ODBC

Before DB2 CLI and ODBC applications can successfully access a DB2 database from a UNIX client, perform the following steps on the client system:

The DB2 database (and node if the database is remote) must be cataloged. To do so, use the command line processor.
For more information see the CATALOG DATABASE and CATALOG NODE commands in the Command Reference.
The DB2 CLI/ODBC driver is installed during the DB2 client install. Be sure it is selected at that point.
If you are using ODBC applications to access DB2 data, perform the following steps. (If you are using only CLI applications, skip this step and go to the next step.)
1. When using an ODBC application you must ensure that an ODBC Driver Manager is installed and that each user that will use ODBC has access to it. DB2 does not install an ODBC Driver Manager, you must use the ODBC Driver Manager that was supplied with your ODBC client application or ODBC SDK in order to access DB2 data using that application.
2. The Driver Manager uses two initialization files.
  
  odbcinst.ini
  ODBC Driver Manager's configuration file indicating which database drivers are installed. Each user that will use ODBC must have access to this file.
  .odbc.ini
  End-user's data source configuration. Each user ID has a separate copy of this file in their home directory. Note that the file starts with a dot.
  
  Setting up odbcinst.ini
  The settings in this file impact all of the ODBC drivers on the machine.
  Use an ASCII editor to update this file. It must have a stanza (section) called [IBM DB2 ODBC DRIVER], with a line starting with "Driver" indicating the full path to the DB2 ODBC driver, called db2.o on AIX, and libdb2 on other UNIX platforms (the file extension depends on the platform; libdb2.so on Solaris Operating Environment, and so on). For example, on AIX, if the home directory of your end user is /u/thisuser/ and the sqllib directory is installed there, then the correct entry would be:
```
   [IBM DB2 ODBC DRIVER]
   Driver=/u/thisuser/sqllib/lib/db2.o
```
  Setting up .odbc.ini
  The settings in this file are associated with a particular user on the machine; different users can have different .odbc.ini files.
  The .odbc.ini file must exist in the end user's home directory (note the dot at the start of the file name). Update this file, using an ASCII editor, to reflect the appropriate data source configuration information. To register a DB2 database as an ODBC data source there must be one stanza (section) for each DB2 database.
  The .odbc.ini file must contain the following lines:
  - in the [ODBC Data Source] stanza:
```
   SAMPLE=IBM DB2 ODBC DRIVER
```
    Indicates that there is a data source called SAMPLE that used the IBM DB2 ODBC DRIVER.
  - in the [SAMPLE] stanza (on AIX, for example):
```
   [SAMPLE]
   Driver=/u/thisuser/sqllib/lib/libdb2.a
   Description=Sample DB2 ODBC Database
```
    Indicates that the SAMPLE database is part of the DB2 instance located in the directory /u/thisuser.
  - in the [ODBC] stanza:
```
   InstallDir=/u/thisuser/sqllib/odbclib
```
    Indicates that /u/thisuser/sqllib/odbclib should be treated as the location where ODBC is installed.
  - Ensure that the InstallDir correctly points to the ODBC Driver Manager location.
    For example, if the ODBC Driver Manager has been installed in /opt/odbc, the [ODBC] stanza would look like:
```
   [ODBC]
   Trace=0
   TraceFile=odbctrace.out
   InstallDir=/opt/odbc
```
  See How to Configure ODBC.INI for more detailed information.
  Once the .ini files are set up you can run your ODBC application and access DB2 databases. Refer to the documentation that comes with your ODBC application for additional help and information.
Configure the DB2 CLI/ODBC driver (Optional).
There are various keywords and values that can be used to modify the behavior of DB2 CLI/ODBC and the applications using it. The keywords are associated with the database alias name, and affect all DB2 CLI/ODBC applications that access the database.
For information on manually editing this file (db2cli.ini), see Configuring db2cli.ini. For information about the specific keywords see Configuration Keywords.

Detailed Configuration Information

The section Platform Specific Details for CLI/ODBC Access should provide you with all of the information you require. The following additional information is useful where DB2 tool support is not available, and for administrators who require more detailed information.

How to Bind the DB2 CLI/ODBC Driver to the Database
How to Set CLI/ODBC Configuration Keywords
Configuring db2cli.ini

How to Bind the DB2 CLI/ODBC Driver to the Database

The CLI/ODBC driver will autobind on the first connection to the database, provided the user has the appropriate privilege or authorization. The administrator may want to perform the first connect or explicitly bind the required files.

Table 10. DB2 CLI Bind Files and Package Names

Bind File Name Package Name Needed by DB2 Universal Database Needed by DRDA servers
db2clish.bnd SQLLFyxx Yes Yes
db2clisn.bnd SQLLCyxx Yes Yes
db2clibh.bnd SQLLDyxx Yes Yes
db2clihn.bnd SQLLEyxx Yes Yes
db2cliws.bnd SQLL65zz Version 2 or later No
db2clims.bnd SQLL75zz No DB2 for MVS/ESA
db2clivm.bnd SQLL85zz No SQL/DS
db2cliv1.bnd SQLLB5zz Version 1 only No
db2cliv2.bnd SQLL95zz Version 2 or later No
db2clias.bnd SQLLA5zz No DB2 Universal Database for AS/400

Note:

Where 'xx' is a hexadecimal value between 00 - FF.
Where 'y' ranges between 0 - 4.
Where 'zz' is unique for each platform.

Previous versions of DB2 servers do not need all of the bind files and will therefore return errors at bind time.

The db2cli.lst file contains the names of the required bind files for DB2 CLI to connect to DB2 Version 2 or later servers (db2clixx.bnd where xx is cs, rr, rs, ur, ws, and v2). The db2cli1.lst file contains the names of the required bind files for DB2 CLI to connect to DB2 Version 1 servers (db2clixx.bnd where xx is cs, rr, ur, and v1).

For DRDA servers:

use one of ddcsvm.lst, ddcsmvs.lst, ddcsvse.lst, or ddcs400.lst bind list files.
Refer to the SYSSCHEMA keyword in Configuration Keywords.
Refer to the Quick Beginnings or DB2 Connect Enterprise Edition for OS/2 and Windows Quick Beginnings for details about required bind options.

How to Set CLI/ODBC Configuration Keywords

DB2 CLI can be configured further by using either the CCA or the DB2 Client Setup administration tool, whichever is applicable for your platform, or by manually editing the db2cli.ini file.

This file contains various keywords and values that can be used to modify the behavior of DB2 CLI and the applications using it. The keywords are associated with the database alias name, and affect all DB2 CLI and ODBC applications that access the database.

By default, the location of the CLI/ODBC configuration keyword file is located in the sqllib directory on Intel platforms, and in the sqllib/cfg directory of the database instance running the CLI/ODBC applications on UNIX platforms.

The environment variable DB2CLIINIPATH can also be used to override the default and specify a different location for the file.

The configuration keywords enable you to:

Configure general features such as data source name, user name, and password.
Set options that will affect performance.
Indicate query parameters such as wild card characters.
Set patches or work-arounds for various ODBC applications.
Set other, more specific features associated with the connection, such as code pages and IBM Graphic data types.

For a complete description of all the keywords and their usage, refer to Configuration Keywords.

Configuring db2cli.ini

The db2cli.ini initialization file is an ASCII file which stores values for the DB2 CLI configuration options. A sample file is shipped to help you get started. Refer to Configuration Keywords for information on each keyword.

See Platform Specific Details for CLI/ODBC Access for more information on how to modify this file on your platform.

There is one section within the file for each database (data source) the user wishes to configure. If needed, there is also a common section that affects all connections to DB2.

Only the keywords that apply to all connections to DB2 through the DB2 CLI/ODBC driver are included in the COMMON section. This includes the following keywords:

DISABLEMULTITHREAD
TRACE
TRACEFILENAME
TRACEFLUSH
TRACEPATHNAME

All other keywords are to be placed in the database specific section, described below.

The COMMON section of the db2cli.ini file begins with:

[COMMON]

Before setting a common keyword it is important to evaluate its impact on all DB2 CLI/ODBC connections from that client. A keyword such as TRACE, for instance, will generate information on all DB2 CLI/ODBC applications connecting to DB2 on that client, even if you are intending to troubleshoot only one of those applications.

Each database specific section always begins with the name of the database alias between square brackets:

[database alias]

This is called the section header.

The parameters are set by specifying a keyword with its associated keyword value in the form:

KeywordName =keywordValue

All the keywords and their associated values for each database must be located below the database section header.
The keyword settings in each section apply only to the database alias named in that section header.
The keywords are not case sensitive; however, their values can be if the values are character based.
For the syntax associated with each keyword, refer to DB2 CLI/ODBC Configuration Keyword Listing.
If a database is not found in the .INI file, the default values for these keywords are in effect.
Comment lines are introduced by having a semicolon in the first position of a new line.
Blank lines are permitted.
If duplicate entries for a keyword exist, the first entry is used (and no warning is given).

The following is a sample .INI file with 2 database alias sections:

; This is a comment line.
[MYDB22]
AUTOCOMMIT=0
TABLETYPE="'TABLE','SYSTEM TABLE'"
 
; This is another comment line.
[MYDB2MVS]
DBNAME=SAAID
TABLETYPE="'TABLE'"
SCHEMALIST="'USER1',CURRENT SQLID,'USER2'"

Although you can edit the db2cli.ini file manually on all platforms, we recommend that you use the CCA if it is available on your platform. You must add a blank line after the last entry if you manually edit the db2cli.ini file.

How to Configure ODBC.INI

Microsoft's 16-bit ODBC Driver Manager and all non-Microsoft ODBC Driver Managers use the odbc.ini file to record information about the available drivers and data sources. ODBC Driver Managers on UNIX platforms also uses the odbcinst.ini file. Although the necessary files are updated automatically by the tools on most platforms, users of ODBC on UNIX platforms will have to edit them manually. The file odbc.ini (and odbcinst.ini where required) are located:

UNIX: Home directory of the user ID running the ODBC application (on UNIX the odbc.ini file name has a dot before it: .odbc.ini)

It is also possible to modify this file manually. Do not change any of the existing entries in the file. To edit this file manually perform the following steps:

Step  1.
Use an ASCII editor to edit the odbc.ini file.
The following is an example odbc.ini file:
[ODBC Data Sources] MS Access Databases=Access Data (*.mdb) [MS Access Databases] Driver=D:\WINDOWS\SYSTEM\simba.dll FileType=RedISAM SingleUser=False UseSystemDB=False

The [ODBC Data Sources] section lists the name of each available data source and the description of the associated driver.
For each data source listed in the [ODBC Data Sources] section, there is a section that lists additional information about that data source. These are called the Data Source Specification sections.
Step  2.
Under the [ODBC DATA SOURCE] entry, add the following line:
database_alias=IBM DB2 ODBC DRIVER

where database_alias is the alias of the database cataloged in the database directory (the database name used by the command line processor CONNECT TO statement).
Step  3.
Add a new entry in the Data Source Specification section to associate the data source with the driver:
[database_alias] Driver=x:\windows\system\db2cliw.dll

where:

database_alias is the alias of the database cataloged in the database directory, and listed under the Data Source Specification section.
x: is the drive where the Windows operating system is installed.

The following shows the example file with the IBM data source entries added:

     [ODBC Data Sources]
     MS Access Databases=Access Data (*.mdb)
     SAMPLE=IBM DB2 ODBC DRIVER
 
     [MS Access Databases]
     Driver=D:\WINDOWS\SYSTEM\simba.dll
     FileType=RedISAM
     SingleUser=False
     UseSystemDB=False
 
     [SAMPLE]
     Driver=D:\WINDOWS\SYSTEM\db2cliw.dll
     Description=Sample DB2 Client/Server database

UNIX Configuration of .ini files

The section UNIX Client Access to DB2 using CLI/ODBC contains detailed steps on how to update both the odbc.ini and odbcinst.ini files.

[ Top of Page | Previous Page | Next Page ]

Step 1.	Use the Client Configuration Assistant (CCA) to add the database (if you have separate client and server machines) so that its instances and databases can be made known to the Control Center, then add the instances and databases for that system. If you do not have access to this program you can use the catalog command in the command line processor.
Step 2.	The DB2 CLI/ODBC driver is an optional component during the DB2 client install on Windows platforms. Be sure it is selected at that point. On OS/2 you must use the Install ODBC Driver icon to install both the DB2 CLI/ODBC driver and the ODBC driver manager. On UNIX platforms the DB2 CLI/ODBC driver is automatically installed with the client.
Step 3.	To access the DB2 database from ODBC: The ODBC Driver Manager (From Microsoft or other vendor) must already be installed (this is done by default during the installation of DB2 only on 32-bit Windows systems). The DB2 databases must be registered as ODBC data sources. The ODBC driver manager does not read the DB2 catalog information; instead it references its own list of data sources. If a DB2 table does not have a unique index then many ODBC applications will open it as read-only. A unique index should be created for each DB2 table that is to be updated by an ODBC application. Refer to the CREATE INDEX statement in the SQL Reference. Using the Control Center you would alter the settings of the table, then select the Primary Key tab and move one or more columns from the available columns list over to the primary key columns list. Any column you select as part of the primary key must be defined as NOT NULL.
Step 4.	If necessary, you can set various CLI/ODBC Configuration Keywords to modify the behavior of DB2 CLI/ODBC and the applications using it.