UPSERT Deadlock

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
iowajag
Participant
Posts: 6
Joined: Sat Feb 28, 2004 11:34 am

UPSERT Deadlock

Post by iowajag »

Anyone please tell, why DS parallel job is keep on running when I select UPSERT method. If I select Load only, it runs fine. Here I am giving update with condition two fields out of 26 of incoming records should match with existing records and also enddate should be greater than system date.
And then insert all incoming record into the Target table. These record datas are coming from CDC with change code = "Edit"/3.

I tried the same logic with small other example, the same case happened.

Appreciate, anyone's information for the problem.

thanks, IOWAJAX
richdhan
Premium Member
Premium Member
Posts: 364
Joined: Thu Feb 12, 2004 12:24 am

Post by richdhan »

Hi,

Iam still not clear what you are trying to convey but I will tell you what we have done maybe it will be of help to you.

For Insert we have used Write Method as Upsert and Upsert Mode as User Defined Update Only and gave the insert script.

For Update we have used Write Method as Upsert and Upsert Mode as User Defined Update Only and gave the update script.

HTH

--Rich


A little bit of ink is powerful than the strongest memory
--Confucius
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Load only and UPSERT are vastly different actions in the database. UPSERT involves DML, where Load uses sqlldr. So, in a nutshell, you're comparing DML loading versus sqlldr, and probably sqlldr DIRECT path.
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
rsrikant
Participant
Posts: 58
Joined: Sat Feb 28, 2004 12:35 am
Location: Silver Spring, MD

Post by rsrikant »

From CDC output the records to a transformer. When the change code is "3" the record should be both updated (probably logical delete or whatever update needs to be done for the existing records) and freshly insert again with new values. So, in the transformer send the insert records and update records into 2 different link outputs.

After that you can do as Rich mentioned in his reply.

For Insert use Write Method as Upsert and Upsert Mode as User Defined Update Only and gave the insert script.

For Update use Write Method as Upsert and Upsert Mode as User Defined Update Only and gave the update script.

Srikanth
Post Reply