Linux informix odbc driver

NOTE : Most older tools use the ODBC driver however some of the new .NET tools coming out require the .NET driver. It’s best to install ODBC, .NET and the GLS components from the client install below. The rest is not necessary, although no harm comes from installing everything.

NOTE : If you install the driver via VPN you might have to reboot the system before it’s available for use. It’s best to install at the local console if you can.

NOTE : The IBM Informix SDK limits passwords used for authenticating against the ODBC service on Unity Connection to 18 characters. There is nothing Cisco can do on the server or client side to get around this. Here’s a link to an IBM document on this.

Support Support policy follows the tools that use these drivers.

IBM Informix Client SDK Install 4.10 TC9 for all 32 and 64 bit Windows Systems. Download Now (ZIP file — unpack and run install)

NOTE : IBM finally updated their install package in recent builds so installing this 32 bit version onto 64 bit operating systems should require only that you run the installer executable found in the zip as administrator — I leave the instructions below «just in case» folks have trouble. I’ve tested the 4.10 TC6 install on 64 bit versions of Windows 7, 8.1 and 10 with no issue.

If you are installing the 32 bit version onto a 64 bit OS you might need to prepend the SysWOW64 path temporarily to your %PATH% variable or the install may bark at you. To do this open a comand prompt — be sure to run «as administrator» , go to the folder where the Informix SDK install is at and type:

set PATH=C:\WINDOWS\SysWOW64\;%PATH%

and then launch the setup from the command line and it should go through. The same technique will work for Windows 8 installs, however you have to run the setup in Windows 7 compatibility mode because the Install Anywhere package IBM uses does not work with Windows 8. Here is a short training video showing me setting up the drivers on Windows 8 (5 minute Flash video).

You can find the latest SDK downloads from IBM using this link. All downloads are free but you do have to register on their site. Be sure to download the 32 bit version even if you are on a 64 bit OS — all tools are compiled for 32 bit operation.

If you are using Java and need to know the details of using JDBC via the Unity Connection database proxy, this document has the details for doing that form both Windows and Linux environments.

Description

The Informix drivers are used by off box tools that connect to Unity Connection 2.1(1) or later systems via the database proxy service.

The help files for each tool that uses the remote database proxy service to attach to Connection has instructions for how to configure the remote ODBC access from off box. You must create an account with remote administration access and enabled/activate the remote proxy service before you can use it — out of the box Connection has this disabled for security purposes.

Источник

Use Informix ODBC Driver with an ODBC Driver Manager

Overview

Open Database Connectivity (ODBC) is an interface to provide universal data access to relational and non-relational database servers. It is a software API based on the X/Open Call Level Interface (CLI) used to access data from a database server. It allows you to write database applications using a standard API that is common to all ODBC Drivers so that you don’t need to write your application in a specific way for a specific database. This helps reduce the development time and makes the solution more portable.

Typical components of an ODBC application are:

ODBC client: An application that uses the ODBC API to interact with a database server.
ODBC Driver: A library or set of libraries that contains the implementation of the ODBC API for a particular database server.
ODBC Driver Manager: A library or set of libraries that can be used by an ODBC client to interact with an ODBC Driver.

Informix ODBC Driver

The Informix ODBC Driver is included as part of the IBM Informix CSDK package. It follows the Microsoft® Open Database Connectivity (ODBC) version 3.5 standards. The Informix ODBC Driver supports several IBM Informix database servers such as IBM Informix Ultimate Edition and IBM Informix Choice Edition. See the Resources section for a link to the IBM Informix ODBC Driver release notes that show the database server compatibility.

On Microsoft Windows®, the ODBC Driver is automatically registered during the CSDK installation. The name of the Informix ODBC Driver is IBM Informix ODBC Driver.

On UNIX® and Linux® platforms, the ODBC Driver is normally referenced by the name of the library. The following list describes the different versions of the Informix ODBC Driver libraries included in the Informix CSDK package:

