User Defined SQL

zam62 · Post by **zam62** » Fri Sep 16, 2005 9:17 am

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

ArndW · Post by **ArndW** » Fri Sep 16, 2005 10:10 am

zam62,

it would be beneficial if you would tell us what the error message is.

zam62 · Post by **zam62** » Fri Sep 16, 2005 11:32 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Sep 16, 2005 4:22 pm

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.

ian_bennett · Post by **ian_bennett** » Sun Sep 18, 2005 11:27 pm

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.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 19, 2005 1:13 am

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.

ian_bennett · Post by **ian_bennett** » Mon Sep 19, 2005 1:33 am

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.

dls · Post by **dls** » Thu Nov 03, 2005 7:21 am

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.

DSXchange

User Defined SQL

User Defined SQL

Re: User Defined SQL