DSXchange

Hi
We are getting the following error while trying to import tables from Oracle 10g

DSR.MetaGeta(GET.TABLES)(SQLConnect('castle','hg426c')): BCI Error:
SQLSTATE=IM002,CODE=0,[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Data source name not found and no default driver specified

We have defined the dsn names in .odbc.ini, uvodbc.config and also modified dsenv.

entry in uvconfig.odbc
<oracle10>
DBMSTYPE = ODBC

Still we are getting the error. Please help us in getting this resolved. Also kindly let me know, if you need any additional info.

Thanks in advance

1.
If you using a Native ODBC driver , you need to make sure this is part of the environmental variable setting

2. you could try creating a new DSN using the standard Driver (DB Specific)

3. Check for the DB Specific access rights

Any one of the above should solve your problems ..

Hi Shankar
The dbuser has no rights problem. We have given two entries in the file .odbc.ini for both wire and non-wire for Oracle. Does having both of them in the file make an issue ?

Please advise

Please post the entries for "castle" from .odbc.ini and from uvodbc.config file.

Did you restart DataStage after editingthe .odbc.ini file?

entry in .odbc.ini
[Oracle]
Driver=/opt/dataStage/Ascential/DataStage/branded_odbc/lib/VMor820.so
Description=DataDirect 5.00 Oracle
ApplicationUsingThreads=1
ArraySize=60000
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
OptimizeLongPerformance=0
Password=
ProcedureRetResults=0
ServerName=<servername>
UseCurrentSchema=1

[Oracle Wire Protocol]
Driver=/opt/dataStage/Ascential/DataStage/branded_odbc/lib/VMora20.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=<servername>
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=
ProcedureRetResults=0
SID=
UseCurrentSchema=1


For uvodbc.config

<oracle10>
DBMSTYPE = ODBC

if these entires are correct, we will restart again. But I wanted to clarify if there is no other issue before restarting.

You've edited the template entries in the .odbc.ini file, not something you should be doing. They should be reverted back and copied to a new section of the file. Also note that the name of that entry must match to the entry in the uvodbc.config file.

At the very least, those two changes need to be made.

I dont see any entries for the DSN "castle".

First line is the Connection name and oracle10 is missing there.
oracle10 is the Connection Name that you specified at your project level {uvodbc.config}

Create a copy of the template and start editing it.
Connection Name in the first line, Hostname, PortNumber and SID are mandatory. Get these information from your DBA or Admin.

Hi Kommven,
I have few questions here.
1. We need to have a separate entry for the dsn(instead of using the template). which should start with the dsn name like

[oracle10]
.........
right ?

2. Can we keep entries for both wire and non-wire protocols in the same .odbc.ini file ? If yes, in such a case we would have 4 entries, two for each of the dsns.

3. when you say connection name, I assume that you mean the dsn or we need to specify it separately again after the line [oracle10] with the parameter "ConnenctionName" ?

4. HostName, PortNumber and SID have been specified in the wire Protocol but not in non-wire. Should we need to mention them in both the places ?

5. In the file uvodbc.config, a single entry is fine or we need to give the hostname, portnumber etc.. ?

Thanks in advance.

1. Yes.

2. Yes.

3. 'ConnectionName' just means what you use in your jobs. Basicall, the 'dsn'.

4. Nope. Client (non-wire) drivers get that from the tns entry.

5. Single entry.

Hi chulett,
I did all the things as said. Server has also been restarted. but when i tried to import an odbc table i got this error:

Error calling subroutine: DSR_IMPORT (Action=14); check DataStage is set up correctly in project project1
(The connection is broken (81002))

Sorry, that's a new one on me and a search of the forums doesn't reveal anyone posting that message before. Suggest you contact your official Support provider.

After re-starting the server, it works well for wire protocol but not for non-wire. But we have the oracle client installed on the machine where the DataStage server is installed. Still we could not connect using non-wire protocol. Can you help me on this?

If you're running with oracle and already have the oracle client installed why don't you use the OCI stage which will be quicker? Unless you have specific requirements for using ODBC.

Have you configured DataStage for Oracle? It's documented in the Install and Upgrade Guide and requires the dsenv file be edited to contain specific entries for ORACLE_HOME and your 'Shared Library Path'.