WebSphere Commerce 7 | Solr | Preprocess failed to run | SolrCore 'collection1' is not available | Could not load config file "search/solr/home/collection1/solrconfig.xml"

After migrating custom Solr files and if you try to run Solr Preprocess, sometimes you might face the following exceptions in running di-preprocess command,

INFO: Solr server core MC_10001_CatalogEntry_Unstructured_en_US status is initialized.
Aug 5, 2016 1:51:45 PM com.ibm.commerce.foundation.dataimport.process.DataImportProcessorMain executeDIH
INFO: Solr server core MC_10001_CatalogEntry_Unstructured_es_ES status is initialized.
Aug 5, 2016 1:51:45 PM null null
INFO: CWPKI0027I: Disabling default hostname verification for HTTPS URL connections.
Aug 5, 2016 1:51:46 PM com.ibm.commerce.foundation.dataimport.util.DataImportHelper printException
INFO: Data import process was unsuccessful. An unrecoverable error has occurred.
Throwable occurred: org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Expected mime type application/octet-stream but got text/html. Error 500: {msg=SolrCore 'collection1' is not available due to init failure: Could not load config file /opt/IBM/WebSphere/CommerceServer70/instances/live/search/solr/home/collection1/solrconfig.xml,trace=org.apache.solr.common.SolrException: SolrCore 'collection1' is not available due to init failure: Could not load config file /opt/IBM/WebSphere/CommerceServer70/instances/live/search/solr/home/collection1/solrconfig.xml
        at org.apache.solr.core.CoreContainer.getCore(CoreContainer.java:827)
        at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:317)
        at com.ibm.commerce.foundation.internal.server.services.search.servlet.solr.SolrSearchDispatchFilter.doFilter(SolrSearchDispatchFilter.java:100)
        at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:217)
        ...........
        ...........
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:278)
        at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
        at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
        at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
        at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
        at com.ibm.io.async.AsyncChannelFuture$1.run(AsyncChannelFuture.java:205)
        at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1662)
Caused by: org.apache.solr.common.SolrException: Could not load config file /opt/IBM/WebSphere/CommerceServer70/instances/live/search/solr/home/collection1/solrconfig.xml
        at org.apache.solr.core.CoreContainer.createFromLocal(CoreContainer.java:530)
        at org.apache.solr.core.CoreContainer.create(CoreContainer.java:597)

Ideally, there won't be any "collections1" folder in the WebSphere Commerce Solr files. This might have happened because couple of Solr configuration files are missed in Search folder of the WebSphere Commerce installation directory.

Check whether all the Seach configurations files are in place and it should resolve your issue.

WebSphere Commerce 7 | Storefront | Product Display Page | "Add to Cart" button failed to load | CMN0420E | CWXFR9010I

After publishing new store in the Extended Sites, all the prices are coming up fine. But, in the Product Display Page, "Add to Cart" button is not getting displayed and it is throwing the following exception,

[8/11/16 5:42:31:712 GMT] 00000069 CommerceSrvr  E com.ibm.commerce.command.ECCommandTarget executeCommand CMN0420E: The following command exception has occurred during processing: "java.lang.NullPointerException". java.lang.NullPointerException
        at com.ibm.commerce.price.commands.PriceRuleGetContractUnitPriceCmdImpl.getRuleExecutionCommand(PriceRuleGetContractUnitPriceCmdImpl.java:512)
        at com.ibm.commerce.price.commands.PriceRuleGetContractUnitPriceCmdImpl.executeRule(PriceRuleGetContractUnitPriceCmdImpl.java:419)
        at com.ibm.commerce.price.commands.PriceRuleGetContractUnitPriceCmdImpl.calculatePrices(PriceRuleGetContractUnitPriceCmdImpl.java:198)
        at com.ibm.commerce.price.commands.PriceRuleGetContractUnitPriceCmdImpl.performExecute(PriceRuleGetContractUnitPriceCmdImpl.java:123)
        ...........
        ...........
        at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199)
        at com.ibm.commerce.price.commands.AbstractGetPriceCmdImpl.execute(AbstractGetPriceCmdImpl.java:1306)
        at com.ibm.commerce.price.commands.AbstractGetPriceCmdImpl.executeNextInChain(AbstractGetPriceCmdImpl.java:1224)
        at com.ibm.commerce.price.commands.EnsureOffersExistGetContractUnitPriceCmdImpl.performExecute(EnsureOffersExistGetContractUnitPriceCmdImpl.java:220)
        ...........
        ...........
        at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:263)
        at org.apache.jasper.el.JspValueExpression.getValue(JspValueExpression.java:100)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_set_274(_CachedProductOnlyDisplay.java:16947)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_when_32(_CachedProductOnlyDisplay.java:17015)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_choose_20(_CachedProductOnlyDisplay.java:17304)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_if_64(_CachedProductOnlyDisplay.java:17346)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspService(_CachedProductOnlyDisplay.java:3099)
        at com.ibm.ws.jsp.runtime.HttpJspBase.service(HttpJspBase.java:99)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
        at com.ibm.ws.cache.servlet.ServletWrapper.serviceProxied(ServletWrapper.java:307)
        at com.ibm.ws.cache.servlet.CacheHook.handleFragment(CacheHook.java:576)
        ..............
        ..............
[8/11/16 5:42:31:713 GMT] 00000069 ExtendedInfo  I   CWXFR9010I: Extended information : [URL=http://localhost/webapp/wcs/stores/servlet/ProductDisplay] [parameters=storeId[0]=10651 storeId[1]=10651 parent_category_rn=40722 productId[0]=5750001 productId[1]=5750001 urlRequestType=Base categoryId=40792 top_category=40718 catalogId[0]=12051 catalogId[1]=12051 langId[0]=-24 langId[1]=-24 ] [userId=-1002]
[8/11/16 5:42:31:715 GMT] 00000069 CommerceSrvr  E com.ibm.commerce.command.ECCommandTarget executeCommand CMN0420E: The following command exception has occurred during processing: "java.lang.NullPointerException". java.lang.NullPointerException
        at com.ibm.commerce.price.commands.AbstractGetPriceCmdImpl.createFromOutput(AbstractGetPriceCmdImpl.java:578)
        at com.ibm.commerce.price.commands.EnsureOffersExistGetContractUnitPriceCmdImpl.performExecute(EnsureOffersExistGetContractUnitPriceCmdImpl.java:222)
        at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157)
        at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332)
        at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:166)
        at com.ibm.commerce.command.MeasuredCacheableCommandImpl.execute(MeasuredCacheableCommandImpl.java:63)
        at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199)
        at com.ibm.commerce.price.commands.AbstractGetPriceCmdImpl.execute(AbstractGetPriceCmdImpl.java:1306)
        at com.ibm.commerce.price.commands.AbstractGetPriceCmdImpl.executeNextInChain(AbstractGetPriceCmdImpl.java:1224)
        at com.ibm.commerce.price.commands.CompositeGetContractUnitPriceCmdImpl.executeCommand(CompositeGetContractUnitPriceCmdImpl.java:339)
        at com.ibm.commerce.price.commands.CompositeGetContractUnitPriceCmdImpl.performExecute(CompositeGetContractUnitPriceCmdImpl.java:230)
        .............
        .............
        at javax.el.BeanELResolver.getValue(BeanELResolver.java:65)
        at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:143)
        at org.apache.el.parser.AstValue.getValue(AstValue.java:102)
        at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:263)
        at org.apache.jasper.el.JspValueExpression.getValue(JspValueExpression.java:100)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_set_274(_CachedProductOnlyDisplay.java:16947)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_when_32(_CachedProductOnlyDisplay.java:17015)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_choose_20(_CachedProductOnlyDisplay.java:17304)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspx_meth_c_if_64(_CachedProductOnlyDisplay.java:17346)
        at com.ibm._jsp._CachedProductOnlyDisplay._jspService(_CachedProductOnlyDisplay.java:3099)
        at com.ibm.ws.jsp.runtime.HttpJspBase.service(HttpJspBase.java:99)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
        ............
        ............

