DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
abyss



Group memberships:
Premium Members

Joined: 22 May 2014
Posts: 172

Points: 1773

Post Posted: Thu Feb 08, 2018 7:28 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
hi all
i think this is more like a database question rather than a ds question but someone may can help me on here.

i have a job has two sql server stages that update same table and they may update same row, if they do the deadlock will occur. these 2 stages runs very different queries from same source so they can't be merge into one stage.

my question is there a way to wait one stage finish then start another one? (i know it's unlikely...) or i have to put one of them into another job?

thanks
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42530
Location: Denver, CO
Points: 218857

Post Posted: Thu Feb 08, 2018 11:30 am Reply with quote    Back to top    

Can you give some details on the job design, please? I'm unclear if there is one main processing stream that splits to two separate target stages or if there are two distinct processing streams in the same job.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1232

Points: 8099

Post Posted: Thu Feb 08, 2018 11:45 am Reply with quote    Back to top    

Isn't a deadlock only going to happen when you try to update/insert data into a common table?

Not sure why you would craft a job that has two stages to update the same table. How do you account for who has priority? Two updates to the same record is dangerous unless you set a prioritized event.

Why not have one upsert event at the end of your flow and ensure that you control which row of data happens before the other row of data.

data stream #2 is concatenated to data stream #1 and than an upsert to the table.

?!?
Rate this response:  
Not yet rated
abyss



Group memberships:
Premium Members

Joined: 22 May 2014
Posts: 172

Points: 1773

Post Posted: Thu Feb 08, 2018 9:17 pm Reply with quote    Back to top    

this is my job, populate type 6 table
Image

those 2 SE stages update same table, one is type 1 change another is type 2 change.
I had a kind of illegitimate work around: set the array size to 1 to avoid the problem. consider the whole table only have couple thousand rows the performance is acceptable...
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42530
Location: Denver, CO
Points: 218857

Post Posted: Thu Feb 08, 2018 10:45 pm Reply with quote    Back to top    

Not sure why you would consider that an "illegitimate work around", that was the first thing I was going to suggest before I ended up simply asking about the job design.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
abyss



Group memberships:
Premium Members

Joined: 22 May 2014
Posts: 172

Points: 1773

Post Posted: Fri Feb 09, 2018 12:38 am Reply with quote    Back to top    

haha awesome.

then i can mark it as resolved then Very Happy Very Happy Very Happy
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42530
Location: Denver, CO
Points: 218857

Post Posted: Fri Feb 09, 2018 8:36 am Reply with quote    Back to top    

Well... considering how any conversation after that would have involved your data volumes and since you've confirmed it is small and the design is working for you, then yes - sounds like a resolution to me. Wink

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1232

Points: 8099

Post Posted: Fri Feb 09, 2018 10:43 am Reply with quote    Back to top    

But the fundamental topic of which thread has priority is still important IMHO.

But that's for abyss to decide.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42530
Location: Denver, CO
Points: 218857

Post Posted: Fri Feb 09, 2018 11:16 am Reply with quote    Back to top    

Of course.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours