Call Level Interface Guide and Reference

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 3.1, Windows 95 and Windows NT Client Access to DB2 using CLI/ODBC"
"OS/2 Client Access to DB2 using CLI/ODBC"
"UNIX Client Access to DB2 using CLI/ODBC"
"Macintosh Client Access to DB2 using CLI/ODBC"

Windows 3.1, Windows 95 and Windows NT Client Access to DB2 using CLI/ODBC

Before DB2 CLI and ODBC applications can successfully access a DB2 database from a Windows 3.1, Windows 95, or Windows NT 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 refer to the on-line help in the CCA (or the catalog command in the Command Reference).
Verify that the Microsoft ODBC Driver Manager and the DB2 CLI/ODBC driver are installed. On Windows 95 and Windows NT they are both installed with DB2 unless the ODBC component is manually unselected during the install. On Windows 3.1 you must use the Install ODBC Driver icon to install the Microsoft ODBC Driver Manager and the DB2 CLI/ODBC driver.
To verify that they both exist on the machine:
1. Run the Microsoft ODBC Administrator from the icon in the Control Panel, or issue the appropriate command from the command line: odbcad32.exe for Windows 95 or Windows NT operating systems, odbcadm.exe on Windows 3.1.
2. Click on the Drivers push button, or the ODBC Drivers tab (depending on the platform).
3. 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 in Windows 95 or Windows NT, or double-click on the Install ODBC Driver icon in the DB2 program group in Windows 3.1.
Register the DB2 database with the ODBC driver manager as a data source. On Windows 95 and Windows NT 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:
  1. Select the DB2 database alias that you want to add as a data source.
  2. Click on the Properties push button. The Database Properties window opens.
  3. Select the Register this database for ODBC check box.
  4. On Windows 95 and Windows NT you can use the radio buttons to add the data source as either a user or system data source.
- Using the Microsoft 32bit ODBC Administration tool, which you can access from the icon in the Control Panel or by running odbcad32.exe from the command line:
  1. On Windows 95 and Windows NT 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).
  2. Click on the Add push button.
  3. Double-click on the IBM DB2 ODBC Driver in the list.
  4. Select the DB2 database to add and click on OK.
- On Windows 95 and Windows NT 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.
  See the
```
   CATALOG [ user | system ] ODBC DATA SOURCE
```
  command in the Command Reference for more information.
Optional: Configure the DB2 CLI/ODBC driver using the CCA:
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 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".
Optional: Using 16-bit ODBC applications:
Although not directly related to DB2, some users have experienced problems when running 16-bit ODBC applications on Windows 95 or Windows NT.
Both 16- and 32-bit applications use the same 32-bit IBM DB2 CLI/ODBC Driver. The Microsoft ODBC Driver Manager takes care of the conversion between the 16-bit application and the 32-bit ODBC driver.
Some 16-bit applications were shipped with an old set of 16-bit Driver Manager DLLs (that is, before 1995). These DLLs do not function well in the 32-bit environment. The following symptoms may occur when running the old DLLs:
- Traps occur in odbc.dll or other ODBC related DLLs.
- 16-bit applications do not see data sources cataloged using the 32-bit ODBC Administrator (from the Control Panel).
If (and only if) you are experiencing these problems you may want to update the Microsoft 16-bit ODBC Driver Manager DLLs. These files are provided with DB2 in the SQLLIB\MSODBC16 subdirectory. Use these new DLLs to replace the older versions that are located in the system subdirectory of the Windows operating system directory.
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 on-line help in the CCA (or the catalog command 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 the ODBC Driver Manager (Visigenic or Intersolv) and the DB2 CLI/ODBC driver are installed:
  1. Run the ODBC Administration tool in one of two ways:
    - Double-click on the Control Panel icon from the Main Folder in WIN-OS/2 or 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 Drivers push button. The Drivers window opens.
  3. Verify that "IBM DB2 ODBC Driver" is shown in the list.
  installed then double-click on the Install ODBC Driver icon in the DB2 folder to install both the DB2 CLI/ODBC driver and the ODBC driver manager.
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 Visigenic ODBC Administration tool, which you can access from the ODBC folder or by running odbcadm.exe from the command line:
    1. Click on the Add push button from the Data Sources window. The Add Data Source Window opens.
    2. Double-click on the IBM DB2 ODBC Driver in the list.
    3. Select the DB2 database to add and click on OK.
Optional: Configure the DB2 CLI/ODBC driver using the CCA:
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 command in the Command Reference.
The DB2 CLI/ODBC driver is automatically installed when DB2 is installed, and therefore nothing needs to be done.

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.)

