Re-usable component which Increases performance in Inventory Availability Service


This blog is aimed at providing information on how Out of the box (OOB) Inventory availability service works and the need of creating a custom re-usable component for inventory availability service. The benefit of using new service against using Out of the box (OOB) service.



  1. IBM WCS version 7
  2. RAD IDE


Inventory in WCS

  1. Catalog
  • In WebSphere commerce, the catalog data feed is loaded via data load process.  With the feed, the products and Items are loaded to database.
  • The product Iphone 10 is loaded into database This product has multiple Items that are resolved to item level SKU.

For example:

Sample catalog entry


     2. Version Spec
  • Inventory for each catentry IDs ( Items) will be loaded by Invenory data load process or It can be manually added using admin tools.
  • This data load process will load the inventor count in all the given fulfilment centers.
  • Versioin Spec table maintains the versions loaded for each catentry id which is associated to item spec id. For example:



3. Fulfillment Centers
  1. Fulfillment centers are the warehouses in wcs. The fulfilment center data is loaded via data load process.
  2. Fulfillment center can be marked for delete to make it inactive and vice versa


 4. Receipt table for Inventory
  1. Inventory data load process, will load the inventory count in Receit table.
  2. It will load the inventory count to each version spec id.
  3. Same version spec id is used for multiple fulfillment centers so that for a cat-entry Id, inventory is fetch from cumulative sum of all the fulfillment centers.


OOB Inventory Service Flow

OOB Service design, Implementation and flow:

Need for creating new customized service:

  1. When we send more number of cateentry ids in URI of inventory availability service, It took good amount of time
  2. In Product Display Page it turned as slow performance and rendering the details got delayed


New Design

  1. To Improve the performance, below design approaches are considered:
  2. Hitting the stored procedure only once for all the cat entries
  3. Avoid calling stored procedure for alternate cat entries
  4. Do everything at stored procedure and sends response to WCS
  5. WCS prepares JSON body and sends back to client


Implementation Steps

  1. Create a new REST Service
    1. Create a Handler
    2. Provide an entry of that handle rin resources.ext file in REST application folder
    3. Map the corresponsing controller command in handler so that hanler transfer the request to controller command
    4. Given an entry of that controller command in cmdreg table
    5. Provide access policies If required
    6. Pass required input parameters
    7. Prepare response which will be sent back to end user


  1. Extend BaseJDBCHelper
    1. Open a connection
    2. Invoke stored procedure with the required input and output parameters


  1. Create a stored procedure and handle entire logic  in procedure only
    1. Create stored procedure
    2. Handle entire logic in stored procedure itself so that number of stored procedures invocation will be reduced


Sample Code:



package com.mycompany.commerce.inventory.handler;
import java.util.logging.Logger;
 * @author Sridhar Ganji
 *             MycompanyInventoryAvailabilityHandler
 *             This RESTful Handler is to perform check & get Inventory
 *  quantity based on catentry Id.