When we tried to validate Price Lists & Price Rules for the store, it looks fine. But, there were no default Customer Segment and Price Rules setup in Commerce Accelerator and after setting up them from Commerce Accelerator, it started coming fine.

Here are the steps to setup default Customer Segments and Price rules,
1. Login to Commerce Accelerator
2. Select "Merchandise" --> "Catalog Filter and Price Rule"
3. In the "Catalog Filter" tab, set property value for the Catalog filter number

4. In the "Price Rule" tab, set property value for the Price Rule

WebSphere Commerce 7 | Solr | Preprocess Command failed to run | ERRORCODE=-4229, SQLSTATE=null | SQLCODE=-803, SQLSTATE=23505 | TI_APGROUP_0

Sometimes, we might face issue of Solr Preprocess failed with the following exceptions and it is because of Data issue in Categories and Catalog entries,
com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor determineCatalogEntriesForCatalog(Connection connection, String catalogID)
INFO: The catalog with ID: 10001 contains 18769 catalog entries.
com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.19.49] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
        at com.ibm.db2.jcc.am.kd.a(Unknown Source)
        at com.ibm.db2.jcc.am.Agent.endBatchedReadChain(Unknown Source)
        at com.ibm.db2.jcc.am.gp.a(Unknown Source)
        at com.ibm.db2.jcc.am.gp.c(Unknown Source)
        at com.ibm.db2.jcc.am.gp.executeBatch(Unknown Source)
        at com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor.populateTable(CatalogHierarchyDataPreProcessor.java:1271)
        at com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor.process(CatalogHierarchyDataPreProcessor.java:676)
        at com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor.process(CatalogHierarchyDataPreProcessor.java:448)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.processDataConfig(DataImportPreProcessorMain.java:1072)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.execute(DataImportPreProcessorMain.java:942)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.main(DataImportPreProcessorMain.java:385)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at com.ibm.ws.bootstrap.WSLauncher.main(WSLauncher.java:267)
Aug 9, 2016 2:21:04 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain processDataConfig(DataProcessingConfig, String)
INFO: Error for batch element #172: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;WCSDB.TI_APGROUP_0, DRIVER=4.19.49
Aug 9, 2016 2:21:04 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain processDataConfig(DataProcessingConfig, String)
INFO: SQL:
Aug 9, 2016 2:21:04 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logExitCode
INFO:
The program exiting with exit code: 1.
Data import pre-processing was unsuccessful. An unrecoverable error has occurred.

This issue might have happend because one Category is mapped with multiple Categories or One Product might be mapped with multiple Categories. Here are the queries to find these entries,

select catentry_id, catgroup_id from catgpenrel where catentry_id in (select catentry_id from catgpenrel where catalog_id = 10001 group by catentry_id having count(catentry_id) > 1)

select catgroup_id_parent, catgroup_id_child from catgrprel where catalog_id = 10001 and catgroup_id_child in (select catgroup_id_child from catgrprel where catalog_id = <catalogId> group by catgroup_id_child having count(catgroup_id_child) > 1)

Once these Catalog entries and groups issue has been fixed, re run Preprocesss command and it should work fine.

WebSphere Commerce 7 | Local setup of WCS & IBM HTTP Server | Not able to hit local server | Request is not reaching to WAS

I got some weird issue in my local environment where WebSphere Commerce Server was able to start properly. But, I am not able open Storefront pages as the request is not reaching the Application Server.

Later, I have realized it was happened because IBM HTTP Server was not started.


After, WebSphere Commerce installation in the local environment and on top of it, IBM HTTP Server has been installed so that Static content will be served from Web Server instead of WCS Application Server as mentioned in the following document,
http://www.ibm.com/developerworks/library/co-config-httpserver-was/

As part of this IBM HTTP Server installation, you might have run Jython Script so that, Ports  80, 443 will be used by HTTP Server. Later, you will be able to hit only local WebSphere Commerce Server through IBM HTTP Server itself.

If we stop HTTP Server and try to hit Storefront, it will not reach the WebSphere Commerce Server. In the future, if you don't want to use IBM HTTP Server and directly, hit WCS Server itself, then update Jython Script "configureWCPorts.py".

AdminTask.modifyServerPort ('server1', '[-nodeName localhost -endPointName WC_defaulthost -host localhost -port 80 -modifyShared true]')

AdminTask.modifyServerPort ('server1', '[-nodeName localhost -endPointName WC_defaulthost_secure -host localhost -port 443 -modifyShared true]')

AdminTask.modifyServerPort ('server1', '[-nodeName localhost -endPointName WC_PORT_4 -host localhost -port 18006 -modifyShared true]')AdminConfig.save()

And run the following Jython Script as mentioned in the document,
wsadmin -lang jython -f configureWCPorts.py -conntype NONE

Now, you should be able to hit WebSphere Application Server directly with out IBM HTTP Server.

WebSphere Commerce 7 | Solr Search | Running Preprocess is failing | CWFDIH0001 | CWFDIH0002 | ERRORCODE=-4461, SQLSTATE=42815

After Solr setup, when we tried to run di-preprocess command, it failing with the following exception,
       
Table name:     TI_GROUPING_0
Fetch size:     500
Batch size:     500
Aug 3, 2016 9:23:59 AM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logExitCode
INFO:
The program exiting with exit code: 1.
Data import pre-processing was unsuccessful. An unrecoverable error has occurred.

Aug 3, 2016 9:23:59 AM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logEndDateAndTime
INFO: Data import pre-processing ended:Wed Aug 03 09:23:59 GMT 2016
Aug 3, 2016 9:23:59 AM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logEndDateAndTime
INFO: Data import pre-processing completed in 4.287 seconds.
Aug 3, 2016 9:23:59 AM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain handleExecutionException
SEVERE: Exception message: CWFDIH0001: Data Import encountered a generic error. The error is CWFDIH0002: An SQL exception was caught. The following error occurred: [jcc][1091][10404][4.19.49] Invalid data conversion: Parameter instance 5711501;5711502 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815.. , stack trace: com.ibm.commerce.foundation.dataimport.exception.DataImportSystemException: CWFDIH0001: Data Import encountered a generic error. The error is CWFDIH0002: An SQL exception was caught. The following error occurred: [jcc][1091][10404][4.19.49] Invalid data conversion: Parameter instance 5711501;5711502 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815..
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.processDataConfig(DataImportPreProcessorMain.java:1112)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.execute(DataImportPreProcessorMain.java:942)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.main(DataImportPreProcessorMain.java:385)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at com.ibm.ws.bootstrap.WSLauncher.main(WSLauncher.java:267)
Caused by: com.ibm.commerce.foundation.dataimport.exception.DataImportSystemException: CWFDIH0002: An SQL exception was caught. The following error occurred: [jcc][1091][10404][4.19.49] Invalid data conversion: Parameter instance 5711501;5711502 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815.
        at com.ibm.commerce.foundation.dataimport.preprocess.AbstractDataPreProcessor.insertColumns(AbstractDataPreProcessor.java:284)
        at com.ibm.commerce.foundation.dataimport.preprocess.StaticAttributeDataPreProcessor.process(StaticAttributeDataPreProcessor.java:365)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.processDataConfig(DataImportPreProcessorMain.java:1072)
        ... 7 more
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10404][4.19.49] Invalid data conversion: Parameter instance 5711501;5711502 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

From the log file, we have noticed two catentries - 5711501;5711502 and we tried validate from the database, one Catentry is parent to another catentry. But where as both catentries has CATENTTYPE_ID has 'ProductBean' itself.

We have corrected the Catentries and rerun the di-preprocess, it was successful

WebSphere Commerce 7 | Storefront | Failed to compile JSP | SRVE0293E | JSPG0049E | Exceeding the 65535 bytes limit

Once the new code has been deployed to WebSphere Commerce environment and hit the Category Page, it starting throwing the following Exception,

[8/4/16 7:29:13:700 GMT] 00000059 webapp        E com.ibm.ws.webcontainer.webapp.WebApp logServletError SRVE0293E: [Servlet Error]-[/DefaultStorefrontAssetStore/Layouts/SearchBasedCategoryPage.jsp]: com.ibm.ws.jsp.JspCoreException: JSPG0049E: /DefaultStorefrontAssetStore/Layouts/SearchBasedCategoryPage.jsp failed to compile :

