[IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed [ISQL]ERROR: Could not SQLConnect

]#   isql -v MYPGDB

[IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

[ISQL]ERROR: Could not SQLConnect

Cause

Either odbc drivers are not mentioned properly or environment variable are not set ODBCSYSINI ODBCINI

Solution

1 ) Set driver in /etc/odbc.ini file

Driver                = /usr/lib64/psqlodbc.so

Description           = PostgreSQL ODBC

2) Export path of odbc.ini file

]# export ODBCSYSINI=/etc

]# export ODBCINI=/etc/odbc.ini

]#   isql -v MYPGDB

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

Top command

top - 09:39:16 up 281 days, 13:03,  3 users,  load average: 3.49, 2.81, 2.33
Tasks: 254 total,   2 running, 252 sleeping,   0 stopped,   0 zombie
Cpu(s): 12.3%us,  1.7%sy,  0.0%ni, 71.7%id, 14.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  325058k total, 266795k used,  58263k free,   2517k buffers
Swap:  83858k total,   20589k used,  817999k free, 2390744k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 6371 Vanita     25   0     46748   6064    2320  R 99.6       0.0           20815:15           scp
23921 oracle    18   0     1.9g      2.7g     2.5g  D  8.3       8.6           0:26.03            oracle
22054 oracle    15   0    1.7g      693m    682m S  1.3      2.2             0:37.01             oracle
 2760 oracle    18   0     1.7g      36m     23m D  1.0        0.1            676:09.89               oracle
 1714 oracle    18   0     627m    159m   35m S  0.3        0.5          2:46.30                 java
24068 oracle    15   0   10992  1168    772 R  0.3           0.0          0:00.55                 top

 “top”, indicates that the system has been up and running for 281  days.
 “Tasks”,  indicates the total number of processes along with a breakdown of running, sleeping, stopped and zombie processes count.
“Cpu(s)”  indicates the current CPU utilization of the system. In this example, CPU is  71.7% idle
 “Mem”  indicates provides the memory information.
"swap" swap memory  information
VIRT stands for the virtual size of a process and it represents how much memory the program is able to access at the present moment
RES stands for the resident size, which is an accurate representation of how much actual physical memory a process is consuming.
SHR indicates how much of the VIRT size is actually sharable .
%CPU CPU % using a process
%MEM  Memory using process
TIME+ elapsed time of process

How to install unixODBC on linux system

How to install unixODBC  on linux system

Open DataBase Connectivity (ODBC) is a cross-platform, call-level interface that allows applications to access data in any database for which there is an ODBC driver. Using ODBC, you can create database applications with access to any database for which your end-user has an ODBC driver. ODBC provides an API that allows your application to be independent of the source database management system (DBMS). This enables for software developers to write open database programs without the need of a proprietary interface

 1) download software

2) untart the files

gunzip  unixODBC-2.3.1.tar.gz

tar xvf unixODBC-2.3.1.tar

cd unixODBC-2.3.1

3) to install the unixodbc run below command

./configure  --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=yes --enable-drivers-conf=yes

Make

make install

 

--enable-drivers: This parameter enables building the drivers that were installed by default in previous versions.

--enable-drivers-conf: This parameter enables building the driver configuration libraries that were installed by default in previous versions.

4) verify  the  installtion

]# odbcinst -j

unixODBC 2.3.1

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /etc/odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

5) Specify the Driver location in odbcinst.ini file

vi   /etc/odbcinst.ini

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/pgsql-9.1/lib/psqlodbcw.so

Setup           = /usr/lib64/libodbcpsqlS.so.1.0.0

FileUsage       = 1

6) Set  database connection information in odbc.ini file and specified the driver which you have mentioned in odbcinst.ini file

vi : /etc/odbc.ini

[MYDATA]

Driver                = PostgreSQL

Description           = PostgreSQL ODBC

Servername            = xyz

Port                  = 5432

Database              = mydb

Username              = myuser

Password              = pass

ReadOnly              = false

ShowSystemTables      = false

LegacySQLTables       = false

LoginTimeout          = 0

QueryTimeout          = 0

DateFormat            = 1

NumericAsChar         = false

SQLBitOneZero         = false

StripCRLF             = false

securityLevel         = preferredUnSecured

caCertFile            =

[ODBC]

IANAAppCodePage=4

InstallDir=/opt/odbc32v51

Trace=0

TraceDll=/opt/odbc32v51/lib/odbctrac.so

TraceFile=odbctrace.out

UseCursorLib=0

7) Export the environment variable

]#  export ODBCSYSINI=/etc

]# export ODBCINI=/etc/odbc.ini

8) test connection

]#  isql -v  MYDATA

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

# query system dsn's

]#odbcinst -q -s

[MAYADATA]

Dltest -> is a utility used to check a share library to see if it can be loaded and if a given symbol exists in it.

Isqlà is an utility which can be used to submit SQL to a data source and to format/output results. It can be used in batch or interactive mode.

Iusql -> provides the same functionality as the isql program.

odbc_config -> is used to find out details about the installation of the unixODBC package.

Odbcinst->is an utility created for install script/RPM writers. It is a command line interface to key functionality in the libodbcin