SPT oracle or ODBC?

vpauls · Post by **vpauls** » Thu Mar 29, 2007 12:37 am

Hello,

I'm wondering about the Stored procedure stage in the database palette of DataStage. We are using Oracle as database for our datawarehouse solution and are wondering if we can make use of this stage?

In other words, are the stage using an ODBC connection to connect to the database or is it using Oracles own drivers?

For us to be able to use it it has to use oracle, not ODBC.

Anyone who can spread any light on this issue? Can't find it in the documentation.

Thanks beforehand

/Elin

vpauls · Post by **vpauls** » Thu Mar 29, 2007 2:48 am

Another question regarding the same topic, to import the stored procedure definitions, it seems like i must use ODBC, why can-t I do it the same way as with the tables, using the ORACCLI or whatever it is called?

Or am I missing something here?

BR
/Elin

chulett · Post by **chulett** » Thu Mar 29, 2007 7:18 am

The Stored Procedure stage when set to 'Oracle' will use the Oracle client on your DataStage server, not ODBC. This also implies you must have configured DataStage for Oracle and be able to use the 'OCI' stages.

Have you? Can you?

vpauls · Post by **vpauls** » Thu Mar 29, 2007 7:24 am

Yes I have and I can when importing table metadata, but when trying to import stored procedure definitions it wants to have a dns and it isn't provided from the server.

/Elin

Thanks for your answer!!

chulett · Post by **chulett** » Thu Mar 29, 2007 7:44 am

"It isn't provided from the server"? In what sense?

If you want to import the metadata, then you will need to setup an ODBC connection to your Oracle database, as that is what it uses. However, there's no requirement that you do this step, it just... helps... as it gives you something to load into the stage. If you know what your SP needs, just setup that information in the stage.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 29, 2007 3:20 pm

vpauls wrote:Yes I have and I can when importing table metadata, but when trying to import stored procedure definitions it wants to have a dns and it isn't provided from the server.

/Elin

Thanks for your answer!!

Do you mean DNS (domain name server) or DSN (data source name)?

vpauls · Post by **vpauls** » Fri Mar 30, 2007 1:46 am

I mean DSN, sorry.

When I try to import the datadefinitions I get an import window that among other things asks for a DSN in a pull down box. When I try to open the box to choose DSN I get the error message: "Empty DSN: list returned from host XXX.XXX.XXX.XXX".

I assume that this is because I haven't set up ODBC on the server?

ray.wurlod · Post by **ray.wurlod** » Fri Mar 30, 2007 5:52 am

Almost certainly.

The DataStage server is the client for the database. It is the DataStage server that must establish the ODBC connection, and therefore you must set up your DSNs on the server.

This involves two files on UNIX machines: .odbc.ini in the DataStage engine directory (usually DSEngine), and uvodbc.config in the project. The first is where you actually configure the DSNs, the second is where you specify the list of DSNs to which you want to connect from that particular project.

(On Windows servers, ODBC.INI is a Registry entry, but uvodbc.config still exists in each project.)

chulett · Post by **chulett** » Fri Mar 30, 2007 7:10 am

Skip that step.

chulett · Post by **chulett** » Fri Mar 30, 2007 12:09 pm

I just mean that the import of SP metadata isn't absolutely required for use by the STP stage.