/opt/IBM/WebSphere/AppServer/profiles/live/temp/WC_myNode_node/server1/WC_live/Stores.war/DefaultStorefrontAssetStore/Layouts/_SearchBasedCategoryPage.java : 967 : The code of method _jspService(HttpServletRequest, HttpServletResponse) is exceeding the 65535 bytes limit

        at com.ibm.ws.jsp.webcontainerext.AbstractJSPExtensionServletWrapper.translateJsp(AbstractJSPExtensionServletWrapper.java:620)
        at com.ibm.ws.jsp.webcontainerext.AbstractJSPExtensionServletWrapper._checkForTranslation(AbstractJSPExtensionServletWrapper.java:487)
        at com.ibm.ws.jsp.webcontainerext.AbstractJSPExtensionServletWrapper.checkForTranslation(AbstractJSPExtensionServletWrapper.java:340)
        at com.ibm.ws.jsp.webcontainerext.AbstractJSPExtensionServletWrapper.handleRequest(AbstractJSPExtensionServletWrapper.java:156)
        at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:374)
        at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1085)
        at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:398)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:241)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
        at com.ibm.commerce.struts.ECActionServlet.processRequest(ECActionServlet.java:232)
        at com.ibm.commerce.struts.ECActionServlet.doGet(ECActionServlet.java:173)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:718)
        at com.ibm.commerce.struts.ECActionServlet.service(ECActionServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
       
To fix the issue, here are the steps,

• Login to WAS Administration Console
• Navigate to Servers --> Server Types --> WebSphere application servers
• Click on server1
• In Configuration Tab, Click on Container Settings --> Web Container Settings --> Web Container

  

• Click on Additional Properties --> Custom properties

 

•  Click on New and provide the custom property,

          Name : com.ibm.wsspi.jsp.disableResourceInjection
          Value: true

•  Restart the server

WebSphere Commerce 7 | Queries | Enable/Disable Site Administrators

Here are few queries related to enabling/disabling Site Administrators in WebSphere Commerce,

-- Query to check Site Admins
select u1.users_id, status, plcyacct_id, logonid, u2.REGISTERTYPE, u2.dn, u2.REGISTRATION from userreg u1, users u2 where u1.users_id = u2.users_id and u2.REGISTERTYPE not in ('R', 'G');

-- Disable all Site Admins
update userreg set status = 0 where logonid in (select logonid from userreg u1, users u2 where u1.users_id = u2.users_id and u2.REGISTERTYPE not in ('R', 'G'));

-- Enable all Site Admins
update userreg set status = 1 where logonid in (select logonid from userreg u1, users u2 where u1.users_id = u2.users_id and u2.REGISTERTYPE not in ('R', 'G'));

-- Disable Site Admin
update userreg set status = 0 where logonid = 'siteadmin'

-- Enable Site Admin
update userreg set status = 1 where logonid = 'siteadmin'

WebSphere Commerce 7 | Setting up New Stores | Queries for setting new LANGUAGE & CURRENCY

If you wanted to setup a new store which supports the LANGUAGE and CURRENCY,  which is not supported by Out of Box WebSphere Commerce then you need to setup this new LANGUAGE and CURRENCY.

Here are tables associated with LANGUAGE,
 - LANGUAGE
 - LANGPAIR
 - LANGUAGEDS
 
Here are tables associated with CURRENCY,
 - SETCURR
 - SETCURRDSC
 - CURFORMAT
 - CURFMTDESC

For example, if you wanted to setup new language_id "en_UK" and currency as "EUR", here are the queries,
 
-- Setting new languages
insert into LANGUAGE values (-24, 'en_UK', 'en', 'UK', null, 'UTF-8', 'iso-8859-1', null);

insert into LANGPAIR(storeent_id, language_id, language_id_alt, sequence) values (-1, -24, -1, 100.0);

insert into LANGUAGEDS(language_id, description, language_id_desc) values (-24, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-23, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-22, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-21, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-20, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-10, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-9, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-8, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-7, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-6, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-5, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-4, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-3, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-2, 'United Kingdom English', -24);
insert into LANGUAGEDS(language_id, description, language_id_desc) values (-1, 'United Kingdom English', -24);

-- Setting new currencies
insert into SETCURR (setccurr, setccode, setcexp) values ('EUR', 604, -2);
insert into SETCURRDSC (setccurr, language_id, description) values ('EUR', -1, 'Euro');
insert into SETCURRDSC (setccurr, language_id, description) values ('EUR', -24, 'Euro');
insert into CURFORMAT (storeent_id, setccurr, roundingmultiple,numbrusg_id, roundingmethod, decimalplaces, minapproveamount) values (-1,'EUR', 1, -1,'R', 2, NULL);
insert into CURFMTDESC (storeent_id, NUMBRUSG_ID, setccurr, language_id, currencysymbol, customizedcurrstr, currencyprefixpos, currencysuffixpos, displaylocale, currencyprefixneg, currencysuffixneg, radixpoint, groupingchar, numberpattern, description) values
(-1, -1, 'EUR', -1, '&euro;', null, '&euro;&nbsp;', null, null, '-&euro;&nbsp;', null, null, null, '#,##0.00', 'Euro');
insert into CURFMTDESC (storeent_id, NUMBRUSG_ID, setccurr, language_id, currencysymbol, customizedcurrstr, currencyprefixpos, currencysuffixpos, displaylocale, currencyprefixneg, currencysuffixneg, radixpoint, groupingchar, numberpattern, description) values
(-1, -1, 'EUR', -5, '&euro;', null, '&euro;&nbsp;', null, null, '-&euro;&nbsp;', null, null, null, '#,##0.00', 'Euro');
insert into CURFMTDESC (storeent_id, NUMBRUSG_ID, setccurr, language_id, currencysymbol, customizedcurrstr, currencyprefixpos, currencysuffixpos, displaylocale, currencyprefixneg, currencysuffixneg, radixpoint, groupingchar, numberpattern, description) values
(-1, -1, 'EUR', -24, '&euro;', null, '&euro;&nbsp;', null, null, '-&euro;&nbsp;', null, null, null, '#,##0.00', 'Euro');
commit;

-- Store related entries
insert into storelang values (-24, 10001, NULL, 1)
insert into storelang values (-24, 10051, NULL, 1)
insert into storelang values (-24, 10101, NULL, 1)

insert into storeentds values (-24, 10001, 'Extended Sites Hub', NULL, 'Extended Sites Hub', NULL, 1);
insert into storeentds values (-24, 10051, 'CAS_AS', NULL, 'Catalog Asset Store', NULL, 1);
insert into storeentds values (-24, 10101, 'DefaultStorefrontAssetStore', NULL, 'DefaultStorefrontAssetStore Storefront Asset Store entity', NULL, 1);

-- Catalog related entries
insert into catalogdsc values (10001, -24, 'CAT_AS', NULL, 'Catalog Asset Store', NULL, NULL, 1); 

WebSphere Commerce 7 | Server failed to Publish | Pointing to Workspace outside RAD | Still points to Old Datbase

We tried to point the Workspace which is outside of RAD and copied the updated wc-server.xml from RAD to the Workspace outside of RAD.

In the updated wc-server.xml has MYNEWDATABASE. But, while starting the server, if failed as it is still trying to point to MYOLDDATABASE. When we have validated wc-server.xml, it has updated MYNEWDATABASE Details itself.

Here is the error log,
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R com.ibm.commerce.wc.appmanagement.exception.WCAppManagementException: com.ibm.websphere.ce.cm.StaleConnectionException: [jcc][t4][2057][11264][4.19.26] The application server rejected establishment of the connection.
An attempt was made to access a database, MYOLDDATABASE, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004DSRA0010E: SQL State = 08004, Error Code = -4,499
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.wc.version.ECDatabaseVersionInfo.readSiteTable(ECDatabaseVersionInfo.java:140)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.wc.version.ECDatabaseVersionInfo.<init>(ECDatabaseVersionInfo.java:84)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.server.ProductInfo.getECDatabaseVersionInfo(ProductInfo.java:58)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.server.ProductInfo.getECDBVersionInfo(ProductInfo.java:96)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.server.ProductInfo.getProductVersion(ProductInfo.java:75)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.server.WcsApp.configInit(WcsApp.java:736)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.server.WcsApp.initialize(WcsApp.java:465)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.server.WebApp.init(WebApp.java:396)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at com.ibm.commerce.struts.InitActionServlet.init(InitActionServlet.java:64)
[7/7/16 18:26:44:912 IST] 00000011 SystemErr     R     at javax.servlet.GenericServlet.init(GenericServlet.java:241)
...........
...........
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.CORBA.iiop.ServerDelegate.dispatchInvokeHandler(ServerDelegate.java:585)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.CORBA.iiop.ServerDelegate.dispatch(ServerDelegate.java:461)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.rmi.iiop.ORB.process(ORB.java:533)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.CORBA.iiop.ORB.process(ORB.java:1574)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.rmi.iiop.Connection.respondTo(Connection.java:2956)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.rmi.iiop.Connection.doWork(Connection.java:2823)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.rmi.iiop.WorkUnitImpl.doWork(WorkUnitImpl.java:65)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ejs.oa.pool.PooledThread.run(ThreadPool.java:118)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1656)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: [jcc][t4][2057][11264][4.19.26] The application server rejected establishment of the connection.
An attempt was made to access a database, MYOLDDATABASE, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004DSRA0010E: SQL State = 08004, Error Code = -4,499
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:44)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at java.lang.reflect.Constructor.newInstance(Constructor.java:516)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapExceptionHelper(GenericDataStoreHelper.java:615)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapException(GenericDataStoreHelper.java:677)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ws.rsadapter.AdapterUtil.mapException(AdapterUtil.java:2112)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ws.rsadapter.spi.WSRdbDataSource.getPooledConnection(WSRdbDataSource.java:2475)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection(WSManagedConnectionFactoryImpl.java:1639)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ejs.j2c.FreePool.createManagedConnectionWithMCWrapper(FreePool.java:2082)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1759)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:2631)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:1064)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:701)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:668)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:635)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     at com.ibm.commerce.wc.version.ECDatabaseVersionInfo.readSiteTable(ECDatabaseVersionInfo.java:132)
[7/7/16 18:26:44:928 IST] 00000011 SystemErr     R     ... 103 more
[7/7/16 18:26:45:755 IST] 00000011 JDBCPersister W   CNTR0031W: Error starting CMP bean WC#Enablement-RelationshipManagementData.jar#AlternativeLanguage:
 com.ibm.ejs.persistence.EJSPersistenceException: ; nested exception is:
    com.ibm.websphere.ce.cm.StaleConnectionException: [jcc][t4][2057][11264][4.19.26] The application server rejected establishment of the connection.
