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.