Datastage Update

gayathrisivakumar · Post by **gayathrisivakumar** » Thu Jan 09, 2014 1:53 pm

Hi Gurus,

I have encountered issues while doing an update on one of my DB2 tables. The problem is job gets aborted if the execution mode is set to "Parallel", while doing an update. But when the execution mode is set to "Sequential" update is happening fine.

Below is the error I am getting:

"DB2 reported: SQLSTATE = 40506: Native Error Code = -1,476: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL1476N The current transaction was rolled back because of error "-911". SQLSTATE=40506 ". According to my understanding -911 stands for dead lock.

In fact this is not a problem that I encountered on one particular table, but a couple of tables. Update query is the normal update query

Ex : Update tablename set PROCESSED = ORCHESTRATE.PROCESSED
where ID = ORCHESTRATE.ID ;

Can anyone help me with this?

Thanks,

Mike · Post by **Mike** » Thu Jan 09, 2014 3:27 pm

Parallel updates are prone to deadlocks...

A few things to reduce the likelyhood of deadloacks:
1) Hash partition by your update key (ID in your case) so that multiple updates for the same row go to the same database connection
2) Make sure that you have a current RUNSTATS on the target table to reduce the likelyhood of lock escalation (this did the trick for a recent client that was experiencing deadlocks)
3) Reduce transaction size so that locks are held for a shorter duration
4) Talk to your DB2 DBA about lock escalation and how to prevent or minimize it

Transaction size of 1 and sequential execution mode are probably the only guarantee for avoiding deadlocks and they both come with a performance penalty.

Consider redesigning your application to avoid updates completely.

Mike

gayathrisivakumar · Post by **gayathrisivakumar** » Fri Jan 10, 2014 8:44 am

Thanks Mike.

So do you mean to say that using Updates in DB2 connectors is a not a good design? I will have to redesign, but still not sure if I can avoid updates completely.

But will give a try on the options you suggested.

If anyone else has still more suggestions please share.

Thanks,

soumya5891 · Post by **soumya5891** » Fri Jan 10, 2014 1:21 pm

The deadlock can be happen when a particular record is trying to be getting updated from multiple sessions,which may be the case here as you running the update in parallel.Please check one thing that whether there are any such records which are duplicates on the basis of update key.

Mike · Post by **Mike** » Fri Jan 10, 2014 3:45 pm

Updates in any DBMS will be some orders of magnitude slower than inserts.

It is frequently more performant to truncate and load instead of processing a mix of inserts and updates.

But, it is not always possible or desirable to eliminate updates.

For an example, if you are updating 90% of the rows in a table, then a truncate and reload is going to provide benefits on a large table.

Another example, if you are updating fewer than 1% of the rows, then sticking with updates may be your best option.

The bottom line is ... know your data so you can make educated design decisions.

Mike