DSXchange

Hi

we have to Insert/Update/or Use Stored proc in oracle from datastage but ODBC to Oracle is not supported right now .Can we do through basic program ? or shell script or is there any alternative for ODBC connectivy ?


Thanks for your time and consideration

Sairam

Have you tried OCI which is a native connection?

I believe ODBC or OCI are the only 2 ways to talk with ORACLE. Even if u want to use DS BASIC or shell script to read the data, they will be using the DSN you create using ODBC or native drivers ...

Care to share why ODBC is not option for you.

Sairam wrote:we have to Insert/Update/or Use Stored proc in oracle from datastage but ODBC to Oracle is not supported right now .Can we do through basic program ? or shell script or is there any alternative for ODBC connectivy ?

You could always prepare a file of inserts and use a command stage/API call to execute sqlldr. You also could prepare an updates file and sqlldr that into a work table, then invoke a sqlplus command to merge or update the target from that work table.

If you're using PX, why would you attempt ODBC when PX works thru OCI?

Hi Guys
u r responses appreciated


The Documment provided to me explains that
ODBC Oracle Connection:Not Supported

I need ur suggestions on how to call these Stored procs /insert/Update
without this ODBC Connectivity .

Is there any Alternative soln if using PX 7.0

Thanks for u r time

Please answer these questions:

1. What Unix version are you on?

2. What version of Oracle?

3. Why are you not using native OCI?

4. Why do you think you need ODBC?

Hi Keenth,
The OS :AIX 5.1
The DB:Oracle 9i

Oracle Connectivity
1. Native Oracle Connection - TNS entry required for the requested Oracle instance. Oracle userid with the proper permissions for the Oracle database is required
2. ODBC Oracle Connection - ODBC to Oracle is not supported at this time.

This is the Document I have been given

Since we r at the starting point we want to know how to acess the Stored Proc or an Oracle util functions thru DataStage 7.0

Thanks

You still haven't answered all of my questions:

3. Why are you not using native OCI?

4. Why do you think you need ODBC?

5. Your desire for stored procedure usage, is it to run a stand-alone stored procedure to do something, or to return streaming data into DataStage? The first should be handled thru a command execution to sqlpus, the second is supported in the OCI stage.

Hi Kenneth ,

Sorry to bother you guys..

If I use the OCI stage could you please explain the process to do that

Thanks

From my own (recent) experiences, if you are planning on inserting large amounts of data into Oracle, create text files and use the Oracle SQLLDR, using this I was able to cut hours from my datastage jobs, this includes the time it takes for Oracle to handle the import.

Sairam wrote:If I use the OCI stage could you please explain the process to do that

Go get your DBA and make sure that the appropriate Oracle client software is installed on your DataStage server. Then, read the Server installation instructions and make the appropriate changes to the dsenv file. Then, read the Parallel installation instructions and make the appropriate changes. Have your Unix administrator recycle the DataStage engine.

Now, you should be able to use the OCI plugin stages on your jobs to connect to Oracle instances.

In case you don't know the difference between ODBC and OCI, ODBC is Microsofts vindictiveness to the world. It is a generic driver to talk to databases. OCI is like ODBC, except it comes directly from the database vendor and is the high performance and preferred solution for communicating to that database.

Sairam wrote:we have to Insert/Update/or Use Stored proc in oracle from datastage but ODBC to Oracle is not supported right now .Can we do through basic program ? or shell script or is there any alternative for ODBC connectivy ?

Calling Stored Procedures on Oracle is not formally supported under PX. You COULD call a Server job, or use a Server-based shared container to do the dirty work.

You can also do a Before/After job routine to call sqlplus to run the procedures. Do a search on this forum.

-T.J.

Thanks a lot guys

I sincerely appreciate your help

Sairam