Has anybody come across similar problem?
Several of our production DataStage jobs abort when extracting data from Oracle tables. There is no meaningful error message. The abort happens once the extracts reaches a certain number of rows (about 15.7 million). It does not matter if the extract is to a hash file, sequential file, or another table. it also does not matter how many columns are extracted (I run the query with one column only and it aborted). The resulting file is much less than 2 GIG limit.
This started happening when we migrated the jobs from DS6.0 to DS7.5.1. At the same time we also moved the jobs to a new server.
The same jobs run fine in DS6.0 and on the old server using the same data base and tables.
I have created a ticket with IBM for this issue. After multiple tests and exchange of information their suggestion was that we have a memory leak in Oracle client. However, the same problem appears when the jobs run on the development server with DS7.5.1 using production volume of data.
The error message from &PH& file is:
[/datastage01/Ascential/DataStage/Projects/ARS/&PH&]cat DSD.StageRun_42964_13800
DataStage Job 458 Phantom 22438
kgefec: fatal error Abnormal termination of DataStage.
Fault type is 11. Layer type is BASIC run machine.
Fault occurred in BASIC program DSD.StageRun at address 1500.0
Abnormal termination of DataStage - OCI8 stage
Moderators: chulett, rschirm, roy
Unfortunately, I'm going to agree with IBM/Ascential. Can you possible try to use the 9i client with an OCI9 stage? The 8i client was problematic, from my own experiences with large volumes over long runtimes.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Thank you. Is it this true for the combination of DS7.5 and OCI8? It runs fine with OCI8 and DS6.0.kcbland wrote:Unfortunately, I'm going to agree with IBM/Ascential. Can you possible try to use the 9i client with an OCI9 stage? The 8i client was problematic, from my own experiences with large volumes over long runtimes.
We are running Oracle 8i, so switching to OCI9 is not really an option.
I know I can split the query into two, but that means re-desing of the impacted jobs. Does anybody have any other suggestions?
Not true. The 9i client is backwards compatible to 8 and forwards to 10. The 8i client is the one that works with 7, 8, and 9. You just need to use the OCI stage with the 9i client. It works just fine. In fact, I've been talking to someone at MCI about his travels thru the Oracle client and he's using 9 instead of 10 to talk to 10 database. The 9 client seems to be fairly stable.bozenna wrote:We are running Oracle 8i, so switching to OCI9 is not really an option.
As for other options, you should avoid long running queries as they use a lot of resources and hold a lot of data in rollback. Consider breaking your query into partitioned ranges and use job instances to simultaneous spool those ranged data sets. Not only will you get the data out faster, you'll be more friendly to the system.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Re: Abnormal termination of DataStage - OCI8 stage
What server are you running on and which OS ? We run Solaris 8 with DS7.51 and we extract using OCI8 for Oracle ; biggest table we extract has more than 50 million records and we have no problems.bozenna wrote:Has anybody come across similar problem?
Several of our production DataStage jobs abort when extracting data from Oracle tables. There is no meaningful error message. The abort happens once the extracts reaches a certain number of rows (about 15.7 million). It does not matter if the extract is to a hash file, sequential file, or another table. it also does not matter how many columns are extracted (I run the query with one column only and it aborted). The resulting file is much less than 2 GIG limit.
This started happening when we migrated the jobs from DS6.0 to DS7.5.1. At the same time we also moved the jobs to a new server.
The same jobs run fine in DS6.0 and on the old server using the same data base and tables.
I have created a ticket with IBM for this issue. After multiple tests and exchange of information their suggestion was that we have a memory leak in Oracle client. However, the same problem appears when the jobs run on the development server with DS7.5.1 using production volume of data.
The error message from &PH& file is:
[/datastage01/Ascential/DataStage/Projects/ARS/&PH&]cat DSD.StageRun_42964_13800
DataStage Job 458 Phantom 22438
kgefec: fatal error Abnormal termination of DataStage.
Fault type is 11. Layer type is BASIC run machine.
Fault occurred in BASIC program DSD.StageRun at address 1500.0
Have you tried to see at which point your job would fail and if it always fail at that same point.For instance,try to extract 1 million and increase the number gradually. If that is the case then it's really likely it's memory leak somewhere.