Oracle 11g and DataStage 7.5 EE compatible?

Nithinit · Post by **Nithinit** » Fri Jan 08, 2010 6:10 am

I want to know whether DataStage 7.5.2 or 7.5.3 is compatible with Oracle 11g. If possible do we need to apply any patch?? Please help me on this

chulett · Post by **chulett** » Fri Jan 08, 2010 7:45 am

I don't believe it is specifically supported yet but I don't know why it wouldn't be "compatible". Have you tried accessing it yet and had issues or are you simply asking at this point?

srinivas.g · Post by **srinivas.g** » Fri Jan 08, 2010 8:00 am

Both Versions are compatible with oracle 11G
Make sure your DSEnv file.

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Fri Jan 08, 2010 1:10 pm

Are you sure about that? I definitely remember that the 8.0 release notes said that it was the first release to support Oracle 11g. And even then you needed patches.

-Edit- Found the link!

http://www-01.ibm.com/support/docview.w ... wg21373013

Compatibility of Oracle 11g with Information Server DataStage 8.0.1

Question
Is Oracle 11g compatible with Information Server DataStage 8.0.1 for both connectors and plug-ins?

Answer
Oracle 11g has been certified with both the plug-ins and operators on Information Server DataStage 8.0.1.

To use Oracle 11g with the Oracle operators:
1. Connect with Oracle 10g library (using Oracle 10g client)
2. Install patch 130939

To use Oracle 11g with the Oracle plug-ins, no patch is required.

chulett · Post by **chulett** » Fri Jan 08, 2010 3:11 pm

Good to know, I don't recall ever hearing that before this. :D

And while I understand what the "Oracle plug-ins" are, I'm not quite sure exactly what is meant by "Oracle operators" Andy - can you clarify?

Nithinit · Post by **Nithinit** » Wed Jan 13, 2010 3:35 am

I tried Connecting to Oracle 11g. But i was not able to..
My Server version is 7.5.1.

saraswati · Post by **saraswati** » Wed Jan 13, 2010 6:15 am

As far as I remember from what IBM told me Oracle 11g might work with 7.5 versions but its certified to work with 8.0 and up only and hence if you face any issue with 11g and 7.5 IBM wont help us out.

chulett · Post by **chulett** » Wed Jan 13, 2010 7:59 am

'Not able to' is a little nebulous, what exactly happened? Have you tried using a lower client version on the DataStage server? For example, a 10g client to connect to the 11g database?

hsahay · Post by **hsahay** » Tue Jul 20, 2010 3:31 pm

We tried with this setup first -

Datastage 7.5 EE
using
Oracle 10G client
to connect to
Oracle 11G database under 11G RAC

While we were able to connect and while it seemed to work fine for most part, one of our jobs aborted with this error -

"Could not determine version of Oracle for which to partition"

We then tried to use a pure 11G environment by replacing the 10G client with a 11G client and now it does not work at all.

