Format currency in WCS backend business logic instead of using JSTL tag in jSP

NumberFormat formatter = null;
formatter= NumberFormat.getCurrencyInstance(getCommandContext().getLocale());
               
DecimalFormat df = (DecimalFormat)formatter;
DecimalFormatSymbols dfs = df.getDecimalFormatSymbols();

dfs.setCurrencySymbol(getCurrencySymbol());
df.setDecimalFormatSymbols(dfs);
               
String formattedAmoun= df.format(amount);

WCS Web Activity Caching Issue

 If you have whole page cache setup for CategoryDisplay pages or any other pages and one of the eSpots on these pages used for creating web activities and this web activity for content recommendation does work when  you visit the page first time and once it is cached next hit to this page does not display the correct content as per web activity evaluation logic then possible cause could be your cachespec.xml config issue. Make sure that cache-entry for CategoryDisplay cache-id or any other cache-id setup for whole page cache (i.e. consume-subfragments is set to true) below  attributes highlighted in RED colour are excluded from save-attributes= false property list.

<cache-entry>
<class>servlet</class>
<name>com.ibm.commerce.struts.ECActionServlet.class</name>
<property name="consume-subfragments">true</property>
<property name="save-attributes">false
<exclude>jspStoreDir</exclude>
<exclude>javax.servlet.forward.path_info</exclude>
<exclude>requestURIPath</exclude>
<exclude>requestServletPath</exclude>
</property>
              <cache-id>
<component id="" type="pathinfo">
<value>/CategoryDisplay</value>
<required>true</required>
</component>
<component id="storeId" type="parameter">
<required>true</required>
</component>
<component id="catalogId" type="parameter">
<required>true</required>
</component>
<component id="DC_lang" type="attribute">
<required>true</required>
</component>
<component id="categoryId" type="parameter">
<required>true</required>
</component>
<component id="top_category" type="parameter">
<required>false</required>
</component>
<component id="parent_category_rn" type="parameter">
<required>false</required>
</component>
</cache-id>
<cache-entry>


Issue is that if whole page is cached then request to that page will be served directly from dynacache sever and backend controller command will not be executed. In this case WCS marketing engine uses command URL (e.g. CategoryDisplay) to execute the web activity target condition and for this to be executed correctly cached page must persist above mentioned request attributes for each category. This is specially applicable for web activities where target conditions are like customer views a specific category for example brake pads then recommend some content for eSpot.

How to run a .sql file from java process specially if data is huge in the database and jdbc connections time out due to long running query

private  void extractDataInCsv() {

    try {
   
   
    File file = new File(this.getClass().getResource("/com/ibm/commerce/data/OrdersDataExtract.sql").toURI());

       
        Runtime rt = Runtime.getRuntime();
        String  executeSqlCommand= null;
       
        if(getRequestProperties().getBoolean("localDev",false)){
        executeSqlCommand = "db2cmd /c /i /w && db2 -tvf "+file.getAbsolutePath(); // local dev  windows
        }else{
        executeSqlCommand = "db2 -vf "+file.getAbsolutePath();  // linux
        }
       
        Process pr = rt.exec(executeSqlCommand);
       
        BufferedReader in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String line = null;
while ((line = in.readLine()) != null) {
    System.out.println(line);
 }
//pause for 15se
Thread.sleep(15000);
       
     } catch (Exception e) {
     e.printStackTrace();
     }
}

Script in OrdersDataExtract.sql is as follows:


connect to dbname user dbusername using password
set schema db2inst1

-- extract data
EXPORT TO /opt/IBM/WebSphere/AppServer/temp/OrdersDataExtract.csv OF DEL MODIFIED BY NOCHARDEL striplzeros decplusblank select with ur
terminate

ShippingModeListDataBean does not return Shipping modes and Charges

If store reference data is all setup correctly and ShippingModeListDataBean still does not return allowed shipping modes and corresponding charges for a store then potential root cause could be that the shipping calcode for all products is not set for in table CATENCALCD.

A row in this table indicates that a CalculationCode is attached to a CatalogEntry. And it is also attached to the CatalogEntry's PRODUCT_ITEM children (or all CatalogEntries) for the specified Store. And also it is attached to TradingAgreement (or all TradingAgreements).

WCS applyCalculation Charges logic looks for indirectly attached calcode for a product and for that it looks into this table. There could also be directly attached calcode for 10% off promotion applied to a specific product then this calcode is attached to product directly.

IBM WCS tables used while creating new content as URL Asset

ATCHTGT -- ATTACHMENT TARGET
ATCHTGT (ATCHTGT_ID, STOREENT_ID, MEMBER_ID, IDENTIFIER, ATTACHUSG_ID, FIELD1, FIELD2, FIELD3, FIELD4, OPTCOUNTER)

