ORACLE RAC-ODBC connection -Datastage 8.1

RaviReena · Post by **RaviReena** » Tue Oct 06, 2009 10:08 am

I am trying to establish an ODBC connection for my ORACLE RAC database to import metadata using table definitions of stored procedure.

Here is my tnsnames entry:
test.world =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = test1)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = test2)
(PORT = 1521)
)
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

How should be my odbc.ini file entry for datastage to connect for ODBC connection?

Any help is appreciated.

Thanks.

Sreenivasulu · Post by **Sreenivasulu** » Tue Oct 06, 2009 10:57 am

For connection via odbc entry is required in .odbc.ini
For connectino via oracle enterprise stage entry is not required in .odbc.ini
But the userid used to connect to oracle should have dba level priviliges

Regards
Sreeni

RaviReena · Post by **RaviReena** » Tue Oct 06, 2009 11:20 am

I agree with you, i need a sample odbc.ini entry for Oracle RAC in this scenario.

chulett · Post by **chulett** » Tue Oct 06, 2009 11:24 am

Nothing magical about RAC, your 'samples' are in the .odbc.ini file in $DSHOME like normal. Meaning, treat it as any other ODBC Oracle data source.

RaviReena · Post by **RaviReena** » Tue Oct 06, 2009 11:38 am

Criag, when i use the following entry it is failing with an error:

TNS:listener could not resolve SID given in connect descriptor

ODBC.ini file entry:
[testodbc]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMora23.so
Description=DataDirect Oracle Wire Protocol driver
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=test1
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=1521
ProcedureRetResults=0
SERVICE NAME = test.world
UseCurrentSchema=1

Do we need to put two odbc entries because it has two host names in TNS entry?

chulett · Post by **chulett** » Tue Oct 06, 2009 12:52 pm

You may have two host names for the load balancing but you still have only a single tnsnames entry and that's all you need at any given time. Have you been able to use this tnsnames entry outside of DataStage? Meaning, can you make a sqlplus connection to it to the RAC database?

RaviReena · Post by **RaviReena** » Tue Oct 06, 2009 12:58 pm

YEs, I am able to connect to this RAC from UNIX box using SQLPLUS.

chulett · Post by **chulett** » Tue Oct 06, 2009 1:02 pm

OK, that's good. Now when you say you are "using it" what exactly does that mean? Is this via an ODBC stage in a job or elsewhere? If elsewhere, have you established whether you can or cannot connect using it in an ODBC stage in a job?

The more details you provide about what you are doing, the better.

RaviReena · Post by **RaviReena** » Tue Oct 06, 2009 1:07 pm

I am using the ODBC Connection by creating a DSN entry and using that DNS entry for Importing the table definitions for Stored procedures.

RaviReena · Post by **RaviReena** » Wed Oct 07, 2009 8:47 am

Any thoughts?

chulett · Post by **chulett** » Wed Oct 07, 2009 11:11 am

I've already posted my thoughts.

chulett wrote:have you established whether you can or cannot connect using it in an ODBC stage in a job?

The more details you provide about what you are doing, the better.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 07, 2009 8:00 pm

Show us your DSN definition from uvodbc.config

RaviReena · Post by **RaviReena** » Fri Oct 09, 2009 8:40 am

Here is my uvodbc.config file contents:

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

<testodbc>
DBMSTYPE = ODBC