libifcli.a or libcli.a: Static version for a single non-threaded application
libifcli.so or iclis09b.so: Shared version for a single non-threaded application
libthcli.a: Static version for a multi-threaded application
libthcli.so or iclit09b.so: Shared version for a multi-threaded application
libifdrm.so or idmrs09a.so: Shared library for the driver manager Replacement (DMR)

The file extension varies from platform to platform.

As Figure 1 shows, ODBC applications can use these libraries in the following three configurations:

Link to the driver directly (A): The Informix ODBC Driver library is directly used by the ODBC application.
Link an ODBC Driver Manager (B): The ODBC Client application links to an ODBC Driver Manager, and the ODBC Driver Manager performs all the operations required to load and use the ODBC Driver acting as an intermediate layer between the application and the ODBC Driver.
Link to the Driver Manager Replacement and the driver (C) : In addition to the Informix ODBC Driver, the Informix CSDK package includes an Driver Manager Replacement (DRM) library (not available on Windows). This library provides most of the features of an ODBC Driver Manager and can be used by an ODBC Client if there is no ODBC Driver Manager installed on the machine.

Figure 1. ODBC client applications connectivity to Informix server

ODBC Driver Manager

The ODBC Driver Manager is a software layer that manages the communications between the user application and ODBC Driver. The ODBC Driver Manager performs jobs such as loading and unloading the appropriate ODBC Driver required by the application, as well as verification of the API parameters passed to the driver. When an application uses a driver manager, the API calls are handled by the ODBC Driver Manager, which performs basic error checking and then processes these calls, or passes them on to the underlying ODBC Driver if needed.

ODBC Driver Manager software is available in the market from third-party vendors for most UNIX and Linux platforms. On some operating systems such as Microsoft Windows or MAC OS X, the ODBC Driver Manager is a part of the operating system.

The typical configurations files used by the ODBC Driver Manager are:

odbcinst.ini: Contains information specific for the Driver Manager such as which drivers are available to the Driver Manager, and configuration parameters shared by all DSN.
odbc.ini: Contains information of ODBC Data Source Name or DSN. A DSN defines a connection with the database. The odbc.ini provides all the information required for the connection to the database and configuration parameters specific for the ODBC Driver.

The odbcinst.ini file

The odbcinst.ini file contains a list of installed ODBC drivers on the UNIX system, and specific attributes for each driver, such as the location of the shared library. This file is usually part of the ODBC Driver Manager, and its default location is the system /etc directory.

Included as part of the Informix CSDK package is the sample odbcinst.ini file shown in Listing 1 that is included in the installation directory under the /etc subdirectory (pointed by the INFORMIXDIR environment variable).

Listing 1. Sample odbcinst.ini file

The odbc.ini file

The odbc.ini file contains data source configuration information and is used by the ODBC Driver and the ODBC Driver Manager. The default location of the odbc.ini is the user home directory (/odbc.ini) but this location can be changed using the ODBCINI environment variable.

A sample odbc.ini file for the Informix ODBC Driver is provided with the CSDK package, as showing in Listing 2. It is located in the CSDK installation directory (usually pointed by the INFORMIXDIR environment variable) under the etc subdirectory.

Listing 2. Sample odbc.ini file

See the Resources section for a link to the IBM Informix ODBC Driver Guide where you can get a complete description of all the odbc.ini configuration parameters.

Using the ODBC Driver with a ODBC Driver Manager

Some of the parameters specified in the odbc.ini are critical when using the Informix ODBC Driver with a third-party ODBC Driver Manager. The most common problem when setting up the ODBC Driver is the Unicode parameter.

The Unicode parameter in the odbc.ini file is used to specify the Unicode flavor required by the driver. It varies between platforms and even between ODBC Driver managers. The IBM Informix ODBC Driver supports UCS-2, UCS-4 and UTF-8 encodings, as shown in Table 1.

