Connecting to Informix DB from Linux - client SDK is installed

Question

I am working on configuring the ODBC.ini file to connect to a remote DB from a Debian server. My background is mostly PHP and MySQL, so setting up an ODBC is new to me and would appreciate any help. My understanding is that the every DSN needs to be configured in the ODBC.ini file, and I already have this information.

;---------------------------------------------------------------------------
; IBM INFORMIX ODBC Sample File
;
; File:     odbc.ini
;
;---------------------------------------------------------------------------
[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
;
; UNICODE connection Section
;
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
;
; Trace file Section
;
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/extra/informix
TRACEDLL=idmrs09a.so

However, I would like to know if there's a utility or method of testing connectivity after the configuration is done on the client side. There seems to be quite a bit of information on how to do it on Windows, much less of it on Linux. Any help is appreciated.

Solution

Before, just a comment. If you want to access Informix with PHP, exists a PDO for that... and you can found tips how install/configure it here, here or here PHP and Informix on Debian - how to install/configure the PDO

I use the "isql" command where I get it with unixODBC rpm (I use opensuse distro).
The official site : http://www.unixodbc.org/
There have some explanations how use it too.
The Informix manual you can found updated information : :http://pic.dhe.ibm.com/infocenter/informix/v121/topic/com.ibm.odbc.doc/ids_odbc_057.htm

To setup the Informix access I just:

1. set the informix variables (you need the csdk installed)

export INFORMIXDIR=/opt/informix  
export INFORMIXSERVER=ifxtest
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts
export PATH=$INFORMIXDIR/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$INFORMIXDIR/lib:$INFORMIXDIR/lib/cli:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/tools

2. create the odbc.ini with the desire configuration.
   set the DNS name (is the Infdrv1 at the example)
   define the driver to path where is installed your csdk define the database, servername, login/passwd (if desire)
3. export the ODBCINI variable pointing to your odbc.ini file , or create the $HOME/.odbc.ini for specific user.
4. try connect to the database executing : isql <dns_name> <user> <passwd>

After all configuration above this is my test:

ifxdba1  cinacio@jdi:~
$ isql dba1 informix xxxxxx
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select first 1 * from systables;
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+------------+------------+--------+-------+---------+-----------------------+-----------+------------+--------+----------+-----------------------+------------+------------+-------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+--------------------------+------------+----------------+-----------+----------+
| tabname                                                                                                                         | owner                           | partnum    | tabid      | rowsize| ncols | nindexes| nrows                 | created   | version    | tabtype| locklevel| npused                | fextsize   | nextsize   | flags | site                                                                                                                            | dbname                                                                                                                          | type_xid   | am_id      | pagesize   | ustlowts                 | secpolicyid| protgranularity| statchange| statlevel|
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+------------+------------+--------+-------+---------+-----------------------+-----------+------------+--------+----------+-----------------------+------------+------------+-------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+--------------------------+------------+----------------+-----------+----------+
| systables                                                                                                                       | informix                        | 1048580    | 1          | 500    | 26    | 2       | 262                   | 2013-06-05| 65539      | T      | R        | 21                    | 16         | 16         | 0     |                                                                                                                                 |                                                                                                                                 | 0          | 0          | 2048       | 2013-06-05 21:28:50.00000| 0          |                |           |          |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+------------+------------+--------+-------+---------+-----------------------+-----------+------------+--------+----------+-----------------------+------------+------------+-------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+--------------------------+------------+----------------+-----------+----------+
SQLRowCount returns -1
1 rows fetched
SQL>

Information of UnixODBC installed at my opensuse...

cinacio@jdi:~
$ rpm -qi  unixODBC
Name        : unixODBC
Version     : 2.2.12
Release     : 219.1.1
Architecture: x86_64
Install Date: Tue 15 Oct 2013 08:18:36 AM BRT
Group       : Productivity/Databases/Tools
Size        : 1700974
License     : GPL-2.0+ ; LGPL-2.1+
Signature   : RSA/SHA256, Sat 26 Jan 2013 01:57:06 PM BRST, Key ID b88b2fd43dbdc284
Source RPM  : unixODBC-2.2.12-219.1.1.src.rpm
Build Date  : Sat 26 Jan 2013 01:56:38 PM BRST
Build Host  : build22
Relocations : (not relocatable)
Packager    : http://bugs.opensuse.org
Vendor      : openSUSE
URL         : http://www.unixodbc.org/
Summary     : ODBC driver manager with some drivers included
Description :
UnixODBC aims to provide a complete ODBC solution for the Linux
platform. Further drivers can be found at http://www.unixodbc.org/.



Authors:
--------
    Peter Harvey <[email protected]>
    Nick Gorham <[email protected]>
Distribution: openSUSE 12.3

Comments

[Kimomaru]

Thank you for replying, ceinmart, I appreciate it. It sounds to me that I need two things in oder to communicate with Informix server; the Informix drivers from IBM (found in client SDK) and unixodbc (which has the isql program). Am I correct?

[ceinmart]

Hi Kimomaru, exactly! This both utility should be enought.

[Kimomaru]

ceinmart - I've installed both of these several times and I'm getting an error whenever I type "isql -v <DSN_NAME_AS_I'VE-SET_IT_IN_/ETC/ODBC.INI> [01000][unixODBC][Driver Manager]Can't open lib '/opt/informix/lib/cli/iclit09b.so' : file not found . I've even changed the permissions on the .so file to be completely accessible, no luck. It's definitely there - I've checked. I've also verified that the OS and the client SDK are both 32 bit. Any ideas?

[ceinmart]

your informix environment have been set? INFORMIXDIR , INFORMIXSERVER, LD_LIBRARY_PATH, PATH?

[Kimomaru]

ceinmart, where are these environmental variables set? I've read in every post on this issue that this is the first place I should look, but the only configuration I've done after installing the client SDK is in either /opt/IBM/informix/etc/odbc.ini (the default path) and in the case of unixodbc /etc/odbc.ini. It's quite vexing. I can't find a simple step by step that includes setting these variables. Am I misunderstanding? I would be very grateful for any help you can give to clarify.

[ceinmart]

This environment need to be manually set, this is informed at the finish of the installation of CSDK or the database. Anyway, I include an example which you can use as base to your environment. (this is a common produre for databases like Informix, Oracle, DB2,...)

[Kimomaru]

Thank you, ceinmart. I appreciate your help and will go through the config you have posted. I have one more question - I'm not accustomed to needing a dbc since I've worked with PHP/MySQL. PHP alse has native extensions for Informix, but why can't it communicate with the Informix server in the same manner (without a dbc)?

[ceinmart]

I'm not sure if I understand your answer. What do you means with "dbc"? odbc? Yes Informix have a PDO to PHP, you don't need the ODBC configuration to access it. You just need to have the CSDK installed where the apache is running , compiling and installing the Informix PDO into PHP and apache configuration... at the PHP offical page have an explanation how to do...

[Kimomaru]

I will look up the php pages for more information. The export syntax you posted seemed to have worked, I can at least start of dbaccess and isql. ISQL will start, but it's generating errors because the sqlhosts file has not been configured (yet). The syntax for sqlhosts is confusing and am hoping you can help. In nettype, there's a part for "database product", which is confusing. Is there a document or tutorial that explains this better? The example syntax says "demo_on onipcshm on_hostname on_servername". Hostname and servername should be the same, no?

[ceinmart]

At the sqlhosts you will configure the network interface used by the database. there you should inform <alias/instancename> <protocol> <host> <port>. Where alias/instancename should be the same of INFORMIXSERVER/DBSERVERNAME and DBSERVERALIASES at the onconfig (here you define each db alias for each network configuration). protocol for network just set to onsoctcp , then the hostname and port to listen. then restart the database (onmode -ky / oninit). Just make sure the mainly instance name was set correctly into the sqlhosts.

[ceinmart]

For more reference , try this tutorial and this official documentation

[Kimomaru]

Thank you for your help, ceinmart. As I am working on connecting to an appliance, I am working through a "[S1000][unixODBC][Informix][Informix ODBC Driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES." error. Definitely progress from the error I was getting before, but in order to fix this I will probably need to reach out to the vendor to help determine what the DBSERVERNAME is (I've tried reviewing in documentation and taking some educated guesses). Hopefully with a little more help from the vendor on the final piece I can finish this. Thanks again.

[Kimomaru]

Hi ceinmart - I got isql working just now, there is a lot of undocumented nuances to getting the client SDK to build connections to the appliance server I'm working on and much conflicting advice. However, thanks to your help and some other sources I can now connect to the Informix server and begin the real work. I just wanted to thank you for your help, you were extremely helpful and made a huge difference. I guess now I have to research how to pass select statements, but now that my connection is working I'll get there. Thanks again.

[Kimomaru]

ceinmart - quick question; have you installed the php Informix PDO driver before? Apparently, this is a separate download and instructions are sparse for some reason.

[ceinmart]

Hi Kimomaru, yes the last time I was installed it was two weeks ago and yes, it is a separated download. AFAIK the way to install it flows the same way used to install any other PDO, where you need to phpize the source, execute the configure, compile with make and install with make install and execute few steps to active it into the apache. Of course, some particular adjusts always be necessary for each vendor. I see this kindly as natural of open sources systems.

[Kimomaru]

Hi ceinmart - okay, I was able to download, compile, and install successfully. Trying to look up how to set it up in php.ini. Any thoughts?

[Kimomaru]

Nevermind, got it working by modifying the 10-pdo.ini file with extension=pdo_informix.so. It shows up now, but getting a new error "SQLSTATE=HY000, SQLDriverConnect: -11041 [Informix][Informix ODBC Driver]Unspecified System Error = -11041." But, this looks like PDO is actually working.

[ceinmart]

I think you aren't using PDO , since they showing "Informix ODBC" message... PDO and ODBC are two things very different and they don't depend one from the other.

[Kimomaru]

Hey ceinmart - the error seems to be pretty much exactly this www-01.ibm.com/support/docview.wss?uid=swg21454845 . The subject is new to me, I'm used to making mysqli connections to a mysql server. Installing ODBC has the benefit of allowing me to connect to the remote host and allowing me to run commands, but I prefer how mysql does it - they have their own client, it builds connections easily and that's it. The IBM link seems to say that the issue resides in permissions problem with the root account used to run apache. Any ideas?

[Kimomaru]

ceinmart - question; PDO and ODBC may be two different things, but in this PHP syntax it's leveraging PDO and DSN to build a connection. DSN is ODBC, no? PDO seems to be interacting with ODBC here. try { $dbh = new PDO("informix:DSN=InformixDB", "username", "password"); } catch (PDOException $e) { echo $e->getMessage(); }

[Kimomaru]

Hey ceinmart - I think I understand now. Setting up a DSN is needed for both ODBC and PDO (that's why a PDO connection can be set up by referencing a DSN in an ODBC.ini or as a string, like this;$db = new PDO("informix:host=host.domain.com; service=9800; database=common_db; server=ids_server; protocol=onsoctcp; EnableScrollableCursors=1", "testuser", "tespass"); Something must be wrong with my string.

[ceinmart]

Just make sure you have set the Informix variables (the same way of my answer) at the moment you start the apache. I don't know how works for Debian but for OpenSuse 12.3 I configure the INFORMIXDIR and LD_LIBRARY_PATH into the /etc/sysconfig/apache2 then I restart the apache.

[John]

There is a command line to export sql query to excel file ? Because the output in the terminal is unreadable