WebSphere Commerce database query and caching

WebSphere Commerce database query and caching

 

 

In WCS, querying database can be cached on WC application and Search application, with slightly different settings.

 

Reference: http://www.ibm.com/support/knowledgecenter/en/SSZLC2_7.0.0/com.ibm.commerce.admin.doc/concepts/cdcaddcomdatcacheconfig.htm

 

WCS database query:

1.      Create new query template file, .tpl

http://www.ibm.com/support/knowledgecenter/en/SSZLC2_7.0.0/com.ibm.commerce.developer.soa.doc/concepts/csdqtf.htm

Create your new extended configuration directory, if not exists. For example,

xml\config\com.ibm.commerce.catalog-ext

Create a new tpl file, with wc-query- as name prefix.

In the body, create SQL in below format:

BEGIN_SQL_STATEMENT

name=SELECT_MY_TABLE

base_table=XMYTABLE

sql=SELECT COLUMN1,COLUMN2 FROM XMAYTABLE WHERE XMAYTABLE_ID = ?MAYTABLE_ID?

END_SQL_STATEMENT

2.      In your Java application, use JDBCQueryService to execute it, like this:

Map sqlParams= new HashMap();

sqlParams.put("MAYTABLE_ID", Collections.singletonList(getMyTableId()));

 

List<Map> nameValueDataSet =   new JDBCQueryService("com.ibm.commerce.catalog").executeQuery("SELECT_MY_TABLE", sqlParams);

 if(nameValueDataSet != null && !nameValueDataSet.isEmpty())

            {

                for(Map rowMap : nameValueDataSet)

                {

                      String column1Value= (String)rowMap.get("COLUMN1");

String column2Value= (String)rowMap.get("COLUMN2");

                     …

                }

            }

 

3.      Caching:

o   In WC application (Fix pack 9)

In order to cache this query, in wc-server.xml, locate "CrossTransactionCache" element

Add or update below attributes:

maxTimeToLiveForAutoCacheEntries="172800"

autoCacheableTableNames="XMAYTABLE XMAYTABLE2 XMAYTABLE3"

 

Make sure the value in cacheableTableNames are using space/tab/new line as delimiter.

maxTimeToLiveForAutoCacheEntries value cannot be 0, otherwise, it will not be cached.

 

o   In Search application (FEP7)

http://www.ibm.com/support/knowledgecenter/SSZLC2_7.0.0/com.ibm.commerce.developer.doc/concepts/csdwccomponentxml_fep7.htm

Create or update the wc-component.xml in extended directory, such as:

Search_eardir/xml/config/com.ibm.commerce.foundation-ext/wc-component.xml

 

Locate "CrossTransactionCache" XML element, append your customized table name into

"autoCacheableTableNames" attribute.  For example:

            

<_config:extendedconfiguration>

<_config:configgrouping name="CrossTransactionCache">        

 <_config:property name="CrossTransactionCache/autoCacheableTableNames" value="SRCHCONF SRCHCONFEXT SRCHATTRPROP SRCHATTR STORECAT STOREDEFCAT CATALOG XMYTABLE1 XMYTABLE2"/>

        </_config:configgrouping>

</_config:extendedconfiguration>