Hi All,
I am trying to delete records from a mainframe DB2 table based on a key. I am using sequential execution on DB2 stage so I should see only one session on Db side from ETL.
But somehow, Datastage creating two sessions with same Autogenerated DELETE statement and causing the deadlock on DB2 end.
I verified the other jobs which are executing in sequential mode which are creating only one session. The only difference is, they have user defined SQL statement and my job has AUTOGENERATED sql and causing deadlock.
Question here is, how DB2 connector stage creating multiple sessions on DB2 when I am executing the stage in SEQUENTIAL.
Sequential Execution on Db2 Causing Deadlock
Moderators: chulett, rschirm, roy
Sequential Execution on Db2 Causing Deadlock
--
Swathi Ch
Swathi Ch
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Connector stages typically do create two connections, one an inactive "control" connection, and one to do the actual work.
I've been caught by this in a number of scenarios, and it's not just DB2; I've experienced the same with Netezza.
However the inactive connection ought not to take any locks (it might be used, for example, to get the execution plan for the SQL), so should not be the cause of any deadlocks.
Get your DBA to do some more specific monitoring, particularly to identify the competing processes when a deadlock is detected.
I've been caught by this in a number of scenarios, and it's not just DB2; I've experienced the same with Netezza.
However the inactive connection ought not to take any locks (it might be used, for example, to get the execution plan for the SQL), so should not be the cause of any deadlocks.
Get your DBA to do some more specific monitoring, particularly to identify the competing processes when a deadlock is detected.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.