DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
techiexavier
Participant



Joined: 28 Feb 2012
Posts: 39
Location: Chennai
Points: 376

Post Posted: Wed Feb 13, 2019 8:34 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi All,

Our parallel job design as below which runs in multiple nodes,

Oracle Connector --> Tr (4 Output links)--> Oracle Connector.

(4 Links out of Transformer and connected to one target Oracle connector).

Transformer and Target Oracle connector runs is sequential mode.
In the Oracle connector link ordering and record ordering for all records are defined.
Target Oracle connector performs delete for a row, update for a row, insert of all records and finally delete for a row in each of its input links.

Our job is failing in deadlock issue in some cases.

Can you please suggest whats the reason for the issue and solution to fix the same.

TIA.
techiexavier
Participant



Joined: 28 Feb 2012
Posts: 39
Location: Chennai
Points: 376

Post Posted: Wed Feb 13, 2019 8:41 pm Reply with quote    Back to top    

Point to add all the target activities are happening on the same table.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

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

Joined: 23 Oct 2002
Posts: 54501
Location: Sydney, Australia
Points: 295558

Post Posted: Wed Feb 13, 2019 9:40 pm Reply with quote    Back to top    

How are your transactions organized? In particular, how many rows per transaction for each of the CRUD operations? If the number of rows per transaction is anything other than 1, you're almost c ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
Rate this response:  
Not yet rated
techiexavier
Participant



Joined: 28 Feb 2012
Posts: 39
Location: Chennai
Points: 376

Post Posted: Wed Feb 13, 2019 9:59 pm Reply with quote    Back to top    

Transformations are like, the links which perform delete and update will have one record and other insert link will have more records.

So totally 3 links will have one record to target and rest one link has more records for insert.
Rate this response:  
Not yet rated
mouthou
Participant



Joined: 04 Jul 2004
Posts: 194

Points: 1881

Post Posted: Thu Feb 14, 2019 1:41 am Reply with quote    Back to top    

as the deadlock error comes out of Oracle, it is pretty obvious that there is same record going for more than one operation at the same time.

For debugging purpose, try removing the one link each time and run the job. you will get to know the link and what combination is still receiving/processing the same record which runs into deadlock. and you can change the design accordingly
Rate this response:  
Not yet rated
anudeepdasari001
Participant



Joined: 14 Dec 2015
Posts: 58
Location: Arizona
Points: 467

Post Posted: Thu Feb 14, 2019 3:33 am Reply with quote    Back to top    

Could you please tell me how huge is the data?

_________________
anudpETL
Rate this response:  
Not yet rated
techiexavier
Participant



Joined: 28 Feb 2012
Posts: 39
Location: Chennai
Points: 376

Post Posted: Sun Feb 17, 2019 7:21 pm Reply with quote    Back to top    

Thanks for the updates.

mouthou,
I have a insert link and other three update/delete links. As per the link ordering and record ordering, these activities need to be performed in sequential order.

Not sure how this is being violated.

anudpETL,
In a batch I am getting around 1 M records.
Rate this response:  
Not yet rated
mouthou
Participant



Joined: 04 Jul 2004
Posts: 194

Points: 1881

Post Posted: Mon Feb 18, 2019 2:27 am Reply with quote    Back to top    

yes you are logically correct as per the link ordering but I am thinking in the lines of duplicate rows getting into one particular link itself. We had such issues with duplicate data getting processed by different partition in the same run. If you have test DB, try to load the data from one link at a time, then you will get more clarity of the data pattern.

In a broader sense, are you sure that there is no other job trying to access the same table and the same row for DML operation around this job runs?
Rate this response:  
Not yet rated
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 417
Location: New York City
Points: 4558

Post Posted: Tue Feb 19, 2019 12:27 pm Reply with quote    Back to top    

...Only wait to avoid the deadlock will be controlling the size of your transactions...I would follow Ray's respond to the point

_________________
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

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

Joined: 23 Oct 2002
Posts: 54501
Location: Sydney, Australia
Points: 295558

Post Posted: Tue Feb 19, 2019 6:34 pm Reply with quote    Back to top    

Partitioning will be important, too. You need to ensure that each key value is processed on only one node, if you're planning to implement parallel connections.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
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