An attempt was made to access a database, MYOLDDATABASE, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004DSRA0010E: SQL State = 08004, Error Code = -4,499
    at com.ibm.ejs.persistence.EJSJDBCPersister.initialize(EJSJDBCPersister.java:392)
    at com.ibm.ejs.persistence.EJSJDBCPersister.create(EJSJDBCPersister.java:220)
    at com.ibm.ws.cpi.JDBCPersisterFactoryImpl.create(JDBCPersisterFactoryImpl.java:119)
    at com.ibm.ws.metadata.ejb.EJBMDOrchestrator.createCMP11Persister(EJBMDOrchestrator.java:1241)
    at com.ibm.ws.metadata.ejb.EJBMDOrchestrator.finishBMDInit(EJBMDOrchestrator.java:1544)
    at com.ibm.ws.runtime.component.EJBContainerImpl.finishDeferredBeanMetaData(EJBContainerImpl.java:5066)
    at com.ibm.ws.runtime.component.EJBContainerImpl.initializeDeferredEJB(EJBContainerImpl.java:4875)
    at com.ibm.ejs.container.HomeOfHomes$1.run(HomeOfHomes.java:408)
    at com.ibm.ejs.container.HomeOfHomes$1.run(HomeOfHomes.java:406)
    at com.ibm.ws.security.util.AccessController.doPrivileged(AccessController.java:118)
    at com.ibm.ejs.container.HomeOfHomes.getHome(HomeOfHomes.java:405)
    at com.ibm.ejs.container.HomeOfHomes.internalCreateWrapper(HomeOfHomes.java:1003)
    at com.ibm.ejs.container.EJSContainer.createWrapper(EJSContainer.java:5024)
    at com.ibm.ejs.container.WrapperManager.faultOnKey(WrapperManager.java:545)
    at com.ibm.ejs.util.cache.Cache.findAndFault(Cache.java:498)
    at com.ibm.ejs.container.WrapperManager.keyToObject(WrapperManager.java:489)
................
................
   
While debugging the issue, we have noticed the following file has MYOLDDATABASE details. After updating resources.xml, clean publish, it started working fine,   
WC/META-INF/ibmconfig/cells/defaultCell/applications/defaultApp/deployments/defaultApp/resources.xml

WebSphere Commerce 7 | Management Center | Failed to load the Catentries on select of Category in the Left navigation tree

After migrating new stores, few configurations didn't loaded properly. And, on click on Category in the left navigation tree of the Catalog Management of the Management Center, it is throwing the following exception,

[7/5/16 14:09:05:685 IST] 00000057 CommerceSrvr  E com.ibm.commerce.productset.commands.util.TPCUtil myFindZeroLevelTPCByCatalogIdAndStoreId CMN0411E: The following finder exception has occurred during processing: "javax.ejb.ObjectNotFoundException: catalogGroupId=". javax.ejb.ObjectNotFoundException: catalogGroupId=
    at com.ibm.ejs.container.EJSHome.activateBean_Common(EJSHome.java:2097)
    at com.ibm.ejs.container.EJSHome.activateBean(EJSHome.java:1890)
    at com.ibm.commerce.catalog.objects.EJSJDBCPersisterCMPCatalogGroupTradingPositionRelBean_3868bf7b.findByPrimaryKey(EJSJDBCPersisterCMPCatalogGroupTradingPositionRelBean_3868bf7b.java:468)
    at com.ibm.commerce.catalog.objects.EJSCMPCatalogGroupTradingPositionRelHomeBean_3868bf7b.findByPrimaryKey(EJSCMPCatalogGroupTradingPositionRelHomeBean_3868bf7b.java:132)
    at com.ibm.commerce.catalog.objects.EJSRemoteCMPCatalogGroupTradingPositionRelHome_3868bf7b.findByPrimaryKey(EJSRemoteCMPCatalogGroupTradingPositionRelHome_3868bf7b.java:123)
    at com.ibm.commerce.catalog.objects._CatalogGroupTradingPositionRelHome_Stub.findByPrimaryKey(_CatalogGroupTradingPositionRelHome_Stub.java:415)
    at com.ibm.commerce.catalog.objects.CatalogGroupTradingPositionRelAccessBean.instantiateEJB(CatalogGroupTradingPositionRelAccessBean.java:246)
    at com.ibm.ivj.ejb.runtime.AbstractEntityAccessBean._instantiate(AbstractEntityAccessBean.java:174)
    at com.ibm.ivj.ejb.runtime.AbstractEntityAccessBean.refreshCopyHelper(AbstractEntityAccessBean.java:309)
    at com.ibm.commerce.catalog.objects.CatalogGroupTradingPositionRelAccessBean.refreshCopyHelper(CatalogGroupTradingPositionRelAccessBean.java:320)
    at com.ibm.ivj.ejb.runtime.AbstractEntityAccessBean.__getCache(AbstractEntityAccessBean.java:101)
    at com.ibm.commerce.catalog.objects.CatalogGroupTradingPositionRelAccessBean.getTradingPositionIdInEJBType(CatalogGroupTradingPositionRelAccessBean.java:398)    at com.ibm.commerce.productset.commands.util.TPCUtil.myFindZeroLevelTPCByCatalogIdAndStoreId(TPCUtil.java:444)
    at com.ibm.commerce.productset.commands.util.TPCUtil.access$9(TPCUtil.java:425)
    at com.ibm.commerce.productset.commands.util.TPCUtil$MyFindZeroLevelTPCByCatalogIdAndStoreId.generateCacheableData(TPCUtil.java:211)
    at com.ibm.commerce.datatype.AbstractFinderResult.invokeFinder(AbstractFinderResult.java:622)
    at com.ibm.commerce.datatype.CacheableFinderResult.invokeFinder(CacheableFinderResult.java:195)
    ...........
    ...........
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:600)
    at com.ibm.commerce.foundation.internal.client.taglib.config.GetDataConfig$DataTypeConfig.invokeClientFacadeMethod(GetDataConfig.java:899)
    at com.ibm.commerce.foundation.internal.client.taglib.GetDataHandler.execute(GetDataHandler.java:744)
    at com.ibm.commerce.foundation.internal.client.taglib.GetDataTag.doTag(GetDataTag.java:388)
    at com.ibm._jsp._GetCatalogEntryOfferPrice._jspService(_GetCatalogEntryOfferPrice.java:147)
    at com.ibm.ws.jsp.runtime.HttpJspBase.service(HttpJspBase.java:99)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
    ..........
    ..........

After that, I have noticed somehow CATEGORY and TRADEPOSCN relationship was missing in the Database.

Here is the query to insert the CATEGORY and TRADEPOSCN relationship
insert into catgrptpc values(
    (select catalog_id from catalog where identifier='Aurora CAS'),
    0,
    (select tradeposcn_id from tradeposcn where name='Aurora ProductsPriceList'),
    (select storeent_id from storeent where identifier='aurora'),
    0
); 

After the insert, it started working fine.

WebSphere Commerce 7 | Management Center | Failed to change the Store from top right dropdown

