Troubleshooting: Operation fails because user lacks the appropriate authority

You attempt to migrate a database from a previous version of WebSphere Commerce to WebSphere Commerce Version 7, but the database migration fails with an error. The error indicates that the current database user has no authority to perform a specific operation, such as a SELECT operation, against some tables.

Problem

The following example shows what the message looks like if you are attempting to migrate a DB2 database:
[2011.10.26.20.47.59] INFO:	[wcimSQLExecTask] SET SCHEMA WCSDBADM 
[2011.10.26.20.47.59] INFO:	[wcimSQLExecTask] Executing file: /opt/IBM/WebSphere/CommerceServer70/schema/db2/wcs.patch.sql 
[2011.10.26.20.47.59] INFO:	[wcimSQLExecTask] Executing file: /opt/IBM/WebSphere/CommerceServer70/schema/db2/wcs.view.sql 
[2011.10.26.20.47.59] ERROR:	[wcimSQLExecTask] Failed to execute:  CREATE VIEW prdatraval AS SELECT c1.attribute_id, c1.attrtype_id, c1.attrvalue_id, c1.catentry_id, c1.field1, c1.field2, c1.field3, c1.floatvalue, c1.image1, c1.image2, c1.integervalue, c1.language_id, c1.name, c1.oid, c1.sequence, c1.stringvalue, c2.partnumber FROM attrvalue c1, catentry c2 WHERE c1.catentry_id = c2.catentry_id 
[2011.10.26.20.47.59] ERROR:	BUILD FAILED 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/build.xml:32: The following error occurred while executing this line: 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/build.xml:45: The following error occurred while executing this line: 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/db.xml:5: The following error occurred while executing this line: 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/db.xml:9: The following error occurred while executing this line: 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/build.xml:56: The following error occurred while executing this line: 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/build.xml:60: The following error occurred while executing this line: 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/features/BASE/db/60/build.xml:33: The following error occurred while executing this line: 
[2011.10.26.20.47.59] ERROR:	/opt/IBM/WebSphere/CommerceServer70/migration/xml/features/BASE/db/60/build.xml:237: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=WCSDBADM;SELECT;WCSDBADM.CATENTRY, DRIVER=4.12.55 
[2011.10.26.20.47.59] ERROR:	Total time: 1 minute 44 seconds 
[2011.10.26.20.47.59] ERROR:	[WCIMANT] <run> WCIM has completed the job(s) with errors. Check the log files for details. 
[2011.10.26.20.54.48] INFO:	[WCIMUtil] <quit> Stopping server.
In this example, the error message shows that an attempt to create a view called WCSDBADM.prdatraval failed. The operation failed because the current database user has no authority to run a SELECT against the WCSDBADM.CATENTRY table.

Solution

To solve the problem:
  1. Restore the previous database to the WebSphere Commerce Version 7 environment by manually issuing the database restore command.
  2. Export the data from the specific table for which the database migration operation is unable to retrieve a record count.
  3. Delete the data from the specific table mentioned in Step 2.
  4. Try again to migrate the database.
  5. After the database migration completes successfully, if the data you exported in Step 2 is still needed, import it back into the table.