DSXchange

Hi everyone,

I am trying to set up ODBC connection from Datastage 7.5.2 on RHEL 4.0 to SQLServer on a Windows 2003 Server.
I did set up .odbc.ini, dsenv, and uvodbc.config both in $DSHOME and in my projects directory. I am using SQL Wired protocol.

I checked the connection through DSEngine shell and I am able to see DSN with DS_CONNECT. Also I am able to login DS_CONNECT <dsn>. All the queries to the tables in SQLServer database are succesful.
But problem is that I can not import ODBC Table definition from SQLServer. It always shows error.

DSR.MetaGeta(GET.TABLES)(SQLConnect('car','datawh')):BCI Error: SQLSTATE=IM976, CODE=-1,
[DataStage][SQL Client][ODBC][Data source name not found, or ODBC is not installed on the system

Here are the content of files:

.odbc.ini
[ODBC Data Sources]
DB2 Wire Protocol=DataDirect 5.0 DB2 Wire Protocol Driver
dBase=DataDirect 5.0 dBaseFile(*.dbf)
Informix=DataDirect 5.0 Informix
Informix Wire Protocol=DataDirect 5.0 Informix Wire Protocol
Oracle=DataDirect 5.0 Oracle
Oracle Wire Protocol=DataDirect 5.0 Oracle Wire Protocol
car=DataDirect SQL Server Wire Protocol
Sybase Wire Protocol=DataDirect 5.0 Sybase Wire Protocol
Text=DataDirect 5.0 TextFile(*.*)
....

[car]
Driver=/home/dsadm/Ascential/DataStage/branded_odbc/lib/VMmsss20.so
Description=DataDirect SQL Server Wire Protocol
Database=ARSystem
LogonID=datawh
Password=DataHouse07
Address=x.x.x.x, 1433
QuotedId=No
QEWSD=39179
AnsiNPW=No

....

uvodbc.config
[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1
<car>
DBMSTYPE = ODBC

dsenv
set +u

if [ -z "$DSHOME" ] && [ -f "/.dshome" ]
then
DSHOME=`cat /.dshome`
export DSHOME
fi

if [ -z "$DSHOME" ]
then
DSHOME=/home/dsadm/Ascential/DataStage/DSEngine; export DSHOME
fi

if [ -z "$APT_ORCHHOME" ]
then
APT_ORCHHOME=; export APT_ORCHHOME
fi

if [ -z "$UDTHOME" ]
then
UDTHOME=/home/dsadm/Ascential/DataStage/ud41; export UDTHOME
UDTBIN=/home/dsadm/Ascential/DataStage/ud41/bin; export UDTBIN
fi

if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
HOME=${HOME:-/}; export HOME

#LANG="<langdef>";export LANG
#LC_ALL="<langdef>";export LC_ALL
#LC_CTYPE="<langdef>";export LC_CTYPE
#LC_COLLATE="<langdef>";export LC_COLLATE
#LC_MONETARY="<langdef>";export LC_MONETARY
#LC_NUMERIC="<langdef>";export LC_NUMERIC
#LC_TIME="<langdef>";export LC_TIME
#LC_MESSAGES="<langdef>"; export LC_MESSAGES

LD_LIBRARY_PATH=`dirname $DSHOME`:$DSHOME/lib:$DSHOME/uvdlls:$DSHOME/java/jre/lib/i386/client:$DSHOME/java/jre/lib/i386:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
fi

ORACLE_HOME=/u01/app/oracle/product/10gR2/db_1
export ORACLE_HOME
DSHOME=/home/dsadm/Ascential/DataStage/DSEngine
DS_HOME=/home/dsadm/Ascential/DataStage
export DSHOME
export DS_HOME
LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$DSHOME/lib:$DSHOME/uvdlls:$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$DS_HOME/branded_odbc/lib"
export LD_LIBRARY_PATH
ODBCINI="$DSHOME/.odbc.ini"
export ODBCINI

Any help would be appreciated.
Thank you

First off, where are the two files that you mentioned - .odbc.in and uvodbc.config? Which is in the DSEngine directory and which is in your Project directory?

Also, in your dsenv file - why are you manually setting DSHOME and ODBCINI near the end of the file? There's no need for that as they are already set right off the bat. Also, you need to remove the quotes you put around that last expansion of the LD_LIBRARY_PATH variable, that makes it put that literal string there and not expand anything.

Stop and restart DataStage once you've done that.

It does not help but thank you for your reply.
Finally, I have found out the answer. It is because DS_HOME and DSHOME variable is added into /home/dsadm/.bash_profile and dsenv file.
So I reinstalled DataStage 7.5.2 on my RHEL 4.0 server and edited .odbc.ini, uvodbc.config, dsenv as guided by the documents in ../branded_odbc/books. Connection from DSServer on RHEL via ODBC to SQLServer is successful.