informix

http://www.synametrics.com/ifmxODBC.htm,

ftp://ftp.software.ibm.com/software/data/informix/downloads/

http://www.connectionstrings.com/

UsingCLI to Connect to Informix Databases Through ODBC

byJames Edmiston

 

Introduction
 

There are many third-partytools on the market for developing client applications and performing queryand reporting against Informix databases. In this article we will discusshow to enable third-party tools to access Informix databases through ODBCby installing and configuring Informix-CLI. Although CLI is the clientconnectivity tool for both a UNIX client and a Windows client, we willonly discuss setting up ODBC for Microsoft Windows environments.
 

Application development toolslike PowerBuilder and Visual Basic enable a developer to create clientapplications to access RDBMS databases. Products like BrioQuery give theuser a front-end tool to report on their data located on a database server.You may even have a need to use Microsoft Access database to link to anInformix database.
 
 
 

 

The connection from a Windowsclient to an Informix database server is achieved by utilizing the MicrosoftOpen Database Connectivity (ODBC) standard. ODBC is an API specificationthat enables access to multiple DBMS's using SQL. ODBC enforces a standardthat gives client applications interoperability across database platforms.
 

An ODBC enabled applicationgains access to a database through the use of an ODBC driver. Each DBMSrequires a different driver. The Informix ODBC drivers are bundled in asuite of client connectivity products.

Informix Products
 

Informix provides a suiteof connectivity products that are used to enable Microsoft Windows' clientsto access Informix servers. The two main products are Informix-CLI andInformix Connect. In the past, each of these products was shipped as aseparate installation CD. As of this year, these products are bundled togetheron one installation CD calledInformix Client Software Developers Kitfor Microsoft Windows Environments, or Informix Client SDK.
 

TheClient SDK contains much more than CLI and Connect. It includes programmerAPI's for C++, ESQL/C and Java. It also contains the latest Informix ODBCdrivers and two versions of the ODBC Driver Manager. The remainder of thisarticle will focus on installing Informix-CLI and the related components,and configuring the Windows client to use the latest ODBC driver to connectto an Informix database server.
 

There are several optionsto choose from when installing SDK. Early in the installation process,there is a choice of "Typical", "Compact" or "Custom" install. If you choose"Custom" you can see all of the components of the SDK. A "Typical" installwill provide the components necessary to establish an ODBC setup.
 

Following is a brief descriptionof some of the key products from the installation CD: 
 

Informix-CLI is theInformix implementation of the Microsoft ODBC standard. Informix-CLI isa call level interface, or connectivity tool that enables applicationsto dynamically access Informix database servers. CLI is composed of APIlibraries, a version of the driver manager and a version of the ODBC driver.CLI version 2.8 is delivered in Informix-Client SDK version 2.01 and containsan ODBC driver (version 2.8) for Informix. 
 

The Microsoft ODBC DriverManager (or ODBC DM) is used for configuring an ODBC data source (morediscussion about the ODBC data source later). The driver manager is alsoknown as the data source administrator and can be found in the Windowscontrol panel. The Driver Manager may already be installed on the clientby another product. There are 2 versions in the component install liston the Client SDK installation CD. Driver Manager version 2.5 and version3.0. Refer to the Informix Client Products Installation Guide for MicrosoftWindows Environments for more details on ODBC driver manager.
 

The Intersolv ODBC Driveris another component bundled in the Client SDK. The Informix ODBC driver,version 2.8, is automatically installed with Informix CLI version 2.8.The more recent driver, the Intersolv Data Direct ODBC driver version 3.01,is a separate component in the list of options in the SDK install. If youuse the version installed with CLI (version 2.8), it is recommended thatyou use version 2.5 of the ODBC DM. ODBC DM version 3.0 is required ifyou use the Intersolv Data Direct ODBC driver.
 