public class MycompanyInventoryAvailabilityHandler extends AbstractConfigBasedClassicHandler
               private static final String CLASSNAME = MycompanyInventoryAvailabilityHandler.class.getName();
               private static final String PARAMETER_CATENTRYIDS = "catEntryIds";
               private static final Logger LOGGER = LoggingHelper.getLogger(MycompanyInventoryAvailabilityHandler.class);
               private static final String RESOURCE_NAME = "inventoryavailability";
               private static final String INVENTORY_FOR_CATENTRY_ID = "bycatentryids/{
               private static final String INVENTORY_AVAILABILITY_CATENTRY_ID_INFO_SUMMARY =                                       "Inventory_Availability_CatEntryId_Info_Summary";
                private static final String COMMAND_INTERFACE_NAME_PARAMETER = "com.mycompany.commerce.inventory.commands.MycompanyInventoryAvailabilityCmd";
                public String getResourceName() {
                                return RESOURCE_NAME;
                 * reserveInventoryForDeviceAndSim
                 * @param storeId
                 * @param orderId
                 * @param responseFormat
                 * @return
                 * @throws Exception
                @Produces({ MediaType.APPLICATION_JSON, MediaType.APPLICATION_XML,
                                                MediaType.APPLICATION_XHTML_XML, MediaType.APPLICATION_ATOM_XML })
                public Response inventoryAvailabilityForCatEntryIds(
                                                @PathParam(PARAMETER_STORE_ID) String storeId,
                                                @PathParam(PARAMETER_CATENTRYIDS) String catEntryIds,
                                                @QueryParam(value = "responseFormat") String responseFormat) throws Exception {
                                // Set up trace facilities
                                final String METHODNAME = "inventoryAvailabilityForCatEntryIds(String storeId,String catentryid,String responseFormat)";
                                // Log method start
                                //final boolean traceEnabled = LoggingHelper.isTraceEnabled(LOGGER);
                                final boolean entryExitTraceEnabled = LoggingHelper
                                if (entryExitTraceEnabled) {
                                                Object[] objArr = new Object[] { storeId,catEntryIds };
                                                LOGGER.entering(CLASSNAME, METHODNAME, objArr);
                                 Response response = prepareAndValidate(storeId, getResourceName(), "GET", this.request, responseFormat);
                                    if (response == null) {
                                                TypedProperty requestProperties = initializeRequestPropertiesFromRequestMap(responseFormat);                                                                            
                                                  * Use the configuration-based REST API to automatically fill in input
                                                  * values, execute, and build the response.
                                                  response = executeConfigBasedCommandWithContext(COMMAND_INTERFACE_NAME_PARAMETER,
                                                                       INVENTORY_AVAILABILITY_CATENTRY_ID_INFO_SUMMARY, responseFormat,storeId, requestProperties);                                                                           
                                // Log method exit
                                if (entryExitTraceEnabled) {
                                                LOGGER.exiting(CLASSNAME, METHODNAME, response);
                                return response;


Register  handler entry  in file

Create Profile mapping in commandMapping-ext.
<?xml version="1.0" encoding="UTF-8"?>

<!-- ====================================================================================== 
            The sample contained herein is provided to you "AS IS". It is furnished by 
            IBM as a simple example and has not been thoroughly tested under all conditions. 
            IBM, therefore, cannot guarantee its reliability, serviceability or functionality. 
            This sample may include the names of individuals, companies, brands and products 
            in order to illustrate concepts as completely as possible. All of these names 
            are fictitious and any similarity to the names and addresses used by actual 
            persons or business enterprises is entirely coincidental.
             ====================================================================================== -->
                        <profile name="Inventory_Availability_CatEntryId_Info_Summary">
                    <input inputName="storeId" methodName="setStoreId"/>
                    <input inputName="catEntryIds" methodName="setCatEntryIds"/>
                    <output methodName="getInventoryAvailability" outputName="InventoryAvailability"/>
Create Controller Command


package com.mycompany.commerce.inventory.commands;
 * Modified Author: Sridhar Ganji
 * Handled Invocation of stored procedure with parameters
 * parsed and prepared the response from the result of stored procedure
import java.rmi.RemoteException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.concurrent.ConcurrentHashMap;
import java.util.logging.Logger;
import javax.ejb.CreateException;
import javax.ejb.FinderException;
import javax.naming.NamingException;
import org.apache.commons.lang3.StringUtils;
import com.mycompany.commerce.constants.MycompanyConstants;
import com.mycompany.commerce.error.messages.MycompanyShoppingCartErrorMessages;
import com.mycompany.commerce.storedprocedure.helper.MycompanyBaseJDBCHelper;
 * MycompanyInventoryAvailabilityCmdImpl
 * This is the command to fetch inventory for one or more product items based on their catEntry Id's.
 * @author Suman Kola
public class MycompanyInventoryAvailabilityCmdImpl extends ControllerCommandImpl
implements MycompanyInventoryAvailabilityCmd {
                private String storeId;
                private String catEntryIds;
                private List<ConcurrentHashMap<String, String>> inventoryAvailability;
                 * CLASS_NAME
                private static final String CLASS_NAME  = MycompanyInventoryAvailabilityCmdImpl.class.getName();
                 * LOGGER
                private static final Logger LOGGER = Logger.getLogger(CLASS_NAME );
                 * performExecute
                 * This method is to fetch Inventory based on CatentryId from fulfillment centers available.
                public void performExecute() throws ECException {
                                LOGGER.entering(CLASS_NAME , MycompanyConstants.Common.PERFORM_EXECUTE.getValue());
                                String methodName = "performExecute";
                                LOGGER.entering(CLASS_NAME, methodName);
                                List list = new ArrayList<ConcurrentHashMap<String,String>>();
                                String catEntryId = null;
                                MycompanyBaseJDBCHelper mycompanyStoredProcedureHelperBean = new MycompanyBaseJDBCHelper();
                                try {
                                                StoreAccessBean sab = new StoreAccessBean();
                                                String langId = sab.getLanguageId();
                                                LanguageAccessBean lab = new LanguageAccessBean();
                                                Locale locale = new Locale(lab.getLanguage(), lab.getCountry());
                                                String sessionId = CommandContextHelper.getUniqueSessionTag( getCommandContext());
                                                mycompanyStoredProcedureHelperBean.callInventoryAvailStoreProcedure(catEntryIds, getStoreId().toString(), sessionId);
                                                String availInvStrResult = mycompanyStoredProcedureHelperBean.getResultStr();
                                                if( StringUtils.isNotEmpty(availInvStrResult) ){
                                                              String[]resultSetArray = availInvStrResult.split(",");
                                                                for( String resultSetForCatEntry : resultSetArray ){
                                                                                String[] resultSetForCatEntryArray = resultSetForCatEntry.split(":");
                                                                                ConcurrentHashMap<String, String> map = new ConcurrentHashMap<String, String>();
                                                                                map.put("availableQuantity",new Double(resultSetForCatEntryArray[1]).toString());
                                                                                catEntryId = resultSetForCatEntryArray[0];
                                                                                setAvaialbleStatusMsgAndAlertMsgs(new Long(resultSetForCatEntryArray[1]), resultSetForCatEntryArray[0], map, sab,lab, locale );


                                } catch (SQLException e) {
                                                throw new ECSystemException(ECMessage._ERR_SQL_EXCEPTION,
                                                                                CLASS_NAME , methodName, e);
                                }catch (RemoteException e) {
                                                throw new ECSystemException(ECMessage._ERR_RTP_REMOTEEXCEPTION,
                                                                                CLASS_NAME , methodName, e);
                                } catch (CreateException e) {
                                                throw new ECSystemException(ECMessage._ERR_CREATE_EXCEPTION,
                                                                                CLASS_NAME , methodName, e);
                                } catch (FinderException e) {
                                      "No Catalog Entry was found for catentry ID "+catEntryId+" Message : "+e.getMessage());
                                } catch (NamingException e) {
                                                throw new ECSystemException(ECMessage._ERR_NAMING_EXCEPTION,
                                                                                CLASS_NAME , methodName, e);

                                LOGGER.exiting(CLASS_NAME , MycompanyConstants.Common.PERFORM_EXECUTE.getValue());
                private void setAvaialbleStatusMsgAndAlertMsgs(Long totalAvailableQuantity, String catEntryId, ConcurrentHashMap<String, String> map,StoreAccessBean sab,  LanguageAccessBean lab, Locale locale) throws RemoteException, CreateException, FinderException, NamingException{
                                String methodName = "setAvaialbleStatusMsgAndAlertMsgs";
                                LOGGER.entering(CLASS_NAME, methodName);
                                CatalogEntryAccessBean aabCatalogEntry = new CatalogEntryAccessBean();
                                Integer intField1 = aabCatalogEntry.getField1InEJBType();
                                if (intField1 == null) {
                                                intField1 = 0;
                                // Field2 is used for sending alert for minimum stock.
                                Integer intField2 = aabCatalogEntry.getField2InEJBType();
                                if (intField2 == null) {
                                                intField2 = 0;
                                // Below condition for setting the inventory to Unavailable
                                if (totalAvailableQuantity < intField1) {
                                // Below condition for sending the alert as inventory is less than
                                // the field2 and greater than the field1
                                if (totalAvailableQuantity < intField2
                                                                && totalAvailableQuantity >= intField1 && intField2 != 0) {
                                                // sendAlertMessage method is invoked to get the alert message
                                                // and flag set
                                                sendAlertMessage(map, sab, lab, locale);
                                if (totalAvailableQuantity >= intField2){
                                LOGGER.exiting(CLASS_NAME, methodName);
                private void sendAlertMessage(ConcurrentHashMap<String, String> map, StoreAccessBean sab,  LanguageAccessBean lab, Locale locale){
                                String methodName = "sendAlertMessage";
                                LOGGER.entering(CLASS_NAME, methodName);
                                ECMessage ecm = MycompanyShoppingCartErrorMessages
                                try {
                                                // Set alert message in user data
                                                                                ECMessageHelper.getUserMessage(ecm, null, locale));
                                                // Set alert flag to true
                                                map.put("x_alertflag", "true");
                                } catch (Exception e) {
                                                throw new InventorySystemException("", null, CLASS_NAME ,
                                                                                "sendAlertMessage(InventoryAvailabilityType)", e);
                                LOGGER.exiting(CLASS_NAME, methodName);
                public void setStoreId(String storeId) {
                                this.storeId = storeId;
                public String getCatEntryIds() {
                                return catEntryIds;
                public void setCatEntryIds(String catEntryIds) {
                                this.catEntryIds = catEntryIds;
                public List<ConcurrentHashMap<String, String>> getInventoryAvailability() {
                                return inventoryAvailability;
                public void setInventoryAvailability(List<ConcurrentHashMap<String, String>> inventoryAvailability) {
                                this.inventoryAvailability = inventoryAvailability;
                 * accessControlCheck
                 * return :true
                public boolean accessControlCheck() throws ECException {
                                return Boolean.TRUE;


Create JDBC Helper that extends BaseJDBCHelper
package com.mycompany.commerce.storedprocedure.helper;
 * Author: Sridhar Ganji
 * This is extended the BaseJDBCHelper class to have some methods to use.
 * This class is used to get DB connection from super class.
 * It calls stored procedure with parameters
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.logging.Logger;
import javax.ejb.SessionContext;
import javax.naming.NamingException;
public class MycompanyBaseJDBCHelper extends BaseJDBCHelper{
                 * CLASS_NAME
                private static final String CLASS_NAME  = MycompanyBaseJDBCHelper.class.getName();
                 * LOGGER
                private static final Logger LOGGER = Logger.getLogger(CLASS_NAME );
                private String resultStr;
                private String p_sperror;
                private String p_sqlstate;
                public  void callInventoryAvailStoreProcedure(String catEntryIds, String storeId, String sessionId) throws SQLException, ECException{
                                String methodName = "callInventoryAvailStoreProcedure";
                                LOGGER.entering(CLASS_NAME, methodName);                              
                                CallableStatement stmt;
                                                String dbschema = WcsApp.configProperties.getValue("Database/DB/DBSchema");
                                                stmt = getCallableStatement((new StringBuilder("{call ")).append(dbschema).append("MYCOMPANY_AVAILABLEINV(?,?,?,?)}").toString());
                                } else
                                                stmt = getCallableStatement("{call MYCOMPANY_AVAILABLEINV(?,?,?,?)}");
                                //"Call MYCOMPANY_AVAILABLEINV with sessioinId: "+sessionId+" for catentryIDS: "+catEntryIds);
                               stmt.setString(1, catEntryIds);
                                stmt.setString(2, storeId);
                                stmt.registerOutParameter(3, 12);
                                stmt.setString(4, sessionId);
                                resultStr = stmt.getString(3);
                                LOGGER.exiting(CLASS_NAME, methodName);
                private  void  makeBespokeConnection() throws ECException{
                                String methodName = "makeBespokeConnection";
                                LOGGER.entering(CLASS_NAME, methodName);
                                try {
                                } catch (NamingException e) {
                                                throw new ECSystemException(ECMessage._ERR_NAMING_EXCEPTION,
                                                                               CLASS_NAME , methodName, e);
                                }catch (SQLException e) {
                                                throw new ECSystemException(ECMessage._ERR_SQL_EXCEPTION,
                                                                                CLASS_NAME , methodName, e);
                                LOGGER.exiting(CLASS_NAME, methodName);
                 * part of Interface method, its overrriden
                public SessionContext getSessionContext() {
                                // TODO Auto-generated method stub
                                return null;
               public String getResultStr() {
                                return resultStr;
                public void setResultStr(String resultStr) {
                                this.resultStr = resultStr;
                public String getP_sperror() {

                                return p_sperror;
                public void setP_sperror(String p_sperror) {

                                this.p_sperror = p_sperror;
                public String getP_sqlstate() {
                                return p_sqlstate;
                public void setP_sqlstate(String p_sqlstate) {
                                this.p_sqlstate = p_sqlstate;


Register controller command in CMDREG table

(0,'com.mycompany.commerce.inventory.commands.MycompanyInventoryAvailabilityCmd','New Controller Command InventoryAvail Service',


Create an Array Type


Create a table to log the results

create table StoredPrcedursLog(userId varchar(200), key varchar(200), value varchar(500));


Creare a procedure
IN catEntries VARCHAR(32672),
IN p_storeId VARCHAR(50),
OUT currentStrRes VARCHAR(32672),
IN sessionId varchar(100)
SPECIFIC ‘Use the generated SQL ID ’
DECLARE temp, curStrVal varchar(32672);
DECLARE i,  leng, strLen,    sumReserved,  totalSum, versionSpcId, versionSpcCount, itemSpcId INTEGER;
DECLARE allVrspcsumCatentry, allVrspcsumAltCatentry, sumCatentry, catTotal  INTEGER;
DECLARE C2 CURSOR FOR                                                                                        
select versionspc_id from versionspc v1 where v1.itemspc_id =   itemSpcId;
SET temp = catEntries;
SET itemSpcId = 0;
SET versionSpcId = 0;
SET curStrVal='';
SET i =1;
SET versionSpcCount = 0; 
SET strLen =  (LENGTH(temp) - length(replace(temp, ',', '') ));
delete from StoredPrcedursLog;
WHILE strLen > 0  DO
        SET CATENTRYIDS[i] = SUBSTR(catEntries, 1, LOCATE(',', catEntries)-1 );
        SET catEntries = SUBSTR(catEntries, LOCATE(',', catEntries)+1);
        SET i = i+1;
        SET strLen = strLen-1;
end WHILE;
SET CATENTRYIDS[i] = catEntries;
SET i =1;
WHILE i<=leng DO
        SET sumCatentry = 0;
        SET totalSum=0;
        SET itemSpcId = 0;
        SET versionSpcId = 0;
        SET sumReserved = 0;
        SET catTotal=0;
        SET allVrspcsumCatentry=0;
        SET allVrspcsumAltCatentry=0;             
        select c.itemspc_id into itemSpcId  from catentry c  where c.catentry_id= CATENTRYIDS[i];
        select count(*) into versionSpcCount from versionspc v1 where v1.itemspc_id = itemSpcId ;      
        insert into StoredPrcedursLog values(sessionId, i||'#'||'catentryId', CATENTRYIDS[i]);      
        OPEN C2;                                                                                                     
        while versionSpcCount >0 DO
                FETCH C2 INTO versionSpcId ;  
                insert into StoredPrcedursLog values(sessionId, i||'#'||'itemSpcId--versionSpcId', itemSpcId||'--'||versionSpcId);              
                select COALESCE(Sum((r.QTYONHAND-r.QTYINPROCESS)),0), c.itemspc_id  into sumCatentry, itemSpcId  from receipt r,  distarrang da, rcptavail ra, catentry c, versionspc vsc, FFMCENTER ffm
                        where  c.catentry_id = CATENTRYIDS[i]  and    c.itemspc_id =  vsc.itemspc_id and
                                r.versionspc_id =  versionSpcId
                                        and da.distarrang_id = ra.distarrang_id and ra.receipt_id = r.receipt_id
                                        AND CURRENT DATE BETWEEN DATE(da.startdate) AND DATE(da.enddate) and  r.store_id = da.wholesalestore_id and  r.store_id=p_storeId and
                                         ffm.FFMCENTER_ID =  r.FFMCENTER_ID and ffm.MARKFORDELETE=0
                                        group by r.versionspc_id,  c.itemspc_id ;        
                SET allVrspcsumCatentry = allVrspcsumCatentry+ sumCatentry ;
                 insert into StoredPrcedursLog values(sessionId, i||'#'||'itemSpcId--versionSpcId--sumCatentry',itemSpcId||'--'||versionSpcId||'--'||sumCatentry);                      
                SET versionSpcCount = versionSpcCount -1;
        end while;
        CLOSE C2;      
        insert into StoredPrcedursLog values(sessionId, i||'#'||'itemSpcId--catentryid--allVrspcsumCatentry',itemSpcId||'--'||CATENTRYIDS[i]||'--'||allVrspcsumCatentry);                            
        SELECT COALESCE(Sum((qtyreserved + qtyallocbackorder)),0) into sumReserved  FROM itemffmctr ifc, FFMCENTER ffm
                WHERE itemspc_id = itemSpcId AND store_id = p_storeId and  ifc.FFMCENTER_ID = ffm.FFMCENTER_ID and  ffm.MARKFORDELETE=0;                       
        insert into StoredPrcedursLog values(sessionId, i||'#'||'catentry-catentryIdSum-sumreserved',  CATENTRYIDS[i]||'-'||allVrspcsumCatentry||'-'||sumReserved);          
        SET catTotal = allVrspcsumCatentry-sumReserved;
        insert into StoredPrcedursLog values(sessionId, i||'#'||'catentry-catTotal',  CATENTRYIDS[i]||'-'||catTotal);                                  
        IF i=1 then
                SET curStrVal = CATENTRYIDS[i] ||':' ||(totalSum);
                 insert into StoredPrcedursLog values(sessionId, i||'#'||'catentryId--currentStr', CATENTRYIDS[i]||'--'||curStrVal);
                 SET curStrVal = curStrVal||',' ||CATENTRYIDS[i] ||':' ||(totalSum);
                 insert into StoredPrcedursLog values(sessionId, i||'#'||'catentryId--currentStr', CATENTRYIDS[i]||'--'||curStrVal);
         END IF;            
        SET i = i + 1;
end WHILE;               
SET currentStrRes = curStrVal;







Implement the new service with above code and db scripts. Stat the server and hit the above REST URL from a REST client.


Screen Shots:

OOB Service:

New Service:



About The Author

Leave a Reply