When using an ODBC application you must ensure that the Visigenic ODBC Driver Manager components are installed and that each user that will use ODBC has access to it.
- If DB2 installed the Driver Manager it would be located in the sqllib/odbclib subdirectory.
- If the Driver Manager was installed by another program, the file .odbc.ini (begins with a period) would be located in the root directory of the user ID that runs the ODBC application.

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 has a separate copy of this file.

subdirectory.

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 (db2.o). For example, 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

See the sample file in the sqllib/odbclib subdirectory for an example.

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 be copied into the end user's home directory and called .odbc.ini. 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 sample odbc.ini template shows:

line 2 (part of 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.
lines 4-6 (part of the [SAMPLE] stanza):
```
   [SAMPLE]
   Driver=/u/thisuser/sqllib/lib/db2.o
   Description=Sample DB2 ODBC Database
```
Indicates that the SAMPLE database is part of the DB2 instance located in the directory /u/thisuser.

line 11 (part of the [ODBC] stanza):

 
   InstallDir=/u/thisuser/sqllib/odbclib

Indicates that /u/thisuser/sqllib/odbclib should be treated as the location where ODBC is installed.

Note:

If your application specifically installed the ODBC components (such as the Driver Manager) elsewhere, you must update the line starting with InstallDir under the [ODBC] stanza to reflect this new location.

For example, if ODBC has been installed in /opt/odbc, the [ODBC] stanza would look like:

 
   [ODBC]
   Trace=0
   TraceFile=odbctrace.out
   InstallDir=/opt/odbc

See the sample file in the sqllib/odbclib subdirectory for an example. You can also 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".

Macintosh Client Access to DB2 using CLI/ODBC

Before DB2 CLI and ODBC applications can successfully access a DB2 database from a Macintosh 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 command 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 to step #4.)
1. An ODBC driver manager must be installed to handle the communications between the ODBC applications and the DB2 ODBC driver. DB2 does not supply a Macintosh ODBC driver manager, only the DB2 ODBC driver itself. If your ODBC application does not supply an ODBC driver manager then you can obtain the MacODBC driver manager directly from Apple.
2. Register the DB2 database with the ODBC driver manager list of data sources (contained in the ODBC Preferences file) as a data source using either of these methods:
  - Running ODBC Setup (in a 68K environment) or ODBC Setup PPC (in a PowerMacintosh environment) from the Control Panel
  - Editing the initialization (ODBC Preferences or ODBC Preferences PPC) file directly; refer to the next step in this process for more information.
Configuring ODBC Preferences or ODBC Preferences PPC: The ODBC initialization file is used to record information such as the available drivers and data sources. See the documentation for your driver manager for procedures on updating this file.
The MacODBC driver manager uses the ODBC Preferences file or the ODBC Preferences PPC file to record information about the available drivers and data sources.
It is also possible to modify these files manually. Do not change any of the existing entries in the files.
1. Use an ASCII editor to edit the ODBC Preferences file or the ODBC Preferences PPC file.
  68K Environment
  The following is a sample ODBC Preferences file:
```
     [ODBC Data Sources]
     GLOBALDB=IBM ODBC DB2 for Macintosh
     SAMPLE=IBM ODBC DB2 for Macintosh
 
     [GLOBALDB]
     Driver=appl:ODBC$DB2DriverFunctionSet
     Description=My GLOBAL database
 
     [SAMPLE]
     Driver=appl:ODBC$DB2DriverFunctionSet
     Description=My SAMPLE database
```
  PowerMacintosh Environment
  The following is a sample ODBC Preferences PPC:
```
     [ODBC Data Sources]
     SAMPLE=IBM ODBC DB2 for PPC
 
     [SAMPLE]
     Driver=DB2ODBCDriverPPC
     Description=My SAMPLE database
```
2. The [ODBC Data Source] 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 Source] section, there is a section that lists additional information about that data source. These are called the Data Source Specification sections.
  Under the [ODBC DATA SOURCE] entry, add the following line:
  
  68K Environment
  database_alias=IBM ODBC DB2 for Macintosh
  PowerMacintosh Environment
  database_alias=IBM ODBC DB2 for PPC
  
  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).
3. Add a new entry in the Data Source Specification section to associate the data source with the driver:
  68K Environment
```
[database_alias]
Driver=appl:ODBC$DB2DriverFunctionSet
```
  PowerMacintosh Environment
```
[database_alias]
Driver=DB2ODBCDriverPPC
```
  database_alias is the alias of the database cataloged in the database directory, and listed under the Data Source Specification section.
The DB2 CLI/ODBC driver can be configured further by 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 applications that access the database. For a complete description of all the keywords and their usage, refer to "Configuration Keywords".
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.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]