Page 1 of 1
connecting postgres using odbc drivers thru datastage
Posted: Thu Mar 27, 2008 4:19 pm
by ramabbm
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.
Posted: Thu Mar 27, 2008 5:23 pm
by ray.wurlod
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?
Posted: Thu Mar 27, 2008 5:54 pm
by ramabbm
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? ...
Posted: Thu Mar 27, 2008 5:58 pm
by ramabbm
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.
Posted: Thu Mar 27, 2008 6:04 pm
by ray.wurlod
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.
Posted: Thu Mar 27, 2008 6:24 pm
by ramabbm
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
Posted: Thu Mar 27, 2008 7:29 pm
by ray.wurlod
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?
Posted: Fri Mar 28, 2008 1:06 pm
by ramabbm
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.
Posted: Fri Mar 28, 2008 4:40 pm
by ray.wurlod
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.
Posted: Mon Mar 31, 2008 1:46 pm
by ramabbm
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
Posted: Mon Mar 31, 2008 2:13 pm
by lstsaur
No, trace should be coded trace=1 not "=yes".