Table 1. Default Unicode values for ODBC Driver Manager

DRIVER MANAGER	Unicode
UnixODBC	UCS-2
DataDirect	UTF-8
iODBC	UCS-4

The following section contains examples of a working ODBC.INI file for the most common platforms.

Linux

Linux distributions do not normally include an ODBC Driver Manager.

UnixODBC, an open source ODBC Driver Manager, is the most common driver manager used on Linux platforms. You can find information on how to build and configure the UnixODBC in the Resources section.

The default Unicode encoding used by the UnixODBC Driver Manager is UCS-2. This means that the Unicode parameter in the odbc.ini file should be UCS-2, otherwise applications using the Unicode ODBC API will fail to work properly.

The output in Listing 3 shows the contents of the odbc.ini file used with UnixODBC Driver Manager.

Listing 3. The odbc.ini file used with UnixODBC Driver Manager

There are two ODBC client tools included as part of the UnixODBC Driver Manager. These tools can be used to test if the configuration of the ODBC Driver is valid.

ISQL is a basic ANSI ODBC client, It uses the ANSI ODBC API so it’s not affected by the Unicode parameter. IUSQL is similar to ISQL but it uses the Unicode ODBC API.

Before attempting to use the ODBC Driver, make sure the environment is set up properly. The Informix ODBC Driver uses the Informix native libraries to communicate with the database server. Environment variables such as INFORMIXDIR and INFORMIXSQLHOSTS are used by these libraries to locate the configuration and resources files needed for connection. In the following sample, the Informix CSDK package was installed in «/usr3/csdk370» so the environment variable INFORMIXDIR points to that directory:

The name of the IDS server you are using (as defined in the odbc.ini) is irk1150, which means that you should have the communication parameters for that server in the Informix sqlhosts file.

The output shown in Listing 4 shows how to use the IUSQL tool using the odbc.ini.

Listing 4. Using IUSQL

Having an incorrect Unicode value in the odbc.ini will results in an error as shown in Listing 5.

Listing 5. Example using incorrect Unicode value

The error is caused due a mismatch between the Unicode encoding used by the application (UCS-2) and the encoding use by the driver manager (UCS-4).

Another ODBC Driver manager that can be used on a Linux OS is Progress DataDirect. On Linux platforms, the DataDirect ODBC Driver Manager uses UTF-8 as Unicode encoding. Included in the install package, there are a couple of ODBC client applications that you can use to test if the configuration of the ODBC Driver is valid.

The output in Listing 6 shows the demoodbc binary connecting to an Informix database server through the IBM Informix ODBC Driver.

Listing 6. Using demoodbc to connect to Informix

The Unicode parameter is the only change in the odbc.ini file between UnixODBC and DataDirect. As before, if the value of this parameter is not right, then the application will fail to connect without any meaningful error message, as shown in Listing 7.

Listing 7. Example using incorrect Unicode

The UnixODBC on an AIX machine uses UCS-2 for Unicode, as shown in Listing 8.

Listing 8. Unicode on AIX

Any other value will result in a failure during the connection.

When using the DataDirect ODBC Driver Manager on a AIX platform, UTF-8 is the Unicode value required in the odbc.ini. The output in Listing 9 shows the odbc.ini contents used on a AIX machine.

Listing 9. odbc.ini contents on AIX

Solaris

Just as on AIX, by default, the UnixODBC Driver Manager needs the Unicode parameter in the odbc.ini file to be set to UCS-2, as shown in Listing 10.

Listing 10. odbc.ini on Solaris

DataDirect ODBC Driver Manager on the other hand, uses UTF-8 for Unicode, as shown in Listing 11.

Listing 11. DataDirect Driver Manager on Solaris

The OpenSource iODBC Driver Manager uses 4 bytes for the Unicode code points (UCS-4). The sample in Listing 12 shows the output of the iodbctest iODBC sample tool connecting to an Informix database through the Informix ODBC Driver.

