DB2 connector stage -911 deadlock error

[Marley777]

Hi, thanks for reading.

Getting the following error. Using a DB2 connector stage.

db2IIW_Bus_Key_Xref_OUT,5: DB2 information: SQLSTATE = 40506: Native Error Code = -1476: Msg = [IBM][CLI Driver][DB2/AIX64] SQL1476N The current transaction was rolled back because of error "-911". SQLSTATE=40506

I have searched the forum and found the issue can be resolved by switching the stage to run in sequential mode instead of parallel. However I really don't want to switch the stage from Parallel to Sequential because I want to maximize performance. Can anyone offer another solution. Working with the DBA's but they are not finding anything on their end. I'm using a db2 connector stage. Write mode is insert then update. Transaction record count is 2000. Auto commit is off.

[jwiles]

Have you partitioned your data prior to loading to DB2?

Regards,

[Marley777]

We link sort/partition in a slowly changing dimension stage then the data continues to an aggregator and a transformer before loading via a db2 connector stage. The aggregator and transformer are set to "auto" for partititioning.

[jwiles]

Try changing the partitioning on the input of the DB2 Connector to something other than auto: DB2 or DB2 Connector. You may also consider reducing the array size (default is 2000).

Also, if you can identify the inserts vs updates prior to loading, you can load those in separate jobs...run all inserts first, then the updates.

Regards,

[Marley777]

do I change the array size and the record count. What's the difference? Do they always have to match?

[Marley777]

Let me answer my own question here.

The array count and record count don't have to match but the record count value must be a multiple of array size.

Record Count is the commit level. The number of records to process before the connector commits the current transaction.

Array Size is the maximum number of rows to transfer in each read or write operation.

I find this confusing...It'a as if 'record count' and 'arrya size' are the same thing. Number of records to process and number of rows to transfer...kinda the same thing can someone please offer an example to help me understand.

[RikDeclercq]

we got deadlocks when doing updates on a table which didn't have an index on the key column

[qt_ky]

Review my suggestions from this topic...

viewtopic.php?t=145389

[rahcomp]

- better to run updates if any, in serial mode
OR
- Perform inserts and updates in 2 different jobs
OR
-Possibly Hash partitioned data before load might help as well in avoiding parallel updates