Configure unixODBC in Linux

Configuring unixODBC on Linux, can be  a general requirement for Peoplesoft or many Application to connect to Oracle database.

Check whether all required RPMs present or Not…as follows.

[root@SAMOEL47UAT2 unixODBC-2.2.14]# rpm -q unixODBC
unixODBC-2.2.11-1.0.1.RHEL4.1
[root@SAMOEL47UAT2 unixODBC-2.2.14]# rpm -q qt
qt-3.3.3-13.RHEL4
[root@SAMOEL47UAT2 unixODBC-2.2.14]# rpm -q unixODBC-kde
unixODBC-kde-2.2.11-1.0.1.RHEL4.1
Output of following command should be /usr

[root@SAMOEL47UAT2 unixODBC-2.2.14]# odbc_config --prefix

Result of above ccommand on Oracle Enterprise Linux is:
/usr
Now copy the unixODBC-2.2.14.tar to /u01

then extract it using tar -xvf unixODBC-2.2.14.tar which will create a folder called unixODBC-2.2.14

Now go to that folder execute the following commands in the appropriate order

cd unixODBC-2.2.14
./configure --prefix=/u01/unixODBC-2.2.14/ --exec-prefix=/u01/unixODBC-2.2.14/ --enable-gui=no
make
make install

After the above command "make install" completed, Go for a tea Break, come back and fire following commands again
Result of following command on Oracle Enterprise Unix should be :

[root@SAMOEL47UAT2 unixODBC-2.2.14]# odbc_config --prefix
/usr/local

[root@SAMOEL47UAT2 unixODBC-2.2.14]# odbc_config --odbcinstini
/usr/local/etc/odbcinst.ini

[root@SAMOEL47UAT2 unixODBC-2.2.14]# odbc_config --odbcini
/usr/local/etc/odbc.ini

[root@SAMOEL47UAT2 unixODBC-2.2.14]# odbcinst -j

unixODBC 2.2.14
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2
Put the following environment variables in your $HOME/.bash_profile (e.g. vi /home/psoft/.bash_profile)

export DM_HOME=/u01/unixODBC-2.2.14/
export PATH=$DM_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$DM_HOME/lib:$LD_LIBRARY_PATH

Edit the following lines in /usr/local/etc/odbcinst.ini ..(The following lines will create an ODBC oracle Driver of name “Oracle_ODBC_Driver” )

[ODBC]
Trace = No
TraceFile = /tmp/sql.log
ForceTrace = No
Pooling = No

[Oracle_ODBC_Driver]
Description = Oracle 10g R2 ODBC driver.
Driver = /u01/oracle/pshome/client_1/lib/libsqora.so.10.1
Setup =/u01/unixODBC-2.2.14/DRVConfig/Oracle/.libs/liboraodbcS.so
FileUsage =
CPTimeout =
CPReuse =

Till this STEP All above things need to be done as ROOT User.
After This STEP

Edit the following lines in $HOME/.odbc.ini
Where
TESTDSN is the name of Data Source Name,
DB,ServerName parameters are your SERVICE NAME from tnsnames.ora USER,
UserID is the username for database,
Driver name should be same as the name from previous step i.e.”Oracle_ODBC_Driver” (From /usr/local/etc/odbcinst.ini)
NOTE: For multiple connections you can create multiple DSNs in one file i.e. $HOME/.odbc.ini

[TESTDSN]
Description = MY_ODBC
Driver = Oracle_ODBC_Driver
DB = PSDEVI
USER = sysadm
PASSWORD = prodadm1
ORACLE_HOME = /u01/oracle/pshome/client_1/
TNS_ADMIN = /u01/oracle/pshome/client_1/network/admin/
DSN = TESTDSN
ServerName = PSDEVI
UserID = sysadm

Now you can use isql to test the connection

isql -v TESTDSN
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+