Informix Connect iscomposed of the runtime versions of the connectivity components. Connectcomes bundled with most of the Informix Windows client products. This productis useful because it can be deployed on client machines to enable completedapplications to connect to Informix databases in the Windows environment.
 

Once you have installed theproducts on the Windows client, you must configure the products using theproper server and database information.
 

Component Configuration
 

The first configuration stepis to create a server entry on the Windows client machine, and providevalues for the Informix variables. Informix includes a utility in the connectivityproducts called "Setnet32". Its function is to provide the Windows clientwith the information it needs to connect to the server by defining thedatabase server, host machine, network protocol and user account information.Setnet32 saves these entries in the Windows registry. A helpful featureof this utility is the ability to save the configuration to a file andconversely load it from a file. If there are several clients to configurewith the same server information, an initial machine can be configuredmanually, the configuration saved to a file and loaded on other clientmachines.
 

To use Setnet32 on the Windowsclient, click on Start, Programs, the Informix program group installedwith SDK, then Setnet32. The first screen that is displayed shows a listof environment variables. Some of the environment variables are requiredand some are optional. The majority of the environment variables on theclient are used for CLI function calls and GLS programming. We will focuson two variables used to establish client/server connectivity. 
 

 

TheEnvironment tab of Setnet32
 

In the environment screen,scroll down through the list toward the bottom and check the settings forINFORMIXDIR and INFORMIXSERVER. The INFORMIXDIR variable is automaticallyset with the directory value specified during the software setup and installation.The INFORMIXSERVER variable can be set manually, or it will automaticallybe set as a result of entering values in the Server Information tab.
 

In the Server Informationtab, define an entry for the server.
 

 

TheServer Information tab of Setnet32
 

Informix Server -the Informix database server/instance name.

HostName - the nameof the host machine where the database server resides.

Protocolname - thenetwork communication protocol.

Service Name - theservice name in the services file that corresponds to the listening port.

Options - this isused for defining groups.
 

After the information hasbeen entered, click on "Make Default Server" and the INFORMIXSERVER variablein the Environment tab will be set with the value from the from "InformixServer" field. Click on the "Apply" button to create the entry and clickon "OK" when finished. Define as many servers as needed.
 

If you are familiar withsetting up Informix Dynamic Server for UNIX, you may find these entriesare identical to the information entered in the sqlhosts file. This informationis saved in the Windows registry under a key value - SQLHOSTS. As a result,there is no need to have a sqlhosts fileon the Windows machine. Rather, the INFORMIXSQLHOSTS variable in the Environmenttab contains the name of the client machine name where the registry entriesreside.
 

ODBC Data Source
 

A Data Source consists ofan ODBC driver, a database and the server information. The server informationis the same information defined previously in Setnet32. A Data Source iscreated using the ODBC Driver Manager or ODBC Data Source Administratoras discussed previously. The ODBC Data Source Administrator is startedin Windows by clicking on Start, Settings, Control Panel, and ODBC. 
 

In the initial screen, clickon the "Add" button to create a new data source. A list is displayed showingall of the ODBC drivers installed on the Windows client. From this listyou can see the version numbers and actual file names of the drivers.
 

 

Selectingthe ODBC driver during data source creation
 

To reiterate what was previouslymentioned, the first Informix ODBC driver in the list above, version 2.80,is automatically installed with the CLI components. The second Informixdriver, version 3.01, is a separate component in the list of options inthe SDK install. There are different characteristics associated with eachdriver concerning the actual ODBC data source set up. The example belowillustrates creating an ODBC data source using the version 3.01 driver.
 

 

"General"tab: Entering the name, description, and database 

duringdata source creation
 

Inthe General tab, enter the data source name, description and database name.The key here is specifying the exact database name that you are connectingto. 
 

 

"Connection"tab: The host, service, server and protocol are filled
in from the "default server" specification in Setnet32
 

In the Connection tab, thedefault server information is automatically filled in from the server creationin the Setnet32 utility. If this particular data source needs to connectto a server other than the default, simply specify the values here. Oncethe information is entered in the "General" and "Connection" tabs, clickon "OK" and the ODBC data source is complete.
 

ServicesFile
 

Thefinal configuration item is adding an entry to the services file on theclient machine. This is identical to the /etc/services file in the UNIXenvironment. The entry consists of the service name, listening port numberand protocol. On the client, the service name must match the service namespecified from the server entry created in Setnet32. The listening portnumber must match the number specified on for the server. The protocolfor a tcp/ip network will be "tcp".
 

The services file for theWindows client is located in the windows home directory. However, the specificlocation depends on the operating system. For Windows 95, the file is c:\windows\services.For Windows NT, c:\winnt\system32\drivers\etc\services. Use an ASCII editor(Notepad) to open the file and insert a line to identify the service. Theformat of the service entry is documented at the top of the file.
 

An example of a servicesfile entry is:
 

informix1 1526/tcp #Informixserver
 

Make sure there is a carriagereturn at the end of the line.
 

Testingthe connection
 

Afterthe server is defined to the client and the services file entry has beencreated, the client to server connection needs to be tested. The ILoginprogram is provided as part of the product install to help verify thatall of the information is correct for the client to connect to the server.Execute the utility on the Windows client by clicking on the Start, Programs,the Informix program group installed with SDK, then "ILogin Demo". Entera valid user-id and password, and ILogin will automatically attempt toconnect to the default server and run a query against the customer tableof the stores7 database. This is a useful tool for trouble shooting theclient to server connection.
 

The database connection definedin the data source needs to be verified using an ODBC-enabled application.The application references the data source by the Data Source Name (DSN).Refer to the product documentation for referencing a DSN. Your connectivitysetup is complete once you have established the connection from the applicationsoftware to the database.
 

Summary
 

The first several times Iattempted to configure a client with Setnet32 and ODBC, I would experiencea different error. However, by reading documentation, consulting with others,and through trial and error, I was able to get to the point where I couldaccomplish the process without any problems. I've included some of the"gotchas" that I experienced.
 

2.Informix error -908. Verify the listening port number defined on the servermatches the client.

4.Informix error -931. Verify the service file entry matches the servicename in the Setnet32 Server Information tab.

6.Informix errors -951 and -956. These errors indicate the user and/or thehost machine does not trust the client machine. The client machine namemay need to be added to the /etc/hosts.equiv and/or the .rhost file inthe user's UNIX home directory on the server machine.

8.You may experience this error during the ODBC setup in the ODBC Data SourceAdministrator:
 

 

The solution to this problemis to explicitly set the INFORMIXDIR and PATH environment variables inthe Environment tab under System Properties in the Control Panel. Createthe INFORMIXDIR variable and supply the value of the directory where InformixCLI is installed on the client. Add the Informix home directory path plusthe "\bin" to the beginning of the PATH variable. Set these in the SystemVariables section.
 

Setting up the client serverconnectivity may involve more in depth troubleshooting and analysis. Beprepared to allow time for these activities.
 

Documentation references.
 

Itis very important to reference the documentation materials that come withthe product. In addition to reading the release notes, there are severalmanuals that address the steps covered in this article.
 

2. InformixClient Products Installation Guide for Microsoft Windows Environments,Version 2.0. This describes how to install and configure the clientproducts addressed in this article.

4. INFORMIX-CLIProgrammer's Manual, Version 2.8. This manual is helpful with configurationas well as the deeper workings of CLI.

6. INTERSOLVDataDirect ODBC Driver User Guide for Informix Database Servers, Version3.01. This is an excellent resource for referencing the items coveredin this article pertaining to the ODBC driver and Driver Manager.
 

JamesEdmiston is President of Quest Information Systems, Inc. and an InformixCertified Professional. He serves as Treasurer and Membership Directorof the Washington Area Informix Users Group. Visit his company web siteat  www.questisinc.com oremail him at James@QuestISInc.com .