After publishing new stores, I did some configuration changes. Later, when I tried change the store in the Management Center in the top right dropdown, it is throwing the following exception,

[7/1/16 19:52:28:176 IST] 0000005f EJBMDOrchestr A   CNTR0098I: Container-managed pre-find persistent store synchronization disabled for bean: "WC#Enablement-RelationshipManagementData.jar#CurrencyDescription".
[7/1/16 19:52:28:506 IST] 0000005f CommerceSrvr  E com.ibm.commerce.datatype.AbstractFinderResult invokeFinder CMN0409E: The following error occurred during processing: "An invalid XML character (Unicode: 0x1a) was found in the element content: ". java.lang.RuntimeException: An invalid XML character (Unicode: 0x1a) was found in the element content:
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl$Escape.convert(XMLSaveImpl.java:2945)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.getDatatypeValue(XMLSaveImpl.java:2754)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveDataTypeSingle(XMLSaveImpl.java:1531)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveFeatures(XMLSaveImpl.java:1118)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveElementID(XMLSaveImpl.java:2458)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveElement(XMLSaveImpl.java:1032)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveElement(XMLSaveImpl.java:918)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveContainedMany(XMLSaveImpl.java:2178)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveFeatures(XMLSaveImpl.java:1386)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.saveElementID(XMLSaveImpl.java:2458)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.writeTopObject(XMLSaveImpl.java:617)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.traverse(XMLSaveImpl.java:549)
    at org.eclipse.emf.ecore.xmi.impl.XMLSaveImpl.save(XMLSaveImpl.java:233)
    at org.eclipse.emf.ecore.xmi.impl.XMLResourceImpl.doSave(XMLResourceImpl.java:203)
    at org.eclipse.emf.ecore.resource.impl.ResourceImpl.save(ResourceImpl.java:993)
    at com.ibm.commerce.foundation.common.util.sdo.SDOHelper.convertObjectToEmfXmlByteArray(SDOHelper.java:1076)
    at com.ibm.commerce.infrastructure.facade.server.commands.cache.OnlineStoreCache$FetchNounUsingDataServiceFacade.generateCacheableData(OnlineStoreCache.java:318)
    at com.ibm.commerce.datatype.AbstractFinderResult.invokeFinder(AbstractFinderResult.java:622)
    at com.ibm.commerce.datatype.CacheableFinderResult.invokeFinder(CacheableFinderResult.java:195)
    at com.ibm.commerce.dynacache.commands.AbstractFinderResultCache.myInvokeFinder(AbstractFinderResultCache.java:1506)
    at com.ibm.commerce.dynacache.commands.AbstractFinderResultCache.invokeFinder(AbstractFinderResultCache.java:1154)
    at com.ibm.commerce.dynacache.commands.AbstractDistributedMapCache$Cache.getOrPut(AbstractDistributedMapCache.java:522)
    at com.ibm.commerce.dynacache.commands.AbstractDistributedMapCache$Cache.access$3(AbstractDistributedMapCache.java:509)
    at com.ibm.commerce.dynacache.commands.AbstractDistributedMapCache.getOrPut(AbstractDistributedMapCache.java:285)
    at com.ibm.commerce.infrastructure.facade.server.commands.cache.OnlineStoreCache.fetchOnlineStore(OnlineStoreCache.java:493)
    at com.ibm.commerce.infrastructure.facade.server.commands.cache.OnlineStoreCache.getOnlineStore(OnlineStoreCache.java:529)
    at com.ibm.commerce.infrastructure.facade.server.commands.FetchOnlineStoreCmdImpl.performExecute(FetchOnlineStoreCmdImpl.java:123)    at com.ibm.commerce.foundation.internal.server.command.impl.CommandTarget.executeCommand(CommandTarget.java:66)
    at com.ibm.websphere.command.TargetableCommandImpl.execute(TargetableCommandImpl.java:139)
    at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:138)
    at com.ibm.commerce.foundation.server.command.bod.AbstractGetBusinessObjectDocumentCmdImpl.performExpression(AbstractGetBusinessObjectDocumentCmdImpl.java:309)
    at com.ibm.commerce.foundation.server.command.bod.AbstractGetBusinessObjectDocumentCmdImpl.performExecute(AbstractGetBusinessObjectDocumentCmdImpl.java:158)
   
When I tried to debug the issue, I have noticed STOREENTDS.DESCRIPTION table has some Junk Characters. After resetting them, It started working fine.

Here are few list of tables that needs to be validated, if you are facing the same kind of issue,
 - STOREENTDS
 - STORECONF
 - STADDRESS

WebSphere Commerce 7 | List of Interview topics at a glance

Here is my long time pending task :)

From couple of months, I was trying of drafting the list of topics which are useful for cracking the WebSphere Commerce interviews.

Downloadable version,  WebSphere Commerce 7 Topics

Feel free to add the missing topics in the comments sections so that, I will updated the same to the document.

WebSphere Commerce 7 | Payment Instructions are Failing | NullPointerException on "createPaymentInstruction"

In my toolkit, few of the data got corrupted and when I try to test the payment pages, it is throwing the following exception,

[4/18/16 9:53:41:332 GMT] 0000006b RemoteExcepti E   CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "createPaymentInstruction" on bean "BeanId(WC_live#Payments-Plugin-Controller.jar#PluginController, null)". Exception data: java.lang.NullPointerException
        at java.util.Hashtable.put(Hashtable.java:877)
        at com.ibm.commerce.payments.plugincontroller.PluginController.getPlugin(PluginController.java:2908)
        at com.ibm.commerce.payments.plugincontroller.PluginController.createPaymentInstruction(PluginController.java:986)
        at com.ibm.commerce.payments.plugincontroller.beans.EJSRemoteStatelessPluginController_c947b422.createPaymentInstruction(EJSRemoteStatelessPluginController_c947b422.java:399)
        at com.ibm.commerce.payments.plugincontroller.beans._PluginController_Stub.createPaymentInstruction(_PluginController_Stub.java:2511)
        at com.ibm.commerce.payment.actions.commands.EditPaymentInstructionPolicyCmdImpl.createPaymentInstruction(EditPaymentInstructionPolicyCmdImpl.java:633)
        at com.ibm.commerce.payment.actions.commands.EditPaymentInstructionPolicyCmdImpl.performExecute(EditPaymentInstructionPolicyCmdImpl.java:244)
        at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157)
        at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332)
        at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:166)
        at com.ibm.commerce.command.MeasuredCacheableCommandImpl.execute(MeasuredCacheableCommandImpl.java:63)
        at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199)
        at com.ibm.commerce.edp.commands.EditCmdImpl.setupAndCreatePaymentInstruction(EditCmdImpl.java:2516)
        at com.ibm.commerce.edp.commands.EditCmdImpl.addPaymentInstruction(EditCmdImpl.java:1039)
        at com.ibm.commerce.edp.commands.EditCmdImpl.processNewPIOperation(EditCmdImpl.java:1726)
        at com.ibm.commerce.edp.commands.EditCmdImpl.processIndividualPIEditOperation(EditCmdImpl.java:1706)
        at com.ibm.commerce.edp.commands.EditCmdImpl.processFG(EditCmdImpl.java:783)
        at com.ibm.commerce.edp.commands.EditCmdImpl.performExecute(EditCmdImpl.java:438)
        at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157)
        at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332)
