Troubleshooting
Problem
Subscription stops with the following error in the CDCz Jeslog: DTC subname CHC0102E SQL error: DSNT408I SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE INDEX IN INDEX SPACE I024IX01 CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'0008278610* DSNT418I SQLSTATE = 23505 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXRINS SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -110 13172739 0 13814229 -489488384 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF92' X'00C90003' X'00000000' X'00D2C9D5' X'E2D30000' X'00000000' SQL DIAGNOSTIC INFORMATION, Diagnostic information (dtcExcSqlStmt;3,966
Symptom
AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X record-id
Cause
The table that is the object of the insert or update operation is constrained to have unique values in certain columns. Completion of the requested operation would result in duplicate values
Environment
CDCz replication to a target DB2 on z/OS
Diagnosing The Problem
In the CDCz Jeslog log look at the messsage:
DTC subname_PR CHC0428E An INSERT operation failed. Table name: user.tble, key: UNIT_NMBR=12345, NMBR=2365820, DATE='2011-01-01'
The event message right after the -803 has the key and the values for the key. User should use this information to run SELECT against the source and target tables to verify what duplicate values exist for those tables.
User can also obtain a dump of the -803 SQLCODE via DSN1SDMP.
For more information on DSN1SDMP reference:
http://publib.boulder.ibm.com/infocenter/
DB2 Version 9.1 for z/OS>DB2 reference information> DB2 utilities> DB2 stand-alone utilities.
The input statement for the dsn1sdmp would be:
SELECT
* OFFSET TO FIRST DATA SECTION CONTAINING THE SQLCA.
P4,08
* SQLCODE -803
DR,74,X'FFFFFCDD)
/*
Resolving The Problem
Users should identfiy the duplicate records then remove the duplicate record from the "target table".
To continue with replication the recommendation are to:
1. To ensure Source and Target tables are synchronized, users should do a Refresh.
2. Another option is to modify the setting for ENDONERROR in the CHCCFGxx member. ENDONERROR=(YES,YES,YES) is the default, indicating the settings for Refresh, Mirroring, and Auditing, respectively.
Changing the values for one of the settings will cause InfoSphere CDC to continue replication after encountering an error while applying data to the target database during the corresponding type of replication activity.
2a. If the records are not needed, user can perform the following from Management Console.
a. park the table in question from the subscription,
b. Mark Table Capture point for Mirroring.
c. Start Mirroring.
2b. It is IMPORTANT to note that doing this will skip all changes from the last time the table was replicated to now
Product Synonym
cdcz
Was this topic helpful?
Document Information
More support for:
InfoSphere Change Data Capture for z/OS
Software version:
6.2.1, 6.3
Operating system(s):
AIX, z/OS
Document number:
414867
Modified date:
16 June 2018
UID
swg21459407