connecting postgres using odbc drivers thru datastage
Moderators: chulett, rschirm, roy
connecting postgres using odbc drivers thru datastage
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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