There are various approaches to correcting duplicate key fields.1. If the duplicate unique index keys resulted from the fact that an
inactive redundant unique index was
activated, then the data are valid. De-activate this unique index or consider removing it from the database schema entirely.
2. Otherwise, before removing these records from the database, first verify that the
duplicate keys found also refer to
duplicate records. In other words that the non-unique indexed fields in the record are also identical:
To verify the 1127 record content:1. The first step in this process is to compare the duplicate records to determine if they are truly duplicates or if only the indexed field(s) have duplicate values.
Refer to Article
Does error 1127 report the RECID of the duplicated record or the original RECID? Use the RECID in the 1127 error to dump the record content. For example:
OUTPUT TO table.<recid>.out
FIND <table> WHERE RECID(<table>)=<recid>.
// DISPLAY RECID(<Table Name>) <Indexed field name>.
EXPORT <table>.
OUTPUT CLOSE.
To find the correlating duplicate record:The dumped 1127 record content needs to be reviewed in order to
construct a new ABL query based on the field values of the record, specifically the unique field values for which an index does exist due to the unique key violation. These can then be compared to verify that the records are identical or not and next decisions can be taken to either delete or re-instate the record content.
The output associated with the <indexed field name> can be used to find the original records RECID:
OUTPUT TO table.<recid>.out
FOR EACH <Table Name> WHERE <Indexed field name> = <indexed field name value from above> use-index <non-unique index>:
// DISPLAY RECID(<Table Name>) <Indexed field name>.
EXPORT <table>/
END.
OUTPUT CLOSE.
If the records are not duplicates but just have duplicate indexed field values determine whether to create a new record with the non-indexed values. Regardless, the next step is to delete the duplicate record with the IDXFIX utility.
To delete the duplicate records:OPTION 1: Delete the duplicate recordSince the 1127 message lists the
duplicate keys encountered, not the
non-duplicates which are indexed. The IDXFIX utility can be run online or offline to delete the duplicate entry. The RECID specified for the Storage Area and all it's indexes will be deleted from the database
$ PROUTIL <dbname> -C idxfix
For more details on usage of idxfix see these articles:
The Storage Area number for the table can be found in the same IDXBUILD output that reports the 1127 errors, in the preceding Phase 1 which scans the area.
Example:
Phase 1, processing block number 320384 of 320502 in area 23. (8367)
The Storage Area number for the table can also be found with ABL using
_StorageObject and
_File When there are many 1127 duplicates to delete, these can be provided in a script. An example is provided in the following Article:
OPTION 2: Dump the unique record set and Load it back
- Perform an ABL dump of this table using this active unique index. In this way, only the indexed records will be dumped, leaving the duplicates behind.
- Then delete the table and re-load it (see below)
For Example:
Option A: Export by the unique FIELD:
OUTPUT TO dupicatestable.d.
FOR EACH duplicatestable BY <theuniquefield>:
EXPORT duplicatestable.
END.
OUTPUT CLOSE.
Option B: EXPORT by the unique INDEX
OUTPUT TO duplicatestable.d.
FOR EACH duplicatestable BY <unique index name>:
EXPORT duplicatestable.
END.
OUTPUT CLOSE.
OPTION 3: If these 1127 duplicates are as a result of a failed or dual binary or bulkload, delete the loaded records from the target database:
OPTION A: Truncate the Area
PROUTIL -C TRUNCATE AREA is the fastest method, however if other tables/indexes are in this area these will be removed as well and can only be run offline.
OPTION B: DROP the table through the data dictionary, then reload the table definitions
Once the record content has been deleted, redo the binary or bulkload.
