Troubleshooting
Problem
Q Apply may fail to resolve -803 violations when applying certain changes accumulated in spill queue if target has secondary unique index(es) .
Symptom
Q Apply encounters SQLCODE -803 (duplicate) while applying changes from the spill queue after load has completed. Because of this error, Q Apply fails to force the SQL operation at the target. This problem happens only when the target table has secondary unique indexes, constraints, or both in addition to the unique index that is used as the replication key (usually primary key). If the spill queue depth is very high, you might experience a performance problem.
A sample entry in the IBMQREP_EXCEPTIONS table would have column values set as follows:
reason='SQLERROR', sqlcode='-803', operation='INSERT(LOAD)', is_applied = N
Sample entries in Q Apply diagnostic log file are as follows:
<appAgnt::handleRowError> ASN0552E "Q Apply : "ASN2" : "BR00000SP001" : The program encountered an SQL error. The server name is "". The SQL request is "INSERT(LOAD)". The table name is .... The SQLCODE is "-803". The SQLSTATE is "23505". The SQLERRMC is ... The SQLERRP is " ".
...
<spillAgntMain> ASN0589I "Q Apply" : "ASN2" : "BR00000SP001" The program received return code "997" from routine " SPILLQ(GAP mode)".
<spillAgntMain> ASN8999D emptyQ
Cause
Customers who have one or more secondary unique indexes defined at the target and observe SQLCODE -803 when using Q Apply to load the target table for the Q subscription.
Environment
All releases of Q Apply
Diagnosing The Problem
The problem occurs because of a timing issue where the load utility already loads the target table with more recent row data that conflicts with prior row data changes in the spill queue. If the conflict occurs because of incompatible column values for both the replication key and secondary unique indexes, then it results in violations even when the SQL operation is forced by using the replication key. Q Apply keeps retrying the conflicting row change until all older row changes in the spill queue are applied. Because the secondary unique index violations still cannot be resolved, Q Apply follows the error action t hat is defined for the Q subscription and logs the corresponding entry into the IBMQREP_EXCEPTIONS table.
As an example, consider the following scenario:
Source Table T1 has 2 columns C1 and C2
Target Table Targ_T1 has 2 columns C1 and C2, where C1 is primary key and unique index is defined on C2
Rows in T1
Transaction 1: Insert (1,1)
Transaction 2: Delete
Transaction 3: Insert (2,1)
Rows in TARG_T1 because of load
2, 1
Rows in Spillq
Insert (1,1)
delete
Insert (2,1)
When Q Apply applies Row 1 from the spill queue ( Insert 1,1), it gets a SQLCODE -803 on the secondary unique index. Q Apply waits until all prior row changes that could resolve this -803 have already been applied. However, because in this case the secondary unique index violation is not due to older row changes, it fails to resolve it and follows the error action.
Resolving The Problem
Workaround: Before activating a Q subscription that uses initial load, temporarily drop any additional unique indexes on the target table. Once the Q subscription goes to active state on the target side, re-apply the secondary unique indexes.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21590043