Regarding Oracle 10g

Zabeerulla · Post by **Zabeerulla** » Wed Apr 26, 2006 6:53 am

Hi,

Can we use DRS Stage of DataStage 7.5.1 to connect Oracle 10g?

Or we have to use only Oralce Stage to connect Oracle 10g in DS 7.5.1.

ArndW · Post by **ArndW** » Wed Apr 26, 2006 7:04 am

DRS, ODBC and the Oracle stages can all connect to an Oracle database.

chulett · Post by **chulett** » Wed Apr 26, 2006 7:06 am

Zabeerulla wrote:Can we use DRS Stage of DataStage 7.5.1 to connect Oracle 10g?

What happens when you try? Answer should be 'yes' but I haven't seen a need to use the DRS stage yet, so...

kcbland · Post by **kcbland** » Wed Apr 26, 2006 7:11 am

I believe you wlil use Oracle 9i client with the DRS stage to point to 8, 9, and 10 databases.

Zabeerulla · Post by **Zabeerulla** » Wed Apr 26, 2006 7:14 am

Now we are using MS SQL Server 2000 and DataStage 7.1. We are using DRS stage for to connect to SQL Server tables.

We are going to upgrade to DS 7.5.1 and migrate to Oracle 10g. Whether we can continue to use the same DRS stage by changing the DBMS name from SQL Server to Oracle or we to replace all the DRS Stages with Oracle OCI stage. Please help.

kcbland · Post by **kcbland** » Wed Apr 26, 2006 7:34 am

The "theory" is that all you need to do is "adjust" the DRS stage to Oracle via the drop down on the stage.

But the "practice" is that you have to re-write any custom SQL into Oracle syntax. You'll also seriously consider re-importing all metadata using Oracle's plugin so that the correct data types are there. Dynamic SQL is generated with optional functions based on the datatypes, so you need to be aware of this.

In moving from SQL-Server to Oracle you will need to adjust datatypes within the data model because of incompatibilities. You'll have to reflect those changes within the DataStage jobs. Oracle has a large text field limited to 4000 characters, so a SQL-Server TEXT value can't convert straight away. Using a CLOB has severe limitations and may not be the choice to make. Your decisions could change the data model, and once you do that the changes to the jobs are more severe.

The panacea of just switching a value in the stage is never the case.

Zabeerulla · Post by **Zabeerulla** » Wed Apr 26, 2006 8:23 am

For connecting Oracle whether I need to edit the following three files.

dsenv
.odbc.oni
uvodbc.config

kcbland · Post by **kcbland** » Wed Apr 26, 2006 8:42 am

None, ODBC connections are managed thru the ODBC icon in Control Panel. OCI connections are managed via Oracle client installation.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 26, 2006 4:42 pm

I suspect Zabeerulla is on UNIX and is paying the price for hijacking a thread about a problem on Windows.

If on UNIX then you do need to edit the three files indicated.

kcbland · Post by **kcbland** » Wed Apr 26, 2006 5:42 pm

ray.wurlod wrote:I suspect Zabeerulla is on UNIX and is paying the price for hijacking a thread about a problem on Windows.

If on UNIX then you do need to edit the three files indicated.

Nope, they're the original poster and indicated Windoze.

DSXchange

Regarding Oracle 10g

Regarding Oracle 10g

Re: Regarding Oracle 10g