...............
...............
Caused by: com.ibm.commerce.exception.ECApplicationException: The payment system encounters an error when updating the payment instructions of order 131,006. GenericSystemError
        at com.ibm.commerce.edp.commands.PIAddCmdImpl.addPI(PIAddCmdImpl.java:502)
        at com.ibm.commerce.edp.commands.PIAddCmdImpl.performExecute(PIAddCmdImpl.java:360)
        at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157)
        at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332)
        at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:166)
        at com.ibm.commerce.command.MeasuredCacheableCommandImpl.execute(MeasuredCacheableCommandImpl.java:63)
        at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199)
        at com.ibm.commerce.foundation.server.command.soi.MessageMappingCmdImpl.invokeCommand(MessageMappingCmdImpl.java:229)


Issue might have happened because your MERCHCONFINFO Tables doesn't have proper entries. Try to correct entries between MERCHCONF and MERCHCONFINFO Tables and it should work fine   

WebSphere Commerce 7 | Upgraded WCS 7.0.0.6 to WCS 7.0.0.7 | Fix Pack 7 | Payment Pages are Failing | CWXFR0020E, CWXFD3134E

I have upgraded my toolkit from WCS 7.0.0.6 to WCS 7.0.0.7 (Applied Fix Pack 7 - Database is DB2) and after that, payment pages are failing with the following exception,

[5/9/16 23:32:42:137 CLT] 00000024 bod   E   CWXFD3134E: Execution of get request failed. CWXFR0020E: Could not get a database connection for component com.ibm.commerce.wallet
com.ibm.commerce.foundation.internal.common.exception.FoundationApplicationException: CWXFD3134E: Execution of get request failed. CWXFR0020E: Could not get a database connection for component com.ibm.commerce.wallet
at com.ibm.commerce.foundation.server.command.bod.bom.AbstractFetchNounUsingBusinessObjectMediatorCmdImpl.performExecute(AbstractFetchNounUsingBusinessObjectMediatorCmdImpl.java:255)
at com.ibm.commerce.foundation.internal.server.command.impl.CommandTarget.executeCommand(CommandTarget.java:66)
at com.ibm.websphere.command.TargetableCommandImpl.execute(TargetableCommandImpl.java:139)

While doing the debug of the issue, I have noticed it is not pointing to my actual DB2 Database. Instead, it is pointing to Cloudscape Database.

Here is the issue,
1. Features has been enabled with FEP5 with Cloudscape
2. On top of it, setdbtype command has been run with DB2
3. Because of this, DB2 related libraries are not available in WC Project and instead, it has Cloudscape related libraries.

Resolution:
Copy all the JAR files from (DB2),
WCDE_installdir\components\foundation\wc.ear.ext\db2 to WCDE_installdir\workspace\WC

WebSphere Commerce 7 | IBM HTTP Server | Steps for setting up IBM HTTP Server

Here are the steps for installing IBM HTTP Server and configuring with WebSphere Commerce,
 

• Move all your static content (Like Images/CSS/JS etc) from Stores project and move it to a StaticWeb Project so that, it will easy to maintain the static assets separately rather than in Stores Project. Later, you can easily move these static contents from this project to your Web Server.

• Install IBM HTTP Server with the steps mentioned in following document,

   http://www.ibm.com/developerworks/library/co-config-httpserver-was/

• Update the following tags in C:\WebSphere\IHS\conf\httpd.conf to point out to StaticWeb Project, 

       <VirtualHost *:80>
       <VirtualHost *:443>
       <VirtualHost *:8006>

       Here is the snippet,
       <VirtualHost *:80>
        ServerName *
        Alias  /wcsstore  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent"
        Alias  /wcs  "W:\IBM\WCDE_ENT70\workspace\CommerceAccelarator\WebContent"
        Alias  /css  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\css"
        Alias  /js  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\js"
        Alias  /icons  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\icons"
        Alias  /images  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\images"
        Alias  /contentspots  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\contentspots"

        # Example:  Turn on the rewrite engine.
        RewriteEngine On
    </VirtualHost>

    <VirtualHost *:443>
        SSLEnable
        SSLClientAuth 0
        ServerName *
        Alias  /wcsstore  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent"
        Alias  /wcs  "W:\IBM\WCDE_ENT70\workspace\CommerceAccelarator\WebContent"
        Alias  /css  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\css"
        Alias  /js  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\js"
        Alias  /icons  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\icons"
        Alias  /images  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\images"
        Alias  /contentspots  "W:\IBM\WCDE_ENT70\workspace\StaticWeb\WebContent\contentspots"
    </VirtualHost>

    <VirtualHost *:8006>
        SSLEnable
        SSLClientAuth 0
        ServerName *
        Alias /wcsstore "W:\IBM\WCDE_ENT70\workspace\Stores\WebContent"
        Alias /wcs "W:\IBM\WCDE_ENT70\workspace\Stores\WebContent"
    </VirtualHost>

• Make sure that no other application is using Port 80. You might face the following issue and here is the resolution for the same,

        http://yashodtechtalk.blogspot.in/2016/05/wcs-ibm-http-server-failed-to-start.html

• Restart the HTTP Server
• After doing these changes, the static content will be served from HTTP Server rather than WebSphere Application Server

WebSphere Commerce 7 | IBM HTTP Server | Failed to Start | make_sock: could not bind to address 0.0.0.0:80

I tried to setup IBM HTTP Server V7.0 by following the document,
http://www.ibm.com/developerworks/library/co-config-httpserver-was/

After the setup, when I tried to start the server, here is the error message
(OS 10048)Only one usage of each socket address (protocol/network address/port)
is normally permitted.  : make_sock: could not bind to address 0.0.0.0:80
no listening sockets available, shutting down
Unable to open logs
Note the errors or messages above, and press the <ESC> key to exit.  16...
Tried multiple things to check whether any other application is running on Port 80. But I didn't noticed any application is running on Port 80.

Later noticed the issue and here it is,
In C:\WebSphere\IHS\conf\httpd.conf file, the following line got duplicated
Listen 0.0.0.0:80

After commented the duplicate line, It is working fine as expected

WebSphere Commerce 7 | User Management | Reset Password

In Development Environment, if you wanted to reset the user credentials, you can follow these steps,

