Oracle connection

[samarvind]

Hi All,

We have faced a strange error in DataStage at the target Oracle stage saying "ORA-03135 Oracle Connection Lost".

My parallel job design has source file input -> transformations-> Write to Oracle

Since the data volume is so huge it takes minimum 2 hours to complete and atleast takes 1.5hrs to do the transformation and write the first row to Oracle. But then when it writes to Oracle after 1.5 hrs it throws a connection time out error from Oracle side. Tried it with 10 rows it worked fine.

Doesn't Data Stage establish connection only when it reaches respective stage to process records or Does Data Stage establishes connection across all the stages like ODBC or Oracle at the start itself?

Have anybody come across such issue?

We are using DS 8.1.

Thanks
Sam A

[chulett]

Doesn't Data Stage establish connection only when it reaches respective stage to process records or Does Data Stage establishes connection across all the stages like ODBC or Oracle at the start itself?

All connections are established when the job starts and the stages "open" or initialize. What is your idle timeout on the Oracle side? Sounds like you may need to bump it up.

[samarvind]

Thanks Chulet for your quick response. I didn't notice that it establishes connection at the start. I may have to request DBA to bump it up.

However, just to understand, when I worked on Server jobs I don't think I have faced such issues. Opening connections at the start, Isn't it a overhead for Data Stage as well Oracle when processing huge volume which takes more time to complete?

Are there any option to set at Data Stage side of things to open it only when the row movement is active for that stage?

[chulett]

Server jobs work the exact same way and no, there's no such option. Do verify with your DBA that your issue is in fact an idle timeout issue and not something else.