IBM Support

How do you test ODBC connectivity on a UNIX® operating system?

Question & Answer


Question

How to test ODBC connectivity on a UNIX® operating system

Answer

INTRODUCTION

To check ODBC connectivity on a UNIX® operating system compile and run a demo program.

BEFORE YOU BEGIN

  • Confirm IBM Informix® Client SDK or IBM Informix® Connect is installed on your UNIX system.
  • Confirm that you have configured your database server and it is online.
  • Know the database name and the database servername that you wish to connect to.
  • Find the odbc.ini sample file located in the $INFORMIXDIR/etc directory where the Client SDK or IBM Informix Connect is installed.
  • Change directory to $INFORMIXDIR/demo/cli. Copy the entire contents of the cli directory to a test directory. All demo programs and header files are called in the makefile file

      Tip: Copy the entire contents of the cli directory to the test directory .

      Note: The dbcreate demo program is used in the examples later in this article.

  • Add the full path of the cli directory, $INFORMIXDIR/lib/cli to your shared library path environment variable.

    • Example:

      For the Solaris Sh shell add $INFORMIXDIR/lib/cli to the LD_LIBRARY_PATH environment variable using this syntax on the command line:

      export LD_LIBRARY_PATH=$INFORMIXDIR/lib/cli:$LD_LIBRARY_PATH

      Tip: See the Related Information section for an article about what shared library path environment variables do and how to set them on specific operating systems.

  • Confirm there is a c++ or cc compiler installed on your operating system that is compatible with the IBM Informix product installed. Check c++/cc compatibility in the ODBC Machine Notes located in the $INFORMIXDIR/release/en_us/0333 directory.

      Note: For SDK 2.90.x the name of the ODBC machine notes file is INFODBC_2.9.

        For SDK 2.81.x the name of the ODBC machine notes file is INFODBC_3.8.

        For SDK 2.70.x the name is INFODBC_3.3.

  • Set your HOME environment variable to the home directory of the user that will test the connection.

    • Example:

      Using the Sh shell to set HOME to user informix's home directory using Sh shell use this syntax on the command line.


        export HOME=/usr/informix


      Tip: See the Related information section for an article about setting and unsetting environment variables.

  • On IDS 9.x and 10.x be sure that a smart blob sbspace has been created and is available for use before running test program.

STEPS

1. Copy the odbc.ini file into the user's HOME directory.


    IMPORTANT: Be sure that the name of the copied file begins with a dot.


      cp $INFORMIXDIR/etc/odbc.ini  $HOME/.odbc.ini

2. Edit the .odbc.ini file.


    a. In the ODBC Data Sources section put in a Datasource name and description.

    b. Change the path for Driver so it points to your $INFORMIXDIR/lib/cli/iclit09b.so library. Some Operating Systems (OS) use .sl extensions for libraries. Be sure to use the appropriate extension for your OS.

    c. Change Database to the name of the database you want to connect to.

    d. Change the description to match the description specified in the ODBC Datasource section.

    e. ( Optional ) You can specify a user logon and password with LogonID= and pwd=.

    f. Change Servername to the name of the server you want to connect to.

    Example:

    This is a sample entry taken from the Client SDK version 2.90.x odbc.ini file:



      [ODBC Data Sources]
      Infdrv1=IBM INFORMIX ODBC DRIVER
      Infdrv2=IBM INFORMIX ODBC DRIVER


      ;
      ; Define ODBC Database Driver's Below - Driver Configuration Section
      ;
      [Infdrv1]
      Driver=/extra/informix/lib/cli/iclit09b.so
      Description=IBM INFORMIX ODBC DRIVER
      Database=stores_demo
      LogonID=odbc
      pwd=odbc
      Servername=ids_server1


      [Infdrv2]
      Driver=/extra/informix/lib/cli/iclis09b.so
      Description=IBM INFORMIX ODBC DRIVER
      Database=stores_demo
      LogonID=odbc
      pwd=odbc
      Servername=ids_server2
      CursorBehavior=0
      CLIENT_LOCALE=en_us.8859-1
      DB_LOCALE=en_us.8859-1
      TRANSLATIONDLL=/extra/informix/lib/esql/igo4a304.so


3. In your test directory, run make at the command line. This will use the makefile file to compile all the test programs into executable programs.

4. Run your selected executable program using the ODBC Data Source:


    Example:

    For the dbcreate demo program run this on the command line:


      ./dbcreate Infdrv1


    If successful the dbcreate program will create a database called odbc_demodb.

    Here is an example of the creation process:


    ./dbcreate Infdrv1

    Using specified DSN : Infdrv1
    STEP 1 done...connected to database server
    STEP 2 done...Database Version -- 09.50.0000 FC3
    Creating UDO database
    Executing stmt 1 of 9
    Executing stmt 2 of 9
    Executing stmt 3 of 9
    Executing stmt 4 of 9
    Executing stmt 5 of 9
    Executing stmt 6 of 9
    Executing stmt 7 of 9
    Executing stmt 8 of 9
    Executing stmt 9 of 9
    STEP 3 done...Sample Database Created
    Populating table 'customer'
    Inserting row # 1
    Inserting row # 2
    Inserting row # 3
    Inserting row # 4
    Inserting row # 5
    Inserting row # 6
    Inserting row # 7
    Inserting row # 8
    Inserting row # 9
    STEP 4 done...Data inserted into the 'customer' table
    Populating table 'item'
    Inserting row # 1
    Inserting row # 2
    Inserting row # 3
    Inserting row # 4
    STEP 5 done...Data inserted into the 'item' table
    Populating table 'orders'
    Inserting row # 1
    Inserting row # 2
    Inserting row # 3
    Inserting row # 4
    Inserting row # 5
    Inserting row # 6
    Inserting row # 7
    Inserting row # 8
    Inserting row # 9
    STEP 6 done...Data inserted into the 'orders' table


    Hit <Enter> to terminate the program...

COMMON PROBLEMS

Symptom: When trying to compile a demo program you get error:


    ld.so.1: ./dbcreate: fatal: libifdmr.so: open failed: No such file or directory

    Killed.


Resolution: Make sure that the path to your CLI libraries is set correctly in the library path environment variable.

Symptom: On IDS versions 9.x and 10.x you get error:


    ERROR: -9810:  HY000 : [Informix][Informix ODBC Driver][Informix]Smart-large-object error. Error in Step 5 -- SQLExecDirect failed    Hit <Enter> to terminate the program...


Resolution: Make sure that you have a sbspace created and available for use in your database server.

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF025","label":"Platform Independent"},{"code":"PF008","label":"DYNIX\/ptx"},{"code":"PF010","label":"HP-UX"},{"code":"PF015","label":"IRIX"},{"code":"PF016","label":"Linux"},{"code":"PF026","label":"Reliant UNIX"},{"code":"PF027","label":"Solaris"}],"Version":"10.0;11.1;7.3;9.4;11.5","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21176492