DB2 connector stage -911 deadlock error
Moderators: chulett, rschirm, roy
DB2 connector stage -911 deadlock error
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.
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.
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
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.
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.
-
- Participant
- Posts: 11
- Joined: Wed Mar 16, 2011 5:56 am
Re: DB2 connector stage -911 deadlock error
we got deadlocks when doing updates on a table which didn't have an index on the key column
Re: DB2 connector stage -911 deadlock error
- 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
OR
- Perform inserts and updates in 2 different jobs
OR
-Possibly Hash partitioned data before load might help as well in avoiding parallel updates
Rahul
DS Lover
DS Lover