error loading DB2 table using ODBC stage

[ds_infy]

Hi,

I have a requirement to update a Db2 table, i am using ODBC stage to perform this update, I need to handle rejects and so i am using ODBC stage to do it as this stage can capture the rejects,

When trying to update, the job fails with below error message

APT_CombinedOperatorController(2),0: Fatal Error: [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]FAILED EXECUTION DUE TO DEADLOCK/TIMEOUT. REASON 00C90088 TYPE 00000302. DBUYNG03.SBUYTTM .X'00006A'

I have validated that there are no duplicates(based on table's key columns)
in the input data which will get updated.

If the execution mode is changed to Sequential from Default(parallel), the job completes fine, Is there any reason for this issue?

There are no other jobs that are accessing the table which may result in a deadlock. Kindly help to understand the reason for the failure when executed job in Parallel mode.

[chulett]

How are you controlling the partitioning? Do an exact search here for "deadlock" to find many conversations on this subject.

[qt_ky]

In parallel mode you may have default settings for the commit count, so you're stomping on your own foot...

[kandyshandy]

Can you share the update statement that is being executed? You may have to modify your update statement...

[kandyshandy]

For e.g. you can't have the below update SQL in parallel mode.

Assume table X has 100000 records with COL2 value as ' yy'.

Code: Select all

UPDATE X set COL1= 'zz' where COL2 = 'yy'

This is a sample scenario where you face DEADLOCK.

To overcome this, pass the primary key as well to your UPDATE statement.

Code: Select all

UPDATE X set COL1 = 'zz' where COL2 = 'yy' and PRIMARYKEY = incomingPRIMARYKEY [i.e. :1 or orchestrate.primarykeyfieldname or :? etc]

[ray.wurlod]

You can if the data are partitioned on COL2.