We have User Define SQL tht was a copy/paste from the Update Existing/Insert New Action on the ORacle OCI 9i Stage. When we run it as User Defined, the job aborts and when we run with Update Existing/Insert New it runs to success.
Has anyone else had or seen this issue?
We are on DS 6.0r4
User Defined SQL
Moderators: chulett, rschirm, roy
zam62,
it would be beneficial if you would tell us what the error message is.
it would be beneficial if you would tell us what the error message is.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: User Defined SQL
There is no error message...This is all we get, yellow dot and Abnormal termination of stage EBO0000ProductToEDRAdam..x_Product_Map detectedzam62 wrote:We have User Define SQL tht was a copy/paste from the Update Existing/Insert New Action on the ORacle OCI 9i Stage. When we run it as User Defined, the job aborts and when we run with Update Existing/Insert New it runs to success.
Has anyone else had or seen this issue?
We are on DS 6.0r4
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In Director reset the job and read the "from previous run..." message.
Also check the logged event that shows the actual SQL that is used; make sure that it is the same as you believe you have pasted in.
Also check the logged event that shows the actual SQL that is used; make sure that it is the same as you believe you have pasted in.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 15
- Joined: Sun Sep 01, 2002 6:56 pm
- Location: Australia
- Contact:
I am having the same problem, but in my case it may be due to using a bit more complex SQL (i.e. contain subqueries).
If I run either the UPDATE or INSERT statement by themselves it runs fine but put them together and I get the abnormal termination.
Error is DataStage Job 2057 Phantom 11843.
Doesnt seem to even get to the point of actually trying to run the SQL.
Only thing I can think of is the SELECT statements somehow throw it out.
BTW am using Oracle 8 OCI against Oracle 9.2 database.
If I run either the UPDATE or INSERT statement by themselves it runs fine but put them together and I get the abnormal termination.
Error is DataStage Job 2057 Phantom 11843.
Doesnt seem to even get to the point of actually trying to run the SQL.
Only thing I can think of is the SELECT statements somehow throw it out.
BTW am using Oracle 8 OCI against Oracle 9.2 database.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 15
- Joined: Sun Sep 01, 2002 6:56 pm
- Location: Australia
- Contact:
Searching the forum, it turns out that this thread closely depicts my circumstances.
I.e.,
DS v6.0
User-defined SQL for update existing/insert new action in OCI 9i stage. UPDATE or INSERT, by themselves, run OK. Together, result in 'Abnormal termination of stage........detected' warning and termination of job.
No 'From previous run.....' log entry after reset of job.
No unexpected NULLS in source data.
No startup entries with actual SQL appear in Director log.
No additional information in &PH& files.
I'm redesigning the job to segregate updates and inserts up front, but I'd really like to debug and understand this problem.
Any additonal ideas/thoughts will be appreciated.
I.e.,
DS v6.0
User-defined SQL for update existing/insert new action in OCI 9i stage. UPDATE or INSERT, by themselves, run OK. Together, result in 'Abnormal termination of stage........detected' warning and termination of job.
No 'From previous run.....' log entry after reset of job.
No unexpected NULLS in source data.
No startup entries with actual SQL appear in Director log.
No additional information in &PH& files.
I'm redesigning the job to segregate updates and inserts up front, but I'd really like to debug and understand this problem.
Any additonal ideas/thoughts will be appreciated.