problem in configuring DSN in Unix

anujgarg · Post by **anujgarg** » Wed Oct 05, 2005 11:44 pm

Hi ,

I am trying to create DSN on unix,but facing the problem.I have created the 'ora' DSN but it is giving warning and not able to connect to database.

First i have created the DSN in .odbc.ini in DSEngine

[Ora]
Driver=/opt/Ascential/DataStage/branded_odbc/lib/VMora19.so
Description=DataDirect Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=149.27.178.145
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=1521
ProcedureRetResults=0
SID=DWTST01
UseCurrentSchema=1

And Then i have added DSN in Uvodbc.config in specific Projects folder.

Code: Select all

<Ora> 
DBMSTYPE = ODBC

I am able to see DSN name while importing tables and gives following error message while trying to import table definitions

DSR.MetaGeta(GET.TABLES)(SQLConnect('Ora','dwd6')): BCI Error:
SQLSTATE=HY000,CODE=7505,[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
SQLSTATE=HY000,CODE=-1,[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-12203: unable to connect to destination

I am not able to see the DSN name also while using ODBC stage.

All suggestions are Welcomed.

Thanks and Regards,
Anuj

Bala R · Post by **Bala R** » Thu Oct 06, 2005 12:37 am

As the error message suggests - Did you check the host name and port number in your DSN setup?
Did you click on the Test Connection button on the ODBC DSN setup tab and verified you could establish a connection?

anujgarg · Post by **anujgarg** » Thu Oct 06, 2005 12:41 am

Hi,

I have checked from tnsnames.ora file and also nothing is there in Unix to check the connection.And port number i have taken is 1521 which is the port for oracle.

Bala R · Post by **Bala R** » Thu Oct 06, 2005 1:02 am

Isn't that you setup the ODBC DSN on the client machine which would be on Windows?
Or how did you setup your DSN?

aesguerra · Post by **aesguerra** » Thu Oct 06, 2005 2:04 am

you might want to check if you can connect to the oracle db natively by using sqlplus.

anujgarg · Post by **anujgarg** » Thu Oct 06, 2005 2:41 am

Hi,

I am able to connect through sqlplus But cannot connect through DSN in Datastage.

ml · Post by ml » Fri Oct 07, 2005 1:07 pm

Hi Anuj,

Are you sure that your Oracle server (149.27.178.145 ) is reachable from your DS server?

also you can test the connection from "/branded_odbc/demo/demoodbc" and the driver VMora19 with "/branded_odbc/bin/ivtestlib".

kcbland · Post by **kcbland** » Fri Oct 07, 2005 2:18 pm

Any reason to use ODBC over OCI? If you can do tnsping from the unix prompt then it obviously means that the server has the Oracle sqlnet client installed. Why not configure DS for Oracle OCI and skip the ODBC stage altogether?