Listing 12. The iodbctest iODBC sample tool

Listing 13 shows UnixODBC on a HP/UX machine, with UCS-2 for Unicode.

Listing 13. UnixODBC on a HP/UX

Unicode=UTF-8 is needed for the DataDirect ODBC Driver Manager, as shown in Listing 14.

Listing 14. Unicode=UTF-8

ODBC sample code

The output in Listing 15 shows a simple ODBC client code which connects to a given DSN, and selects the first three rows from the systables catalog table.

Listing 15. ODBC client code

Using the –lodbc flag instructs the linker to add the ODBC library to the executable, as shown in Listing 16.

Listing 16. The -lodbc flag

Troubleshooting

Common connection problems

You can verify common connect level problems by checking the following issues when you try to set up a third-party driver manager.

The INFORMIXDIR environment variable might be set incorrectly.
The INFORMIXSQLHOSTS variable has the proper values for your Informix server.
Check and make sure the shared library path is setup correctly, for example: LD_LIBRARY_PATH, SHLIB_PATH).
Make sure you use the same 32 vs 64 binaries. You can use a tool like file/truss/strace to check which libraries files are been loaded, which could point to errors in the environment.
You can also debug the problems by using UnixODBC Driver Tracing , ODBC Driver Tracing , or SQLIDEBUG .

An example error is [ISQL]ERROR: Could not SQLDriverConnect .

UnixODBC Driver Manager tracing

UnixODBC Driver tracing provides you information about the names of the ODBC APIs that are getting called from the application, as well as their parameters. If a serious error occurs, (i.e., which could be a problem in unixODBC), you can find it in the source code file by looking at the line number from where it is generated. You can enable tracing by setting Trace parameter to Yes in ODBCINST.INI file as shown in Listing 17.

Listing 17. Trace parameter set to Yes

ODBC Driver tracing

ODBC Driver tracing provides you with information about the names of the ODBC APIs that are getting called from the application, as well as their parameters. When developing ODBC applications, common mistakes are often made while giving parameter values for API’s. You can generate a trace file of all ODBC calls to the ODBC Driver using the provided ODBC Trace facility. On a Windows system, you can enable ODBC Trace using the ODBC Data Source Administrator (odbcad32.exe). On UNIX machines you can enable tracing by setting Trace parameter to 1 in the ODBC.INI file, as shown in Listing 18.

Listing 18. Enable trace on UNIX machines

The output in Listing 19 shows some entries in a ODBC trace file.

Listing 19. Entries in ODBC trace file

SQLIDEBUG trace

ODBC uses SQLI protocol to communicate with the IDS server. The SQLIDEBUG trace contains all messages communicated between the client application and the IDS server. You can enable SQLIDEBUG trace at the Informix Client side by defining the environment variable as follows: SQLIDEBUG=2:/tmp/sqlitrace.

The Informix communication libraries will trace all the SQLI messages into a series of files, with one file per connection, and with the prefix path provided in the SQLIDEBUG variable.

The SQLIDEBUG contains SQLI packages communicated between the server and the client in a binary format. To obtain a readable output, you must use the sqliprint tool (called sqliprt.exe on Windows platforms). This tool is also included as part of the Informix CSDK package.

The output in Listing 20 shows how to set SQLIDEBUG, as well as how to decode the SQLI trace file generated.

Listing 20. Set SQLIDEBUG

The output in Listing 21 shows a section of the decoded trace.txt file.

Listing 21. Decoded trace.txt file

You can find additional information regarding ODBC and SQLIDEBUG traces in the Resources section.

Conclusion

This article has served as a quick guide to using Informix ODBC Driver with third-party driver managers such as UnixODBC and Data Direct. In addition, it provided troubleshooting techniques for common connection problems and tracing options.

Downloadable resources

Comments

Источник

Linux informix odbc driver