DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
DS_FocusGroup



Group memberships:
Premium Members

Joined: 15 Jul 2007
Posts: 197
Location: Prague
Points: 2056

Post Posted: Sun May 23, 2010 7:22 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Windows
Hi,

We've suddenly run into this major issue where all of our production jobs that have an Oracle Stage in the source are failing with the following error:

<Stage Name>,0: (aptoci.C:355). Message: ORA-00022: invalid session ID; access denied

....
<Stage Name>,0: (aptoci.C:355). Message: ORA-03113: end-of-file on communication channel
(aptoci.C:491). Message: ORA-03114: not connected to ORACLE


Job Design:
Oracle Enterprise Stage -> Transformer -> Teradata Connector Stage

Observations:
1) The problem seems to be only with the Oracle stage as there are other jobs where the source is a flat file and these are successfully loading into the target Teradata table.

2) The error is not specific to one particular DataStage project as I have tested these jobs across multiple environments (dev, tst, prod) with same results.

3) The jobs were running fine till end of last week and I haven't been able to come up with any explanation as to what's caused this to occur. To my knowledge, nothing has changed on either the DataStage server or at the Oracle end.

4)I'm able to connect to Oracle from the ETL server itself using the other client tools (SQL Plus) and also able to do a TNSPING, so this does not seem to be a connectivity issue b/w the servers.

5) Another observation that has me stumped is the inconsistency of the behaviour - I can browse the data using the "View Data" option in the Oracle Enterprise Stage, but when I execute the same job (using the same connection parameters), it fails with the aforementioned error!

Are there any environment variable settings in specific that I need to check?

Any ideas or suggestions are most welcome!
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 39139
Location: Denver, CO
Points: 199869

Post Posted: Sun May 23, 2010 7:43 am Reply with quote    Back to top    

Let's see... by the numbers. 1) Good, that helps narrow things down. 2) Good, that lets us know that it is probably Oracle. 3) Keep looking. Obviously, something has changed. 4 ...

_________________
-craig

If you try and take a cat apart to see how it works, the first thing you have on your hands is a non-working cat. -- Douglas Adams
Rate this response:  
Not yet rated
DS_FocusGroup



Group memberships:
Premium Members

Joined: 15 Jul 2007
Posts: 197
Location: Prague
Points: 2056

Post Posted: Sun May 23, 2010 11:08 am Reply with quote    Back to top    

Thanks Craig. The Oracle Stage is being used as a source so not many options/operations to play with.

As far as getting the DBA onboard is concerned, tried that but didn't get much out of it - "Since you can connect from your ETL server to Oracle using the same user ID albeit a different application (SQL Plus), this is not an oracle-related issue and is specific to your application's configuration (DataStage)". Not sure how what exactly I should have him check...

I've also tried using explicit values for the connection params (Server, User & Pass) instead of parameter-set in the original job to discount any issues with the latter (in case the value file somehow got corrupted). Didn't work Confused

What's really got me stumped is that some of the jobs run OK in one instance and rerunning the same job with same settings causes the reported error! Doesn't make sense!

Any system-level env variables I need to be aware of?
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 39139
Location: Denver, CO
Points: 199869

Post Posted: Sun May 23, 2010 12:24 pm Reply with quote    Back to top    

DS_FocusGroup wrote: As far as getting the DBA onboard is concerned, tried that but didn't get much out of it - "Since you can connect from your ETL server to Oracle using the same user ID albeit ...

_________________
-craig

If you try and take a cat apart to see how it works, the first thing you have on your hands is a non-working cat. -- Douglas Adams
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours