In this series of articles we will see how to fetch data from Informix database into Oracle Database.
There can be 2 methods to do this, or might be more :)
The use of Oracle Gateway 11g for Informix or Oracle Gateway 11g for ODBC, the first option requires the license from Oracle Support, and the second has no license requirement. But in second option we need to have a ODBC driver for Informix which is a licensed from the vendor.
Cook Book:
We would test the fetching of data from Informix database into Oracle database by the use of the second option, i.e. we would need the Oracle Gateway 11g for ODBC and offcourse we need the ODBC driver for Informix database, which can be downloadable freely for a trial.
We need the following software for this testing.
1- ODBC driver for Informix database
2- Oracle 10g R2 (10.2.0.4) database (in which we will fetch the data from Informix database)
3- Oracle Gateway 11g for ODBC (11.0.7.0)
4- Informix database
In this article we will install the ODBC driver and connect this with the Informix database to check the connectivity with the Informix at ODBC level. This is most important step, as if it is successfull only then we can move forward to the next steps.
1- ODBC driver for Informix installation and configuration:
Installation:
The ODBC driver can be downloaded from the www.datadirect.com site for a trial free of cost. We would downlad the evsol64.tar.Z, this is for the Sun Sparc 64 bit.
We have the Sun Sparc machine for the testing, so we downloaded the ODBC driver for Informix for 64 bit Sun Sparc version.
For the ODBC installation, it is better to create a OS user "odbc" user, which will own the ODBC binaries. The installation is simple and straightforward. And it will take around 300MB - 350MB approx.
The environment variable ODBC_HOME can be set to the installation directory, and the PATH environment variable also can be set for the ODBC binaries / executables.
Configuration:
After the installation, we need to configure the ODBC driver for Informix so it could connect to the Informix and can fetch data at the ODBC level.
For the configuration we need to edit the "odbc.ini" file, this is the only file which needs to be configured for the ODBC connectivity, and if once it is configured successfully then the rest would be very easy. Always remember this is the main core file for the connectivity of Informix database to Oracle Database.
If the file is checked, we can see that there are lot of sections inside this "odbc.ini" file, and every section has its own purpose.
First section in the odbc.ini file is [ODBC data sources], and it contains all the information regarding all the databases which can be configured with this ODBC driver. e.g. Infromix, Sybase, DB2, mySQL, Teradata, PostgreSQL, SqlServer, Greenplum etc
The second section is [ODBC] contains the installation directory, trace level, and trace file directory.
The remaining sections will contain the required information to connect to respective databases, and for this article we are concerned with the section [Informix].
We need to change the parameters related to the Informix server. Following parameters needs to be modified.
1- Database - The name of Informix database to connect
2- HostName - The machine name which hosts the Informix database
3- LogonID - Informix database userid
4- Password - Password for the Informix database userid
5- PortNumber - Informix database portnumber
6- ServerName - This can be found out from ONFONGI file and there is parameter DBSERVERNAME
After setting these parameters in the odbc.ini file, we need to test the configuration, whether it connects to the Informix database and fetches the records at ODBC level.
Testing the configuration:
For a testing it is better to create a test user on the Informix database. Once user created on Informix database, we need to create a test table and insert some records into the test table. In the ODBC installation directory there is "demo" directory, there are sql scripts for each database, we can use the script "empinformix.sql" script to create a test table. We used Sql Razor tool to connect to the Informix database with the test user and created the table emp and isnerted some records into this table.
The utiltiy "demoodbc" can be used to test the ODBC connectivity with Informix database.
the syntax of "demoodbc" is
$ demoodbc -uid username -pwd password Informix Database Source name (from the file odbc.ini)
We created the user as "test" on Informix database and password is abc12345 and the data source name is "Informix" in the odbc.ini file, now we can test the ODBC connection as follows...
$ demodbc -uid test -pwd abc12345 Informix
for this execution we needs to be in the directory "demo" inside the ODBC installation directory.
on the execution of the above statement we got the following error messages....
==========================
bash-2.05$ demoodbc -uid test -pwd abc12345 Informix
demoodbc DataDirect Technologies, Inc. ODBC Sample Application.
demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.
SQLConnect: Failed...
SQLSTATE = S1000
NATIVE ERROR = -329
MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.
SQLConnect: Retrying Connect.
SQLConnect: Failed...
SQLSTATE = S1000
NATIVE ERROR = -329
MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.
SQLConnect: Retrying Connect.
SQLConnect: Failed...
SQLSTATE = S1000
NATIVE ERROR = -329
MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.
SQLConnect: Retrying Connect.
No connection could be established.
==========================
having seen the error message "carefully" we found that there is issue with the database name, when we checked the [Informix] section of the odbc.ini file, we found that the value for parameter "Database" is incorrect, on correction and re-execution of the "demoodbc" we got the following messages....
=====================
demoodbc DataDirect Technologies, Inc. ODBC Sample Application.
demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.
SQLConnect: Failed...
SQLSTATE = S1000
NATIVE ERROR = -761
MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.
SQLConnect: Retrying Connect.
SQLConnect: Failed...
SQLSTATE = S1000
NATIVE ERROR = -761
MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.
SQLConnect: Retrying Connect.
SQLConnect: Failed...
SQLSTATE = S1000
NATIVE ERROR = -761
MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.
SQLConnect: Retrying Connect.
No connection could be established.
============
having seen the error messages, we corrected the Servername parameter value in the odbc.ini file, we got the correct value from the ONFONFIG file from the Informix database machine. after correcting this value and again executing the "demoodbc" , it was successfull with the following messages :)
================
bash-2.05$ demoodbc -uid test -pwd abc12345 Informix
demoodbc DataDirect Technologies, Inc. ODBC Sample Application.
demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.
First Name Last Name Hire Date Salary Dept
---------- --------- --------- ------ ----
Tyler Bennett 32000 D101
Tyler Bennett 32000 D101
John Rappl 47000 D050
George Woltman 53500 D101
Adam Smith 18000 D202
David McClellan 41500 D101
Rich Holcomb 49500 D202
Nathan Adams 21900 D050
Richard Potter 15900 D101
David Motsinger 19250 D202
Tim Sampair 27000 D101
Kim Arlich 57000 D190
Timothy Grove 29900 D190
Tyler Bennett 32000 D101
Tyler Bennett 32000 D101
John Rappl 47000 D050
George Woltman 53500 D101
SQLFetch returns: SQL_NO_DATA_FOUND
In the next article we will move forward with installation, configuration and testing of Oracle Database Gateways 11g 11.1.0.6 and patching to 11.1.0.7, and then installation of the Oracle 10g R2, patching to 10.2.0.4.
