Informix Connection Using unixODBC
A user on Red Hat with Client SDK 3.00 couldn't connect to a remote Informix instance via unixODBC; isql just hung. The cause was a port mismatch: the client's sqlhosts service alias resolved to 8201/tcp while the server's instance listened on 7101/tcp (and odbc.ini specified TcpPort=7101). After changing the client's /etc/services entry to 7101, isql connected and queries worked. Follow-ups noted a sqlexec service entry is only needed for that SE/ipcpip entry, and that the sqlhosts server name must match the engine's DBSERVERNAME/alias.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
I apologize in advance if this is not the place for unixODBC/Informix
questions. I'm attempting to use unixODBC on a Redhat 4.0 host `VMRH40BO'
with IBM's clientsdk 3.00 to connect to an Informix database located
on my local subnet on host `maindb'. I'm confused by how the sqlhosts
and obdc.ini files should look. Currently my VMRH40BO:/etc/sqlhosts
file contains:
maindb onsoctcp maindb port_aliasdemo_se seipcpip se_hostname sqlexec
where port_alias is set in VMRH40B0:/etc/services as follows:
port_alias 8201/tcp
My VMRH40BO:/etc/odbc.ini file contains:
[ODBC Data Sources]
CRSQLServerWP=DataDirect 5.1 SQLServer Wire Protocol Driver
CRSybaseWP=DataDirect 5.1 Sybase Wire Protocol Driver
CRText=DataDirect 5.1 Text Driver
InformixODBC=HP G6 running Informix Database
[Informix]
Driver=/opt/informix/lib/cli/iclit09b.so
Description=HP G6 running Informix Database
IpAddress=192.168.1.100
Server=main_shm
TcpPort=7101
Database=main_live
LogonId=crystal
Password=password
Now, given the database configuration on maindb given below....
maindb's database listing (from dbaccess):
SELECT DATABASE >>
Select a database with the Arrow Keys, or enter a name, then press Return.
------------------------------------------------ Press CTRL-W for Help --------
main_live@main_ecf
main_test@main_ecf
main_train@main_ecf
sysadmin@main_ecf
sysmaster@main_ecf
sysuser@main_ecf
sysutils@main_ecf
maindb:/etc/services file:
# End Media Manager services #
main_ecf 7101/tcp # Informix instance main_ecf
...what should my odbc.ini and sqlhosts files look like on VMRH4BO?
Also, I've been attempting to use isql to see if I could connect from
VMRH4BO to maindb, invoking it as follows:
isql -v Informix crystal password
When the above is run, it leaves me infinitely waiting for it to
return with no output to the screen. Running strace on it yields:
[mtaylor@VMRH4BO ~]$ strace -p 24656
send(3, "sqAbYBPQAAsqlexec crystal -pcmxt"..., 442, 0
Is there a better way to test the connection to the database?
Thanks in advance,
Marshall
Never played with unixodbc, but first guess:
port_alias 8201/tcp does not match TcpPort=7101
And: what port number is sqlexec in /etc/services ?
Joerg Volz
------------------------------------------------------------------------
-----
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
MARSHALL HAHN-TAYLOR
Sent: Tuesday, January 26, 2010 9:57 PM
To: ids@iiug.org
Subject: Informix Connection Using unixODBC [18775]
Hello,
I apologize in advance if this is not the place for unixODBC/Informix
questions. I'm attempting to use unixODBC on a Redhat 4.0 host
`VMRH40BO'
with IBM's clientsdk 3.00 to connect to an Informix database located
on my local subnet on host `maindb'. I'm confused by how the sqlhosts
and obdc.ini files should look. Currently my VMRH40BO:/etc/sqlhosts
file contains:
maindb onsoctcp maindb port_aliasdemo_se seipcpip se_hostname sqlexec
where port_alias is set in VMRH40B0:/etc/services as follows:
port_alias 8201/tcp
My VMRH40BO:/etc/odbc.ini file contains:
[ODBC Data Sources]
CRSQLServerWP=DataDirect 5.1 SQLServer Wire Protocol Driver
CRSybaseWP=DataDirect 5.1 Sybase Wire Protocol Driver
CRText=DataDirect 5.1 Text Driver
InformixODBC=HP G6 running Informix Database
[Informix]
Driver=/opt/informix/lib/cli/iclit09b.so
Description=HP G6 running Informix Database
IpAddress=192.168.1.100
Server=main_shm
TcpPort=7101
Database=main_live
LogonId=crystal
Password=password
Now, given the database configuration on maindb given below....
maindb's database listing (from dbaccess):
SELECT DATABASE >>
Select a database with the Arrow Keys, or enter a name, then press
Return.
------------------------------------------------ Press CTRL-W for Help
--------
main_live@main_ecf
main_test@main_ecf
main_train@main_ecf
sysadmin@main_ecf
sysmaster@main_ecf
sysuser@main_ecf
sysutils@main_ecf
maindb:/etc/services file:
# End Media Manager services #
main_ecf 7101/tcp # Informix instance main_ecf
....what should my odbc.ini and sqlhosts files look like on VMRH4BO?
Also, I've been attempting to use isql to see if I could connect from
VMRH4BO to maindb, invoking it as follows:
isql -v Informix crystal password
When the above is run, it leaves me infinitely waiting for it to
return with no output to the screen. Running strace on it yields:
[mtaylor@VMRH4BO ~]$ strace -p 24656
send(3, "sqAbYBPQAAsqlexec crystal -pcmxt"..., 442, 0
Is there a better way to test the connection to the database?
Thanks in advance,
Marshall
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
IT Handel und Beratung Jorg Volz
Bernhard-Fruh-Str. 7
77855 Achern
GERMANY
Tel: +49 (0)7841-681651
Fax: +49 (0)7841-681654
Mobil: +49 (0)170-2989757
VAT-ID: DE201383541
http://www.it-volz.de
> Never played with unixodbc, but first guess: > port_alias 8201/tcp does not match TcpPort=7101 > And: what port number is sqlexec in /etc/services ? > Joerg Volz There is no sqlexec port in VMRH4BO:/etc/services. Should there be? Upon changing the port address from 8201 to 7101 I now get: isql -v Informix crystal password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select count(*) from systables; +------------------+ | | +------------------+ | 279 | +------------------+ SQLRowCount returns -1 1 rows fetched SQL> It looks like I connected =) Tausend Dank! -mt
>There is no sqlexec port in VMRH4BO:/etc/services. Should there be? As long as you don´t need to connect via sqlexec: > demo_se seipcpip se_hostname sqlexec you don´t need it. But: That's one of the usual trap doors to check :-) Mit freundlichen Gruessen Joerg Volz ----------------------------------------------------------------------------- -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARSHALL HAHN-TAYLOR Sent: Tuesday, January 26, 2010 10:58 PM To: ids@iiug.org Subject: Re: RE: Informix Connection Using unixODBC [18777] > Never played with unixodbc, but first guess: > port_alias 8201/tcp does not match TcpPort=7101 > And: what port number is sqlexec in /etc/services ? > Joerg Volz There is no sqlexec port in VMRH4BO:/etc/services. Should there be? Upon changing the port address from 8201 to 7101 I now get: isql -v Informix crystal password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select count(*) from systables; +------------------+ | | +------------------+ | 279 | +------------------+ SQLRowCount returns -1 1 rows fetched SQL> It looks like I connected =) Tausend Dank! -mt ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. IT Handel und Beratung Jörg Volz Bernhard-Früh-Str. 7 77855 Achern GERMANY Tel: +49 (0)7841-681651 Fax: +49 (0)7841-681654 Mobil: +49 (0)170-2989757 VAT-ID: DE201383541 http://www.it-volz.de
I don't see a definition for server main_shm in your sqlhosts file, does the
DBSERVERNAME or DBSERVERALIAS contain that servername? If so, there should
be an entry in sqlhosts for it. The ODBC drivers don't actually use
sqlhosts, but the engine does. If there isn't an entry for that connection
name the server will not listen for it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Jan 26, 2010 at 3:57 PM, MARSHALL HAHN-TAYLOR <
marshall_hahn-taylor@canb.uscourts.gov> wrote:
> Hello,
>
> I apologize in advance if this is not the place for unixODBC/Informix
> questions. I'm attempting to use unixODBC on a Redhat 4.0 host `VMRH40BO'
> with IBM's clientsdk 3.00 to connect to an Informix database located
> on my local subnet on host `maindb'. I'm confused by how the sqlhosts
> and obdc.ini files should look. Currently my VMRH40BO:/etc/sqlhosts
> file contains:
>
> maindb onsoctcp maindb port_alias> demo_se seipcpip se_hostname sqlexec
>
> where port_alias is set in VMRH40B0:/etc/services as follows:
>
> port_alias 8201/tcp
>
> My VMRH40BO:/etc/odbc.ini file contains:
>
> [ODBC Data Sources]
> CRSQLServerWP=DataDirect 5.1 SQLServer Wire Protocol Driver
> CRSybaseWP=DataDirect 5.1 Sybase Wire Protocol Driver
> CRText=DataDirect 5.1 Text Driver
> InformixODBC=HP G6 running Informix Database
>
> [Informix]
> Driver=/opt/informix/lib/cli/iclit09b.so
> Description=HP G6 running Informix Database
> IpAddress=192.168.1.100
> Server=main_shm
> TcpPort=7101
> Database=main_live
> LogonId=crystal
> Password=password
>
> Now, given the database configuration on maindb given below....
>
> maindb's database listing (from dbaccess):
>
> SELECT DATABASE >>
> Select a database with the Arrow Keys, or enter a name, then press Return.
>
> ------------------------------------------------ Press CTRL-W for Help
> --------
>
> main_live@main_ecf
>
> main_test@main_ecf
>
> main_train@main_ecf
>
> sysadmin@main_ecf
>
> sysmaster@main_ecf
>
> sysuser@main_ecf
>
> sysutils@main_ecf
>
> maindb:/etc/services file:
>
> # End Media Manager services #
> main_ecf 7101/tcp # Informix instance main_ecf
>
> ....what should my odbc.ini and sqlhosts files look like on VMRH4BO?
>
> Also, I've been attempting to use isql to see if I could connect from
> VMRH4BO to maindb, invoking it as follows:
>
> isql -v Informix crystal password
>
> When the above is run, it leaves me infinitely waiting for it to
> return with no output to the screen. Running strace on it yields:
>
> [mtaylor@VMRH4BO ~]$ strace -p 24656
> send(3, "sqAbYBPQAAsqlexec crystal -pcmxt"..., 442, 0
>
> Is there a better way to test the connection to the database?
>
> Thanks in advance,
>
> Marshall
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151743f702f7b70b047e187ff3
I should have double checked what I put for my sqlhosts file.
It should have been:
main_shm onsoctcp maindb port_aliasdemo_se seipcpip se_hostname sqlexec
Does that jive with what you're saying about DBSERVERNAME or
DBSERVERALIAS? When I googled those two variables, I get
references to them in regards to the onconfig file (from
publib.boulder.ibm.com). There actually isn't any onconfig
config file on this system.
> I don't see a definition for server main_shm in your sqlhosts file, does the
> DBSERVERNAME or DBSERVERALIAS contain that servername? If so, there should
> be an entry in sqlhosts for it. The ODBC drivers don't actually use
> sqlhosts, but the engine does. If there isn't an entry for that connection
> name the server will not listen for it.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Jan 26, 2010 at 3:57 PM, MARSHALL HAHN-TAYLOR <
> marshall_hahn-taylor@canb.uscourts.gov> wrote:
>
> > Hello,
> >
> > I apologize in advance if this is not the place for unixODBC/Informix
> > questions. I'm attempting to use unixODBC on a Redhat 4.0 host `VMRH40BO'
> > with IBM's clientsdk 3.00 to connect to an Informix database located
> > on my local subnet on host `maindb'. I'm confused by how the sqlhosts
> > and obdc.ini files should look. Currently my VMRH40BO:/etc/sqlhosts
> > file contains:
> >
> > maindb onsoctcp maindb port_alias> > demo_se seipcpip se_hostname sqlexec
> >
> > where port_alias is set in VMRH40B0:/etc/services as follows:
> >
> > port_alias 8201/tcp
> >
> > My VMRH40BO:/etc/odbc.ini file contains:
> >
> > [ODBC Data Sources]
> > CRSQLServerWP=DataDirect 5.1 SQLServer Wire Protocol Driver
> > CRSybaseWP=DataDirect 5.1 Sybase Wire Protocol Driver
> > CRText=DataDirect 5.1 Text Driver
> > InformixODBC=HP G6 running Informix Database
> >
> > [Informix]
> > Driver=/opt/informix/lib/cli/iclit09b.so
> > Description=HP G6 running Informix Database
> > IpAddress=192.168.1.100
> > Server=main_shm
> > TcpPort=7101
> > Database=main_live
> > LogonId=crystal
> > Password=password
> >
> > Now, given the database configuration on maindb given below....
> >
> > maindb's database listing (from dbaccess):
> >
> > SELECT DATABASE >>
> > Select a database with the Arrow Keys, or enter a name, then press Return.
> >
> > ------------------------------------------------ Press CTRL-W for Help
> > --------
> >
> > main_live@main_ecf
> >
> > main_train@main_ecf
> >
> > sysadmin@main_ecf
> >
> > sysmaster@main_ecf
> >
> > sysuser@main_ecf
> >
> > sysutils@main_ecf
> >
> > maindb:/etc/services file:
> >
> > # End Media Manager services #
> > main_ecf 7101/tcp # Informix instance main_ecf
> >
> > ....what should my odbc.ini and sqlhosts files look like on VMRH4BO?
> >
> > Also, I've been attempting to use isql to see if I could connect from
> > VMRH4BO to maindb, invoking it as follows:
> >
> > isql -v Informix crystal password
> >
> > When the above is run, it leaves me infinitely waiting for it to
> > return with no output to the screen. Running strace on it yields:
> >
> > [mtaylor@VMRH4BO ~]$ strace -p 24656
> > send(3, "sqAbYBPQAAsqlexec crystal -pcmxt"..., 442, 0
> >
> > Is there a better way to test the connection to the database?
> >
> > Thanks in advance,
> >
> > Marshall
> >
> >
> >
> >
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >