Do I need, can I have SID=Oracle SID in Oracle odbc defintio

[flashgordon]

Hi,

Our SE/Oracle link is working quite well thanks to this site. We are using a huge Peoplesoft Schema (1000+ tables) and Oracle Plug In Import is taking a 1/2 hour to come up. It is not an EE site and the Orchestrate import is not working. We want to use the Oracle ODBC drivers so we can import Oracle metadata, one table at a time, and not have to wait the 1/2 hour for the list to come up. I've never done Oracle odbc before. The vanilla Oracle ODBC definition does not have a "SID=Oracle SID" entry in it. It does have a "HostName=Oracle server" in it. I am slightly nervous about trying to access ODBC Oracle without a SID. Does it work to just specify "HostName=Oracle server"?

Also does the datastage server need to be booted to make the new odbc connections known? My memory was that you didn't need to reboot the server.

[chulett]

I haven't had any issues using the same 'tnsname' in both the thick and thin Oracle ODBC drivers:

Code: Select all

SID=XXXXXX          (Wired)
ServerName=XXXXXX   (Non-wired)

Changes/additions to this are immediately available.

[flashgordon]

Craig,

Thanks. That helps a lot. Use the SID for Host Name. I was going to put the Unix server name. Thanks for clarifying that. I saw the SID option on the Thin in the default .odbc.ini and got confused.

.. Flash

[swades]

Sample Entry in .odbc.ini

[DSN]
Driver=/apps/dstage/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=DB Host
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber= port_number
ProcedureRetResults=0
SID=Service Name
UseCurrentSchema=1

If your DB Address on multiple HOST, just DEFINE servicename1 as SID, so it explicitly connect to any one host. And make sure you enter this in uvodbc.config under Project Directory. You dont need to RESTART your DS server.