Connectivity to Oracle from Datastage:Urgent Help

Viswanath · Post by **Viswanath** » Thu Feb 13, 2003 3:02 am

Hi All,
We are facing problems when trying to connect to Oracle Database using ODBC stage or ORAOCI8 stage. We did the following when trying to connect to the Oracle database using ODBC stage:-

1.)Created an ODBC on the Datastage machine by which it connects to Oracle. 2.)Using this DSN created we try to connect to the database. Now here we are facing problems as the tables present in the database should get reflected when trying to access them from manager, instead it just hangs and nothing happens. We do not get any particular error as such. We have tried to connect to DB2 using ODBC and it works perfectly ok.

When trying to connect using ORAOCI8, we get the following error when trying to view the data, "ds_loadlibrary: Win32 error in LoadLibrary of oraoci8.dll - The specified module could not be found". Any inputs will be a great help.

Viswanath.S

kamalraaj · Post by **kamalraaj** » Thu Feb 13, 2003 4:39 am

Hi Vis,

Change ur plugin.

KAM

Viswanath · Post by **Viswanath** » Thu Feb 13, 2003 4:43 am

Hi,
How do I change the Plug-in? I have tried to Unregister and Register the plug-in and it does not work! Also once I unregister the plug-in when i try to register again I get the following message :-

Unable to register plugin (Path=E:ServerAscentialDataStageEnginebindsdb2.dll) Error Code=-2
Regards

Viswanath.S

raviyn · Post by **raviyn** » Thu Feb 13, 2003 5:05 am

When u are using the ODBC import from manager use the option name contains..In case of oracle schema something like . otherwise this view will show the all the tables in the Oracle database schema for all usernames.....Then u wont feel it is hanged....
HTH[:)]

Viswanath · Post by **Viswanath** » Thu Feb 13, 2003 5:58 am

Hi,
Can you please give me an example supporting this? Say I have a table named EMP under the username SCOTT. How do I specify DataStage to display the snapshot for just this table?
And the application was allowed to standby for more than an hour and still there was no change![?]
Thanks for the answers

Viswanath.S

raviyn · Post by **raviyn** » Thu Feb 13, 2003 6:09 am

I am using DS5.2 when in manager i say import from ODBC there is an option "name contains" in which if u say directly SCOTT.EMP then it displays this faster and import the meta data , than leaving the "name contains" as blank(as in this case it will try to display all the tables).

chulett · Post by **chulett** » Thu Feb 13, 2003 6:47 am

To use OCI for Oracle, you have to ensure that your "dsenv" file has the proper information required by the OCI plugins. The error you are getting implies you haven't done this step.

Find "dsenv", whose location changes depending on your version. The default for 6.0 is under "~dsadm/Ascential/DataStage/DSEngine". Edit the file and add your current $ORACLE_HOME. Add $ORACLE_HOME/lib to your library path. Do this in the PLATFORM SPECIFIC section, save your changes and then stop/restart the server for them to take effect. Only then will OCI work for you.

An example from my server, which is Compaq Tru64. You'll need to make sure you do this in an appropriate manner for yours!

ORACLE_HOME=/oracle/app/oracle/product/8.1.7.3; export ORACLE_HOME
LD_LIBRARY_PATH=$dshome/lib:/.dslibs:$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

Again, the ORACLE_HOME line will need to be added and the LIBRARY_PATH one will already exist and you'll need to add the '$ORACLE_HOME/lib' bit into the midst of it.

All of this is in the README for the server. The documentation for the OCI plugins contains this information as well I believe, all plugin docs are on the server cdrom in pdf format.

-craig

ray.wurlod · Post by **ray.wurlod** » Thu Feb 13, 2003 2:02 pm

A good way to remember this (and that Oracle client software must be installed on the DataStage server machine) is that DataStage when running acts as just another client to the Oracle database.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518