Troubleshooting: Custom table cannot be accessed

When migrating a database from a previous version of WebSphere Commerce to WebSphere Commerce Version 7, the database migration fails with an error that a new custom table could not be accessed normally.

Problem

If the database type is DB2, the error message will look similar to the following:
INFO:    [massload] Massloading C:\WCDE_E~1\schema\xml\wcs.keys.xml
INFO:    [massload] -dbname 172.16.2.140:50000/BRSF2 -dbuser db2admin -dbpwd ****** -infile C:\WCDE_E~1\schema\xml\wcs.keys.xml -method sqlimport -comm
itcount 1000 -maxerror 1 -customizer  -schemaname db2admin
INFO:   [wcimKeysMigrationTask] SET SCHEMA DB2ADMIN
ERROR:  BUILD FAILED
ERROR:  C:\WCDE_E~1\migration\xml\build.xml:32: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\build.xml:45: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\db.xml:5: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\db.xml:9: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\build.xml:56: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\build.xml:60: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\features\BASE\db\60\build.xml:35: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\features\BASE\db\60\build.xml:274: The following error occurred while executing this line:
ERROR:  C:\WCDE_E~1\migration\xml\features\BASE\db\common.xml:366: com.ibm.db2.jcc.a.SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRM
C=1;DB2ADMIN.XCATSEL, DRIVER=4.3.111
ERROR:  Total time: 4 minutes 2 seconds
ERROR:  WCIM has completed the job(s) with errors. Check the log files for details.
In this example, the error message shows that the custom table DB2ADMIN.XCATSE is in check pending state and could not be accessed normally.
The problem is occurs if the new custom table has foreign keys on one or more following WebSphere Commerce tables:
  • ACCOUNT
  • ATCHAST
  • ATCHTGT
  • ATCHTGTDSC
  • CATENTDESC
  • CATENTRY
  • CATGROUP
  • CATGRPDESC
  • COLLATERAL
  • CMFILE
  • DMACTIVITY
  • DMCAMPAIGN
  • EMLMSG
  • EMSPOT
  • GRADDR
  • MBRGRP
  • PX_PROMOAUTH
  • PX_PROMOCD
  • STORE

Solution

To solve the problem, follow these steps to perform an integrity check of the new custom table during database migration:
  1. Before you try to migrate the database again, open the file wc_installdir/schema/migration/60/db2/wcs.restore.bootstrap.sql and append the following SQL statement: 
    set integrity for table_name immediate checked;
    where table_name is the name of the new custom table that contains one or more of the foreign keys. If there is more than one table that has a foreign key to any of the above tables, you need to add an SQL statement for each table.
  2. Restore the database.
  3. Rerun database migration.