Sunday, June 8, 2014

Resolving conflicts due to particular sqlstate in DB2 Q Replication

I had a situation when i tried to insert a row in source which was detected as conflict at target so Q Apply program stopped processing, so now if we want that transaction to be ignored we need to tell q apply program to ignore it .

2014-06-08-12.47.57.547884 ASN0552E  "Q Apply" : "ASNTA" : "BR00000AG012" : The program encountered an SQL error. The server name is "". The SQL request is "EXEC". The table name is "run SQL". The SQLCODE is "-668". The SQLSTATE is "57016". The SQLERRMC is "". The SQLERRP is "SQLDTBLR".
2014-06-08-12.52.03.789464 <brwzMain> ASN7524E  "Q Apply" : "ASNTA" : "BR00000" : The Q Apply program encountered an error or conflict for Q subscription  "STAFF0002" (receive queue  "ASNTB.TESTB_TO_ASNTA.TESTA.DATA", replication queue map "TESTB.ASNTB_TO_TESTA.ASNTA"). It stopped reading from the receive queue. The error occurred while applying a row from the transaction corresponding to the LSN "0000:0000:0000:04d6:82d3".

Below are steps the pass to ibmqrep_targets to ignore that transaction for particular sql state.

db2inst1@sairam:~$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

db2inst1@sairam:~$ db2 "update asnta.ibmqrep_targets set oksqlstates='57016' where subname='STAFF0002'"
DB20000I  The SQL command completed successfully.

Then it had successfully ignored that transaction alone and q apply started processing the Receive queue.

No comments:

Post a Comment