connecting postgres using odbc drivers thru datastage

ramabbm · Post by **ramabbm** » Thu Mar 27, 2008 4:19 pm

we are trying to connect to a postgres 8.1 database from datastage using the openlink odbc drives and it says it couldnot load the drivers.

we have the modified the .odbc.ini and make the required changes in uvodbc.config and dsenv file

tried to restart the server after changing all the parameters.
we are not sure what is the syntax for postgres variables

here is our entries in .odbc.ini

[PostgresPG4]
Driver=/home/p139pkg/odbc/source/lib/pgr7_mt_lt.so
Servername=11.16.155.104
Username=tstusr
Password=tstusr
Port=5432
Trace=Yes
TraceFile=/tmp/trace_psqlodbc.log

we also noticed that we have no trace file in /tmp

here is the actual message from datastage

DSR.MetaGeta(GET.DSNINFO)(SQLConnect('PostgresPG4','tstusr')): BCI Error:
SQLSTATE=IM003,CODE=0,[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Specified driver could not be loaded

It would be a great help if someone can help us to get this connectivity working.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 5:23 pm

Have you checked that the executing user ID has sufficient permission to /home/p139pkg/odbc/source/lib/pgr7_mt_lt.so including "x" to the shared object itself?

ramabbm · Post by **ramabbm** » Thu Mar 27, 2008 5:54 pm

yes, we have tried with chmod 777
still we have the same err..

ray.wurlod wrote:Have you checked that the executing user ID has sufficient permission to /home/p139pkg/odbc/source/lib/pgr7_mt_lt.so including "x" to the shared object itself? ...

ramabbm · Post by **ramabbm** » Thu Mar 27, 2008 5:58 pm

we also tried using the example binary using root
with same result, i have copied the error below

# example
example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : PostgresPG4

Enter the user name : tstusr

Enter the password : tstusr
SQLSTATE = IM002
NATIVE ERROR = 0
MSG = [DataDirect][ODBC lib] Data source name not found and no default driver specified

SQLConnect: Retrying Connect.
SQLSTATE = IM002
NATIVE ERROR = 0
MSG = [DataDirect][ODBC lib] Data source name not found and no default driver specified

SQLConnect: Retrying Connect.
SQLSTATE = IM002
NATIVE ERROR = 0
MSG = [DataDirect][ODBC lib] Data source name not found and no default driver specified

SQLConnect: Retrying Connect.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 6:04 pm

That's a completely different error. Read both error messages carefully. "Could not be found" and "Could not be loaded" are not the same thing.

ramabbm · Post by **ramabbm** » Thu Mar 27, 2008 6:24 pm

yeah, we resolved that err
now both the places we are getting the same err,
there was some issues with DSN name

we also tried all these drivers and result is the same
-rwxrwxrwx 1 p139pkg ux_mdc 1901328 May 24 2007 pgr7_mt_lt.so
-rwxrwxrwx 1 p139pkg ux_mdc 1945847 May 24 2007 pgr7_mt_lu.so
-rwxrwxrwx 1 p139pkg ux_mdc 1900696 May 24 2007 pgr7_st_lt.so
-rwxrwxrwx 1 p139pkg ux_mdc 1945215 May 24 2007 pgr7_st_lu.so

# example
example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : pg
Enter the user name : tstusr
Enter the password : tstusr
SQLSTATE = IM003
NATIVE ERROR = 0
MSG = [DataDirect][ODBC lib] Specified driver could not be loaded

SQLConnect: Retrying Connect.
SQLSTATE = IM003
NATIVE ERROR = 0
MSG = [DataDirect][ODBC lib] Specified driver could not be loaded

SQLConnect: Retrying Connect.
SQLSTATE = IM003
NATIVE ERROR = 0
MSG = [DataDirect][ODBC lib] Specified driver could not be loaded

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 7:29 pm

Which odbc.ini file were you using - the one in DataStage or the one in branded_odbc - when you did this test?

Was there anything useful in the trace log?

ramabbm · Post by **ramabbm** » Fri Mar 28, 2008 1:06 pm

We are using the one in Datastage [.odbc.ini]. We did not change anything in obbc.ini file under branded_odbc.

We did not find any trace log file under temp. Could you please let us know how to set this trace file.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 28, 2008 4:40 pm

You already have it:
Trace=Yes
TraceFile=/tmp/trace_psqlodbc.log

But, if the driver can not be loaded, it won't be up to the point where it can trace what the driver is doing.

ramabbm · Post by **ramabbm** » Mon Mar 31, 2008 1:46 pm

After installing 32 odbc drivers onstead of 64 bit, datastage example binary is working fine. However from datastage client we are getting the following error message.

DSR.MetaGeta(GET.DSNINFO)(SQLConnect('PG32','tstusr')): BCI Error:
SQLSTATE=S1000,CODE=79,[DataStage][SQL Client][ODBC][OpenLink][ODBC][PostgreSQL Server]A remote host refused an attempted connect operation.

we changed the followig env variables in dsenv to make it work
####Openlink Postgres ####################
DBCINSTINI="/home/p139pkg/odbc/source/32bit/bin/odbcinst.ini"
PATH="/home/p139pkg/odbc/source/32bit/bin:/home/p139pkg/odbc/source/32bit/samples/ODBC:$PATH"
LD_LIBRARY_PATH="/home/p139pkg/odbc/source/32bit/lib:/fs2/oracle/product/10.2.0/
db_1//lib32:/fs2/oracle/product/10.2.0/db_1//lib32:/home/p139pkg/odbc/source/lib
:/lib:/usr/lib:/www/a/home/db2inst4/sqllib/lib64:$LD_LIBRARY_PATH"
#########################################

with the above changes the example binary is working fine but again same err with data stage client

we assume the datastage client also takes the same env variable values but we are not sure how to check that ....

is there any other way to troubleshoot this, we are also not getting any trace.logs even if the .odbc.ini has trace=yes and trace log set to /tmp/trace.log

lstsaur · Post by **lstsaur** » Mon Mar 31, 2008 2:13 pm

No, trace should be coded trace=1 not "=yes".