1. One of ours jobs that uses a built in SQLPLUS routine aborted with the following error -
Adj_RunAdjModels_SEQ..JobControl (SQLPLUS1): Error when executing command: echo ' EXECUTE PKG_ADJ_MDL_LOAD.SP_LOAD;'| sqlplus -L prepstguser/*****@prepperf_b.ascap.com
*** Output from command was: ***
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

2. I tried doing VIEW Data in a server job and it failed with this error -

##I TFCN 000001 17:15:19(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5.0.1
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved

##I TUTL 000031 17:15:19(001) <main_program> The open files limit is 4000; raising to 2147483647.
##I TOSH 000002 17:15:19(002) <main_program> orchgeneral: loaded
##I TOSH 000002 17:15:19(003) <main_program> orchsort: loaded
##I TOSH 000002 17:15:19(004) <main_program> orchstats: loaded
##I TFSC 000001 17:15:19(005) <main_program> APT configuration file: /app/product/dsadm/Ascential/DataStage/Configurations/default.apt
##W TCOS 000049 17:15:19(006) <main_program> Parameter specified but not used in flow: _G_DS_DB_INSTANCE
##W TCOS 000049 17:15:19(007) <main_program> Parameter specified but not used in flow: _G_DS_DB_USER_NAME
##W TCOS 000049 17:15:19(008) <main_program> Parameter specified but not used in flow: _G_DS_DB_PASSWORD
##W TCOS 000049 17:15:19(009) <main_program> Parameter specified but not used in flow: DS_DIS_ID
##W TCOS 000049 17:15:19(010) <main_program> Parameter specified but not used in flow: DS_CALC_RUN_CTL_ID
##W TCOS 000049 17:15:19(011) <main_program> Parameter specified but not used in flow: DSProjectMapName
>##E TOSH 000205 17:15:19(012) <main_program> PATH search failure:
>##E TOSH 000000 17:15:19(013) <main_program> Error loading "orchoracle": Could not load "orchoracle": Could not load module /app/product/dsadm/Ascential/DataStage/PXEngine/lib/liborchoracleaix3.so.
> Dependent module /app/product/oracle/DBBASE/11.2/lib/libclntsh.so could not be loaded.
> The module has an invalid magic number.
>Could not load module /app/product/dsadm/Ascential/DataStage/PXEngine/lib/liborchoracleaix3.so.
> Dependent module /app/product/dsadm/Ascential/DataStage/PXEngine/lib/liborchoracleaix3.so could not be loaded.
>##E TOSH 000000 17:15:19(014) <main_program> Could not locate operator definition, wrapper, or Unix command for "oraread"; please check that all needed libraries are preloaded, and check the PATH for the wrappers
>##E TCOS 000029 17:15:19(015) <main_program> Creation of step finished with status = FAILED

So unlessi am doing something obviously wrong that you gentlemen can pinpoint from the error messages above, it looks like 7.5 is no go with 11G.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 20, 2010 3:44 pm

chulett wrote:I'm not quite sure exactly what is meant by "Oracle operators" Andy - can you clarify?

Oracle operators are the Orchestrate operators that deal with Oracle, such as oraread and orawrite. You see these mentioned in the "modules loaded" event in the job log. And in the score, of course.

chulett · Post by **chulett** » Tue Jul 20, 2010 4:36 pm

Gotcha.

hsahay · Post by **hsahay** » Wed Jul 21, 2010 3:18 pm

I got it working.

Datastage 7.5
using
Oracle 11G client
connecting to
Oracle 11G database on 11G RAC
on AIX 5.3

The slew of errors i was getting earlier (see my message above) was simply because the DBAs installed a 64 bit version of the oracle 11G client. We replaced it with the 32 bit version and voila - everything started working.

hsahay · Post by **hsahay** » Tue Aug 03, 2010 12:37 pm

Just wanted to mention that the above configuration (DS 7.5 with oracle 11G) is working for us for the most part but atleast one of our jobs is failing for no apparent reason with this error message -

"Could not determine version of Oracle for which to partition"

It is a parallel job using an enterprise oracle stage with "partition table" option enabled.

There may be other such gotchas awaiting you if you decide to go ahead with 7.5/11 combo.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 03, 2010 3:48 pm

That error about partitioned table may be unrelated to version but related to privileges on eight specific system tables. You can Search DSXchange for details.

BradJoss · Post by **BradJoss** » Fri Oct 01, 2010 3:29 pm

ray.wurlod wrote:That error about partitioned table may be unrelated to version but related to privileges on eight specific system tables. You can Search DSXchange for d ...

I'm working on pretty much the same issue. The difference is that I'm working in DSv8.0.1 and have client 10g installed on the DS server connecting to an 11g Database (11.2.0.1.0 64 bit). Oracle is new to me and I'm using the SCD stage in my job which is new to me. I've had the DBA add all the prevliges that you mentioned Ray as well as setting the various Environmental Variables to handle for Oracles' use of $ in there tablenames. The table that is my lookup to the SCD is not partitioned and yet I still get the same error: "Could not determine the Oracle version for partitioning. [orareadop.C:156". At this time I haven't been able to confirm with the DS Admins if patch 130939 has been installed or not but I find it curious that as soon as the client was switched to 11g (32 bit) things worked ok.

If any one has got this working without having to update the client I'd sure like to here about it.

Thanks,
Brad