Stored Proc Issue in WCS 6.0
Versions:
WebSphere Commerce 6.0.0.1 Enterprise Edition
Database: DB2 8.2.3 Enterprise
Problem
Encountered this error when trying to place an order:
SQL0444N Routine “GETITEMS” (specific name “SQL061206150656090″) is implemented with code in library or path “\GETITEMS”, function “GETITEMS” which cannot be accessed. Reason code: “4″. SQLSTATE=42724
Solution:
This basically means that certain stored procedures do not exist in the database. This might happen for any number of reasons. I know that I was working with a copy of the db2 database that is restored at some time. In any case, I noticed that GetItems and some other stored procedures disappeared from the database! To know what stored procedures must exist, check the file <wcs_home>/bin/createsp.db2.bat. To know what stored procedures exist in the commerce database, check the procedures list using the DB2 Admin Console. I recompiled the stored procedures from a DB2 command window as follows:
db2 -td@ -f <wcs_home>/schema/db2/storedprocedure/inventoryallocation.procedure.sql
db2 -td@ -f <wcs_home>/schema/db2/storedprocedure/getitems.procedure.sql
db2 -td@ -f <wcs_home>/schema/db2/storedprocedure/reverseinventory.procedure.sql
Note that GetItems stored procedure uses InventoryAllocation procedure, therefore that procedure must be compiled before compiling the GetItems procedure.
That solved the problem.