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.

WCS Component Service strusts configuration and invocation Details

1. Component Plugin Configuration in  struts-config-order-services.xml

<plug-in className="com.ibm.commerce.struts.ComponentPlugIn">

 <set-property property="componentId" value="order"/>

  <set-property property="clientFacadeClassName"        value="com.ibm.commerce.order.facade.client.OrderFacadeClient"/>

</plug-in>

2. Struts Action Mapping Configuration:

<action parameter="order.addOrderItem" path="/OrderChangeServiceItemAdd" type="com.ibm.commerce.struts.ComponentServiceAction">

<set-property property="authenticate" value="0:0"/>

<set-property property="https" value="0:1"/>

</action>

3. ComponetServiceAction.java class invokeService(ActionMapping mapping,HttpServletRequest request) method has following code which calls the appropriate method on component client facade

{

RequestHandle handle = (RequestHandle)request.getAttribute("EC_requestHandle");

Map responseMap = ComponentPlugIn.invokeComponentService(handle, inMap, mapping.getParameter());

}

Virtual Host Changes for WebServicesRouter Project: Default Port is 8000 which is secure port

HTTPServer plugin-cfg.xml path on app server or httpserver based on environments:
C:\IBM\WAS\profiles\wc_instancename\config\cells\cell_name\nodes\WC_instancename_node\servers\webserver1

Change
1. First open the commerce deployment manager admin console and update the virtual host mapping for WebServicesRouter project from VH_instancename_Tools to VH_instancename.
2. Click on enterprise application link and then click on save configuration link on this page. Don’t check synchronize with node check box and click OK.
3. Now click on nodes under system administration link and then select the appropriate node and click synchronize
4. Check logs , this project should bound to new virtual host group VH_wc_instancename not VH_wc_instancename_Tools

<UriGroup Name="VH_wc_instancename_wc_instancename_Cluster_URIs">
      <Uri AffinityCookie="JSESSIONID" AffinityURLIdentifier="jsessionid" Name="/webapp/wcs/stores/*"/>
      <Uri AffinityCookie="JSESSIONID" AffinityURLIdentifier="jsessionid" Name="/InitializationServlet/*"/>
<!-- added below uri so that webservicerouter project urls like /webapp/wcs/services/   can also be handled by virtual host group VH_wc_instancename-->
<Uri AffinityCookie="JSESSIONID" AffinityURLIdentifier="jsessionid" Name="/webapp/wcs/*"/>
</UriGroup>
<Route ServerCluster="wc_instancename_Cluster" UriGroup="VH_wc_instancename_wc_instancename_Cluster_URIs" VirtualHostGroup="VH_wc_instancename"/>







It can also be achieved through following way:

httpd.conf file path on sefags840:   C:\IBM\WCS\instances\wc_instancename\httpconf

<VirtualHost sefags840.secotools.net:8000>
SSLEnable
SSLClientAuth 0
ServerName sefags840.secotools.net
Alias /wcsstore "C:\IBM\WAS\profiles\wc_instancename\installedApps\WC_wc_instancename_cell\WC_wc_instancename.ear/Stores.war"
Alias /accelerator "C:\IBM\WAS\profiles\wc_instancename\installedApps\WC_wc_instancename_cell\WC_wc_instancename.ear/CommerceAccelerator.war/tools/common/accelerator.html"
Alias /wcs "C:\IBM\WAS\profiles\wc_instancename\installedApps\WC_wc_instancename_cell\WC_wc_instancename.ear/CommerceAccelerator.war"
Alias /wcwkspcadmin "C:\IBM\WAS\profiles\wc_instancename\installedApps\WC_wc_instancename_cell\WC_wc_instancename.ear/WorkspaceAdministration.war"
Alias /workspaceadmin "C:\IBM\WAS\profiles\wc_instancename\installedApps\WC_wc_instancename_cell\WC_wc_instancename.ear/WorkspaceAdministration.war/tools/workspaceadmin/wkspcadmin.html"
</VirtualHost>

Change
Comment out SSLEnable & SSLClientAuth 0 lines in above configuration like this 
#SSLEnable
#SSLClientAuth 0