ATCHAST -- This table holds the asset content url if is it url asset etc.
ATCHAST (ATCHAST_ID, STOREENT_ID, ATCHTGT_ID, ATCHASTPATH, DIRECTORYPATH, MIMETYPE, MIMETYPEENCODING, TIMECREATED, TIMEUPDATED, IMAGE1, IMAGE2, OPTCOUNTER)

COLLATERAL -- This table holds the content name and other info
COLLATERAL (COLLATERAL_ID, STOREENT_ID, COLLTYPE_ID, NAME, URL, FIELD1, FIELD2, OPTCOUNTER, BEHAVIOR)

COLLDESC -- other language dependent data for content
COLLDESC (COLLATERAL_ID, LANGUAGE_ID, LOCATION, MARKETINGTEXT, FIELD1, FIELD2, OPTCOUNTER, LONGMKTGTEXT)

ATCHREL -- This table holds the attachment relation between a business object and an attachment target; OBJECT_ID is same as COLLATERAL_ID
ATCHREL (ATCHREL_ID, OBJECT_ID, ATCHOBJTYP_ID, ATCHTGT_ID, ATCHRLUS_ID, LASTUPDATE, SEQUENCE, BIGINTOBJECT_ID, OPTCOUNTER)

List of IBM WCS tables related with e-marketing activity and asset content

atchast          
atchastlg              
atchrel                    
atchtgt                  
atchtgtdsc            
collateral              
colldesc                
dmactivity            
dmelement              
dmelementnvp        
dmemspotdef      
dmtriglstn              
folderitem

Solr Search Index setup for new languages in WCS v7

1.     Add the new supported language in the STORELANG database table for Extended Site Catalog Asset Store (storeId=10051).

2.    Perform the following steps for the following files:

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CatalogEntry/conf/locale

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CatalogEntry/unstructured/conf/locale

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CataloGroup/conf/locale

2.1.   Create a folder for your locale. For example, nl_NL indicates a Dutch language locale for Netherlands.

2.2.  Create the necessary files for your new locale directory.

a.    If language is already included in the existing locale files, copy the files from the original locale directory. For example, fr_FR, to the new directory, fr_NL.

b.    If language is not included in the existing locale files, use the file names and structure from another locale as a sample for its supported structure and organization for example en_GB.

2.3.  Optional: If there are any stop words for the language, create a file, stopwords.txt under the new directory for the schema.xml file. Save your changes and close the file.

2.4.  Copy the schema.xml file under new locale directory created in above step 2.2  file from Search project in SVN repository trunk

CatalogEntry Path: Search/solr/home/MC_10001/en_GB/CatalogEntry/conf/schema.xml

CatalogGroup Path:

Search/solr/home/MC_10001/en_GB /CatalogGroup/conf/schema.xml

3.    Update the following files for customizations if any for a specific project. Latest customized copy of these files can be found in the solrhome (WC_installdir/instances/instance_name/search/solr/home) for each core (e.g.

Search/solr/home/MC_10001/en_GB/CatalogEntry/conf/ wc-data-config.xml)

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CatalogEntry/conf/database/db2/wc-data-config.xml

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CatalogEntry/unstructured/conf/ database/db2/wc-data-config.xml

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CataloGroup/conf/ database/db2/wc-data-config.xml

           

4.    Update the following files for customizations if any for a specific project.

Directory: WC_installdir/components/foundation/samples/dataimport/catalog/db2

Files:   wc-dataimport-preprocess-attribute.xml

wc-dataimport-preprocess-fullbuild.xml

wc-dataimport-preprocess-unstructured-content.xml

Latest customized copy of these files can be found in the solr pre-processConfig directory WC_installdir/instances/instance_name/search/ pre-processConfig)

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CatalogEntry/conf/database/db2/wc-data-config.xml

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CatalogEntry/unstructured/conf/ database/db2/wc-data-config.xml

WC_installdir/components/foundation/subcomponents/search/solr/home/template/CataloGroup/conf/ database/db2/wc-data-config.xml

           

5.    Run the setupSearchIndex utility asper the process provided in HCL commerce documentation

For Local Dev environments need to run below script:

C:\WCDE_ENT70\components\foundation\subcomponents\search\bin>

setupSearchIndex.bat  -masterCatalogId 10001 –setupWebContent false

This script will also update database tables SRCHCONFEXT and SRCHCONF

6.    solr.xml config file will be updated with new locale cores once setupSearchIndex utility executes successfully.

File Location: solrhome (WC_installdir/instances/instance_name/search/solr/home)/MC_10001/solr.xml

7.    wc-search.xml config file will be updated with new locale cores once setupSearchIndex utility executes successfully.

File Location: WC/xml/config/ com.ibm.commerce.catalog-ext/wc-search.xml

8.    Once everything is done we need to run the solr pre-process and build-index scripts for new locales.