Massloader hangs on second attempt to massload same data with update option Technote
(troubleshooting)Problem(Abstract) You use idresgen with load option mixed and then use massloader load option sqlimport. The importing hangs and gives a duplicate key exception on the second load of the same data.
Error code: SQL0803NResolving the problem Ensure that all primary keys specified for each record within the datafile are unique if these records have unique indexes. Additional information
The duplicate key exception exists even with the mixed option as the specified input as the unique index and primary key is conflicting.
Two records exist within the XML datafile that have the same reference ID for the primary key and different unique indexes. On the first load, these two records within the datafile are assigned the same primary key and this will cause the second record to update the first record on the initial load. On the second load, the records within the datafile will be assigned a different primary key as the first record's unique indexes will not exist in the database. This is due to the refresh in the first load with the second record. Due to the sharing of the reference ID between the two records, the second record will be forced to have the same reference ID as the first record. When loading the second time, both records will be inserted as new records. The first one with a unique primary key and the second one with unique indexes with the same primary key. The failure of the second record would occur as the primary key has already been used on the first record. This enables the first record to be loaded correctly but not the second record.
Example:
An XML file exists with three columns and two records. They contain the following information:
Record 1:
Primary Key: RefID1
Unique Index A: DataA1
Unique Index B: DataB1
Record 2:
Primary Key: RefID1 (same as record 1)
Unique Index A: DataA2
Unique Index B: DataB2
On the first idresgen and massload, the following information is loaded. Assume a primary key index starts with 1.
Record 1: This record is loaded as usual.
Primary Key: 1
Unique Index A: DataA1
Unique Index B: DataB1
Record 2: This record overwrites the record 1 as the primary keys are the same. Only Record 2 will exist in the database.
Primary Key: 1 (same as record 1 as they have the same RefID)
Unique Index A: DataA2
Unique Index B: DataB2
With the same raw data and the second idresgen and massload, the following is loaded. The next available primary key index starts with 2.
Record 1: This record is loaded as a new record.
Primary Key: 2
Unique Index A: DataA1
Unique Index B: DataB1
Record 2: This record fails to load because the unique index is the same as first load, record 1. Primary key is the same as second load, record 1.
Primary Key: 2 (same as record 1 as they have the same RefID)
Unique Index A: DataA2
Unique Index B: DataB2
Cross Reference information
Segment Product Component Platform Version Edition Commerce WebSphere Commerce Business Edition Runtime AIX, i5/OS, Linux, Solaris, Windows 5.6, 5.6.1, 5.6.1.1, 5.6.1.2, 5.6.1.3, 5.6.1.4 Commerce WebSphere Commerce - Express Runtime i5/OS, Linux, Windows 5.6, 5.6.1, 5.6.1.1, 5.6.1.2, 5.6.1.3, 5.6.1.4, 6.0, 6.0.0.1, 6.0.0.2, 6.0.0.3, 6.0.0.4 Express Commerce WebSphere Commerce Professional Edition Runtime AIX, i5/OS, Linux, Solaris, Windows 5.6, 5.6.1, 5.6.1.1, 5.6.1.2, 5.6.1.3, 6.0, 6.0.0.1, 6.0.0.2, 6.0.0.3, 6.0.0.4 Professional Edition Commerce WebSphere Commerce Developer Enterprise Runtime Windows 6.0, 6.0.0.1, 6.0.0.2, 6.0.0.3, 6.0.0.4 Enterprise Commerce WebSphere Commerce Developer Business Edition Runtime Windows 5.6, 5.6.1, 5.6.1.1, 5.6.1.2, 5.6.1.3, 5.6.1.4 Developer Business Edition Commerce WebSphere Commerce Developer Professional Edition Runtime Windows 5.6, 5.6.1, 5.6.1.1, 5.6.1.2, 5.6.1.3, 6.0, 6.0.0.1, 6.0.0.2, 6.0.0.3, 6.0.0.4, 5.6.1.4 Developer Professional Edition Commerce WebSphere Commerce Developer Express Runtime Windows 5.6, 5.6.1, 5.6.1.1, 5.6.1.2, 5.6.1.3, 5.6.1.4, 6.0, 6.0.0.1, 6.0.0.2, 6.0.0.3, 6.0.0.4 Developer Express
Document Information Current web document: http://www.ibm.com/support/docview.wss?uid=swg21239302