Sequential Execution on Db2 Causing Deadlock

[SwathiCh]

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.

[ray.wurlod]

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.

[SwathiCh]

Thank you Ray.

Any idea why it is creating two sessions only with AUTOGENERATED sql option. If I use user defined sql, we are seeing only one session on DB side.