Troubleshooting: Problem adding primary key constraint to the PROFILE table

While attempting to migrate the database from a previous version of WebSphere Commerce to WebSphere Commerce Version 7, you encounter an error that states that a constraint on the primary key (profile_id) could not be added for the PROFILE table. This error causes database migration to fail.

Problem

If you are migrating a DB2 database, you will see an error message similar to the following:

[2011.04.01.15.02.21] INFO:	[wcimPK2NegTask] SELECT * FROM profile table1 WHERE table1.profile_id > 0 AND EXISTS (SELECT 'X' FROM profile table2 WHERE (table2.profile_id = table1.profile_id * -1)  AND (table2.store_id = table1.store_id) AND (table2.transport_id = table1.transport_id) AND (table2.msgtype_id = table1.msgtype_id) ) 
[2011.04.01.15.02.25] INFO:	[wcimPK2NegTask] SET SCHEMA WCUSER 
[2011.04.01.15.02.25] INFO:	[wcimPK2NegTask] DELETE FROM profile WHERE profile_id in (SELECT profile_id FROM profile table1 WHERE table1.profile_id > 0 AND EXISTS (SELECT 'X' FROM profile table2 WHERE (table2.profile_id = table1.profile_id * -1)  AND (table2.store_id = table1.store_id) AND (table2.transport_id = table1.transport_id) AND (table2.msgtype_id = table1.msgtype_id) ) 
[2011.04.01.15.02.25] INFO:	[wcimPK2NegTask] DELETE FROM profile WHERE profile_id in (SELECT profile_id FROM profile table1 WHERE table1.profile_id > 0 AND EXISTS (SELECT 'X' FROM profile table2 WHERE (table2.profile_id = table1.profile_id * -1)  AND (table2.store_id = table1.store_id) AND (table2.transport_id = table1.transport_id) AND (table2.msgtype_id = table1.msgtype_id) )) 
[2011.04.01.15.02.26] INFO:	[wcimSQLExecTask] SET SCHEMA WCUSER 
[2011.04.01.15.02.26] INFO:	[wcimSQLExecTask] Executing file: /opt2/wcee7/wsc/schema/migration/60/wcs.add.profile.keys.indicies.sql 
[2011.04.01.15.02.30] ERROR:	[wcimSQLExecTask] Failed to execute:  alter table profile add primary key (profile_id) 
[2011.04.01.15.02.30] ERROR:	BUILD FAILED 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/build.xml:32: The following error occurred while executing this line: 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/build.xml:45: The following error occurred while executing this line: 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/db.xml:5: The following error occurred while executing this line: 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/db.xml:9: The following error occurred while executing this line: 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/build.xml:56: The following error occurred while executing this line: 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/build.xml:60: The following error occurred while executing this line: 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/features/BASE/db/60/build.xml:35: The following error occurred while executing this line: 
[2011.04.01.15.02.30] ERROR:	/opt2/wcee7/wsc/migration/xml/features/BASE/db/60/build.xml:263: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-673, SQLSTATE=23515, SQLERRMC=PROFILE_ID..., DRIVER=4.11.69 
[2011.04.01.15.02.30] ERROR:	Total time: 89 minutes 20 seconds 
[2011.04.01.15.02.30] ERROR:	[WCIMANT] <run> WCIM has completed the job(s) with errors. Check the log files for details.

The attempt to add the primary key constraint for the PROFILE table failed because some of the rows that were intended to be primary keys had duplicate profile_id values. Each row in the PROFILE table must have bootstrap value of WebSphere Commerce for all columns; this value should not be changed. If you modified this value for one or more columns for any rows in the table in the previous WebSphere Commerce database version, this would cause the problem.

Solution

To solve this problem:

Execute the following SQL statement on the migrated database:
```
Select count(profile_id), profile_id from profile group by profile_id having count(profile_id) > 1
```
The SQL statement will return a list of profile_id values. All of the values listed are duplicates.
Restore the database from the previous version of WebSphere Commerce into database management for WebSphere Commerce Version 7.
Using the list of profile_id values from Step 1:
1. record each customized column and its customized value for all of the customized rows
2. update the customized column to the bootstrap value
For example, in the database restored from the previous version of WebSphere Commerce, if you see the following row in the PROFILE table:
```
<profile profile_id="-12" store_id="0" devicefmt_id="-2" transport_id="1" msgtype_id="18" usersview="N" lowpriority="0" highpriority="0" />
```
but you see that the bootstrap value of the column devicefmt_id of this row is -3, you need to manually update the customized column to the bootstrap value. You can get the bootstrap data from WC_installdir/schema/xml/wcs.bootstrap_base.xml in the previous version WebSphere Commerce. For this example, you would use the following SQL statement to update:
```
UPDATE PROFILE SET devicefmt_id=-3 WHERE profile_id=-12
```
Try again to migrate the database from the previous version of WebSphere Commerce to WebSphere Commerce Version 7.
After database migration completes, in the migrated database, restore each customized column and its customized value for all of the customized rows that you recorded in Step 3.
For example, in the migrated WebSphere Commerce V7 database, if there is the following row in the PROFILE table,
```
<profile profile_id="-12" store_id="0" devicefmt_id="-3" transport_id="1" msgtype_id="18" usersview="N" lowpriority="0" highpriority="0" />
```
But you'd recorded that in the previous version of the database, the value for column devicefmt_id in this row had been changed from -3 to -2 , then you would use the following SQL statement to restore the column to its previous customized value.
```
UPDATE PROFILE SET devicefmt_id=-2 WHERE profile_id=-12
```