Scenario - 1 (If you don't know the Merchant Key):
In local environment, if you know any user credentials (Eg: MyUser1) and need to update another user credentials (Eg: MyUser2) with the same, here is the setup of queries, you need to execute,

    update userreg set SALT = (select SALT from userreg where LOGONID = 'MyUser1') where LOGONID = 'MyUser2'
    update userreg set LOGONPASSWORD = (select LOGONPASSWORD from userreg where LOGONID = 'MyUser1') where LOGONID = 'MyUser2';
    update userreg set status = 1 where logonid = 'MyUser2';
    update userreg set passwordexpired = 0 where logonid='MyUser2'; 

Scenario - 2 (If you know the Merchant Key):
If you know merchant key and here is the procedure,

1. Run the command
    wcs_password.bat <Your required password> <Your required Salt> <Merchant Key>
   
    Eg: wcs_password.bat myPassw0rd mySalt 0123456789abcdef
   
   It will generate encrypted password,
  
    Eg: 34jmgfjspuw93rsjdfi-qua4wd
  
2. Run the following queries,
    update userreg set SALT = 'mySalt' where LOGONID = 'wcsadmin'
    update userreg set LOGONPASSWORD = '34jmgfjspuw93rsjdfi-qua4wd' where LOGONID = 'wcsadmin';
    update userreg set status = 1 where logonid = 'wcsadmin';
    update userreg set passwordexpired = 0 where logonid='wcsadmin';

WebSphere Commerce 7 | Access Control Policies | Inserting Access Control Policies through SQL

Here are the queries, you need to execute to insert Access Control Policies for Views and Controller Commands,

--Query for ACL to insert a View
INSERT INTO ACACTION (ACACTION_ID, ACTION) VALUES ((SELECT COUNTER FROM KEYS WHERE TABLENAME='acaction'), 'MyNewView');

INSERT INTO ACACTACTGP (ACACTGRP_ID,ACACTION_ID) VALUES
((SELECT ACACTGRP_ID FROM ACACTGRP WHERE GROUPNAME = 'AllSiteUsersViews'
AND MEMBER_ID IN (SELECT ORGENTITY_ID FROM ORGENTITY WHERE ORGENTITYNAME='Root Organization')
),(SELECT ACACTION_ID FROM ACACTION WHERE ACTION='MyNewView'));

UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acaction';

--Query for ACL to insert a Command
INSERT INTO ACRESCGRY
(ACRESCGRY_ID,RESCLASSNAME)
VALUES
((SELECT COUNTER FROM KEYS WHERE TABLENAME='acrescgry'),'com.ibm.commerce.sample.commands.MyNewControllerCmd');

INSERT INTO ACRESACT
(ACRESCGRY_ID, ACACTION_ID)
VALUES
((SELECT COUNTER FROM KEYS WHERE TABLENAME='acrescgry'),(SELECT ACACTION_ID FROM ACACTION WHERE ACTION='Execute'));

INSERT INTO ACRESGPRES
(ACRESGRP_ID, ACRESCGRY_ID)
VALUES
((SELECT ACRESGRP_ID FROM ACRESGRP WHERE MEMBER_ID IN
(SELECT ORGENTITY_ID FROM ORGENTITY WHERE ORGENTITYNAME='Root Organization') and GRPNAME='AllSiteUserCmdResourceGroup'),
(SELECT COUNTER FROM KEYS WHERE TABLENAME='acrescgry'));

UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acrescgry';

WebSphere Commerce 7 | Search | Click on Category Pages Failing | CWXFS0010E

After FEP8 migration and when category pages are clicked, it is throwing the following exception,

CWXFS0010E: Failed to execute search request because the current store does not have a valid search configuration.
        at com.ibm.commerce.catalog.facade.client.AbstractCatalogFacadeClient.checkShowCatalogNavigationView(AbstractCatalogFacadeClient.java:1796)
        at com.ibm.commerce.catalog.facade.client.AbstractCatalogFacadeClient.getCatalogNavigationView(AbstractCatalogFacadeClient.java:1827)

And later, I have noticed WC/xml/config/com.ibm.commerce.catalog-ext/wc-search.xml was empty with the following content,



Content of the file:
<_config:cores>
</_config:cores>

I have updated the file with the search-configuration and core information and it started working fine,

WebSphere Commerce 7 | User Login Failing | After Logout, it doesn't work

I have noticed in my local toolkit, User Registration was working fine. But, if you logout from the store, I am not able to login again with the same user. And in the logs also, I didn't notice any exception

Issue:
WC\xml\member\MemberRegistrationAttributes.xml was some how corrupted and it was empty

Fix:
After replaced WC\xml\member\MemberRegistrationAttributes.xml with the original file and after build and deploy, it started working fine

Thanks Vivek Kumar for quickly noticed the issue.

WebSphere Commerce 7 | Local Toolkit | "Loose Archive Resources path" Error

When ever you notice the following exception when you start the server in local environment,



Check whether all the paths and configurations are proper in the following files,
1. workspace\WC\xml\config\ToolkitModuleConfig.xml
2. workspace\.metadata\.plugins\com.ibm.etools.wrd.websphere.v7\looseconfigurations\WebSphereCommerceTestServer\WC\looseconfig.xmi

WebSphere Commerce 7 | Storefront | Enable "SearchBasedNavigation" through Queries

If you have problem logging in Management Center and  still wanted to enable Search Based Navigation through the Queries then here is the approach,

Queries to check whether Search Based Navigation is enabled,
select * from emspot where name = 'SearchBasedNavigation';
select * from DMEMSPOTDEF where emspot_id in (select emspot_id from emspot where name = 'SearchBasedNavigation'); 

Queries to enable Search Based Navigation for the store,
insert into emspot(emspot_id, storeent_id, name, usagetype, up_name) values ((select max(emspot_id)+1 from emspot), 10101, 'SearchBasedNavigation', 'STOREFEATURE', 'SEARCHBASEDNAVIGATION');
insert into DMEMSPOTDEF(dmemspotdef_id, emspot_id, storeent_id, contenttype, content, optcounter, sequence)
    values (
        (select max(DMEMSPOTDEF_ID) + 1 from DMEMSPOTDEF),
        (select emspot_id from emspot where name = 'SearchBasedNavigation' and storeent_id = 10101),
        10101,
        'FeatureEnabled',
        'true',
        0,
        0
    );

WebSphere Commerce 7 | Development Environment | Reset "wcsadmin" user password to "wcsadmin" | Script

If you have forgotten WCS Admin passwords in your development environments and wanted to reset the password to "wcsadmin", here is the script to apply on DB2 Database,

update userreg set logonpassword = x'74434f61354f51593862415655304d5268424e54723865685653356151374a2b353163506c4261363730633d202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020' where logonid='wcsadmin';
update userreg set salt = 'hsdbacehyoyn' where logonid='wcsadmin';
update userreg set status = 1 where logonid='wcsadmin';
update userreg set passwordexpired = 0 where logonid='wcsadmin';