Datastage Errors Encountered

roy · Post by **roy** » Thu Apr 22, 2004 8:29 am

Hi & welcome aboard,
What happens if you try to run this sql via an ORACEL sql editor you use? (toad/sqlplus/sql navigator)?
if it fails contact your DBA, since it's not a DS issue.
if it works (with the same user you run it from DS jobs) then you have a problem to solve and continue this thread.

IHTH,

ray.wurlod · Post by **ray.wurlod** » Thu Apr 22, 2004 3:47 pm

The factr that there's an Oracle error code in this message indicates that DataStage sent the SQL to Oracle via the driver, but Oracle has complained about it. Use oerr -10695 to get more information about exactly what the error is. Was there any further text in the error message, or in a subsequent error message, that you haven't posted?

dennism · Post by **dennism** » Thu Apr 22, 2004 6:54 pm

I can connect to the database thru sqlplus and toad.

this is another error log after the one i've already posted:

JOLoadDfLoadProfile2a..ORA_Lp_raw_max_tabs.LK_ORA_Lp_raw_max_tabs: DSD.BCIClose
SQLSTATE=60, DBMS.CODE=-3113
[DataStage][SQL Client][ODBC][DataDirect][ODBC 20101 driver][Oracle]11604

Can anyone help me to resolve this? thanks

Hi & welcome aboard,
What happens if you try to run this sql via an ORACEL sql editor you use? (toad/sqlplus/sql navigator)?
if it fails contact your DBA, since it's not a DS issue.
if it works (with the same user you run it from DS jobs) then you have a problem to solve and continue this thread.

chulett · Post by **chulett** » Thu Apr 22, 2004 9:15 pm

Silly question perhaps, but why aren't you using the OCI stages to access Oracle?

dennism · Post by **dennism** » Fri Apr 23, 2004 3:40 am

chulett,

the Datastage version we're using does'nt support connection to Oracle 9i thru OCI stage. We're using version 5.2. I think this a limitation of the agreement between our company and Ascential.

thamark · Post by **thamark** » Fri Apr 23, 2004 3:41 am

[img][

quote="dennism"]I can connect to the database thru sqlplus and toad.

this is another error log after the one i've already posted:

JOLoadDfLoadProfile2a..ORA_Lp_raw_max_tabs.LK_ORA_Lp_raw_max_tabs: DSD.BCIClose
SQLSTATE=60, DBMS.CODE=-3113
[DataStage][SQL Client][ODBC][DataDirect][ODBC 20101 driver][Oracle]11604

Can anyone help me to resolve this? thanks

[/img]

Please go thru the bellow link, which could help you to identify the cause of the problem.

http://home.clara.net/dwotton/dba/ora3113.htm

chulett · Post by **chulett** » Fri Apr 23, 2004 6:44 am

dennism wrote:the Datastage version we're using does'nt support connection to Oracle 9i thru OCI stage. We're using version 5.2. I think this a limitation of the agreement between our company and Ascential.

Are you sure? Have you actually tried it? In the past, I've very happily used older versions of DataStage to connect to Oracle 9i databases with the OCI8 stage. All that is required is that you have an 8i client installed on the DataStage server and then point the variables in dsenv to use the 8i client as your ORACLE_HOME. Should work fine. Worked for me!

Certainly easier to figure out what's wrong using OCI rather than ODBC.

dennism · Post by **dennism** » Mon Apr 26, 2004 7:36 pm

chulett,

yes im sure. i've tried it. btw the OCI plugin that comes with our DataStage version is 1.5. and it can connect only to Oracle 7. thanks for the info.....

chulett · Post by **chulett** » Mon Apr 26, 2004 8:31 pm

Ah. You've tried the OCI stage.

In your version, you should have both an OCI and an OCI8 stage. You are correct that the older OCI stage is for Oracle 7.x but you really should have an OCI8 stage that is meant to be used with Oracle 8i. If it's not showing up for you in Designer, I'd guess that the plugin was not selected when the server was installed as it is an option.

It should simply be a matter of rerunning the Server installation and it will offer you the chance to add more plugins. Either that or run the dspackinst utility and point it to the appropriate directory on the server cdrom. Note that because of the limitation of only having one Oracle Home that can be referenced at any one time in your 'dsenv' file, you'd need to decide which one of the two you were going to use and point to the appropriate Oracle Home for the plugin of choice.

It really would be worth your while to investigate this. The OCI8 stage is (as one would think) much more appropriate for use against an Oracle database, much less 'constraining' than the ODBC stage.

dennism · Post by **dennism** » Mon Apr 26, 2004 9:36 pm

ok tnx, ill try to talk to our administrators.