DB2 Connector - Issue in updating the table
Posted: Mon Nov 02, 2015 9:19 am
Experts,
We are migrating jobs from v7.5 to v9.1.
Job Design in v7.5
---------------------
update Dataset ---> Transformer ----> DB2 PX Stage
Job Design in v9.1
---------------------
update Dataset ---> Transformer ----> DB2 connector Stage
(we updraged the DB2 stage to Connector in v9.1)
Update Statement:
UPDATE SCHEMA.TABLE_NAME SET END_TP=?,UPDT_TP=? WHERE CASE_NUM=? AND END_TP='9999-12-31-23.59.59.999999';
the job is running fine in v7.5 and the job is failing in v9.1 due to the below error.. both the version jobs are pointing to same db2 database. (we are taking backup and restoring original backup evertime we run the jobs from v7.5 and v9.1)..
Fatal Error: Fineos_Clm_Taskupd,5: SQLExecute reported: SQLSTATE = 40506: Native Error Code = -1,476: Msg = [IBM][CLI Driver][DB2/AIX64] SQL1476N The current transaction was rolled back because of error "-911". SQLSTATE=40506
(CC_DB2DBStatement::processRowStatusArrayUserSQL, file CC_DB2DBStatement.cpp, line 3,451)
i researched online and found this is due to the current update SQL query waiting for the previous update SQL query for long time for locking the table for the update.. and since it is waiting for long time and reaches the timeout limit.. the DS v9.1 job is failing.
the columns used in WHERE clause is not a index column.. so we added INDEX for those columns and ran the job.. it worked.
tried with Hash partioning on the key column / switched off auto commit..tried to run it in sequential mode.. nothing is working..and we are in a situation were we cannot add INDEX to this table in any higher environemnt(qa, production..).. so is there another solution for fixing this problem ?
We are migrating jobs from v7.5 to v9.1.
Job Design in v7.5
---------------------
update Dataset ---> Transformer ----> DB2 PX Stage
Job Design in v9.1
---------------------
update Dataset ---> Transformer ----> DB2 connector Stage
(we updraged the DB2 stage to Connector in v9.1)
Update Statement:
UPDATE SCHEMA.TABLE_NAME SET END_TP=?,UPDT_TP=? WHERE CASE_NUM=? AND END_TP='9999-12-31-23.59.59.999999';
the job is running fine in v7.5 and the job is failing in v9.1 due to the below error.. both the version jobs are pointing to same db2 database. (we are taking backup and restoring original backup evertime we run the jobs from v7.5 and v9.1)..
Fatal Error: Fineos_Clm_Taskupd,5: SQLExecute reported: SQLSTATE = 40506: Native Error Code = -1,476: Msg = [IBM][CLI Driver][DB2/AIX64] SQL1476N The current transaction was rolled back because of error "-911". SQLSTATE=40506
(CC_DB2DBStatement::processRowStatusArrayUserSQL, file CC_DB2DBStatement.cpp, line 3,451)
i researched online and found this is due to the current update SQL query waiting for the previous update SQL query for long time for locking the table for the update.. and since it is waiting for long time and reaches the timeout limit.. the DS v9.1 job is failing.
the columns used in WHERE clause is not a index column.. so we added INDEX for those columns and ran the job.. it worked.
tried with Hash partioning on the key column / switched off auto commit..tried to run it in sequential mode.. nothing is working..and we are in a situation were we cannot add INDEX to this table in any higher environemnt(qa, production..).. so is there another solution for fixing this problem ?