Page 1 of 1
User Defined SQL
Posted: Fri Sep 16, 2005 9:17 am
by zam62
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
Posted: Fri Sep 16, 2005 10:10 am
by ArndW
zam62,
it would be beneficial if you would tell us what the error message is.
Re: User Defined SQL
Posted: Fri Sep 16, 2005 11:32 am
by zam62
zam62 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
There is no error message...This is all we get, yellow dot and Abnormal termination of stage EBO0000ProductToEDRAdam..x_Product_Map detected
Posted: Fri Sep 16, 2005 4:22 pm
by ray.wurlod
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.
Posted: Sun Sep 18, 2005 11:27 pm
by ian_bennett
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.
Posted: Mon Sep 19, 2005 1:13 am
by ray.wurlod
Same advice to you, Ian, also look at the start up entries to determine the actual SQL statement that was sent to the database to be prepared.
Posted: Mon Sep 19, 2005 1:33 am
by ian_bennett
They do not appear in this case.
I'm guessing the error is occuring during this process.
ray.wurlod wrote:Same advice to you, Ian, also look at the start up entries to determine the actual SQL statement that was sent to the database to be prepared.
Posted: Thu Nov 03, 2005 7:21 am
by dls
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.