DSXchange

Hi,

I'm trying to insert and update into SQL server table through ODBC stage.
I'm getting the below error. Pls help me to resolve it.

Error 1:
ODBC_Connector_21,0: Execute failed on statement INSERT INTO
tblBloombergMonthly( ID_CUSIP , MTG_FACTOR , MTG_FACTOR_DT , MTG_EST_ACC_RT,UserID)
VALUES
(? ,? ,? ,?,?)

Error 2:
ODBC_Connector_21,0: ODBC function "SQLExecute" reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (CC_OdbcDBStatement::executeInsert, file CC_OdbcDBStatement.cpp, line 673)

I tried re run the job and tried inserting and updating separately.But still getting the same error.

... and does it work if you run the job on a single node?

chulett wrote:... and does it work if you run the job on a single node?

I'm able to run in single node. But i suppose to run in 4 nodes

ajithaselvan wrote:

chulett wrote:... and does it work if you run the job on a single node?

I'm able to run in single node. But i suppose to run in 4 nodes

I had the same issue months ago and I solved it by adding index on the column of the Update "where" clause.

You should also look at your partitioning, 'Auto' is not your friend at times like this. Typically one would hash partition over the key(s) used in the target.

The problem is that there is no index on the where clause columns, and keep in mind the order of the columns makes a difference. By that I mean that the order of the columns in the index needs to match the order of the columns in the where clause.
What happens is that a job when trying to update will try to place a table lock if there is no index and if another process already has a row locked then it cannot get exclusive access to place the table lock.

With an index it does not use a table lock.
I have had the same problems. I had to review all the developers jobs and make sure the appropriate indexs were there.