Thursday, 23 October 2014

Analyzing memory heaps from WebSphere Commerce

You need two install 2 plug-ins in RAD:
  1. Eclipse Memory Analyzer Plug-in: http://www.eclipse.org/mat/
  2. IBM Diagnostic Tool Framework for Java Plug-in: http://www.ibm.com/developerworks/java/jdk/tools/dtfj.html#download

This should allow you to read WCS dump files (*.phd). Install #1 then #2.
After that you can follow this tutorial to get ideas on what to investigate:

CoreMetrics | basics !!

Coremetrics provides web analytics and the other big competitor in this space is Omniture. The analytics integration is provided using Javascript. In Websphere commerce there is a tighter integration in the form of tags that are provided out of the box and  coremetrics reports are provided in management center in newer versions of WCS and older versions still need to use coremetrics site to view reports.


  1. Create a coremetrics account.
  2. Update WC\xml\config\bi\biConfig.xml with correct configuration for coremetrics configuration. clientId is the contract ID is usually one for test environments and 1 for production and ssoKey is generated from Coremetrics support.
  3. Configuration in wc-server.xml 
    • <configuration
                    cmClientID=""
                    password=""
                    serviceURL="https://wscreceiver.coremetrics.com/Receiver/sendEventData"
                    sslKeyPassphrase="WebAS"
                    sslKeyStore="/usr/WebSphere/AppServer/profiles/demo/etc/DummyServerKeyFile.jks"
                    sslTrustPassphrase="WebAS"
                    sslTrustStore="/usr/WebSphere/AppServer/profiles/demo/etc/DummyServerTrustFile.jks"
                    transmitClassName="com.ibm.commerce.bi.events.transmit.CMWebServiceTransmitter"
                    transmitEnabled="false" username=""/&gt
  4. Access Coremetrics reports
    • Open a Web browser to the following URL: https://welcome.coremetrics.com
    • Log in using your Client ID, username, and password. 
    • Starting V7, Feature Pack 3. We can also view reports directly from Management Center using links in the Catalogs, Marketing and Promotions tool.
There are several tags for CoreMetrics, one example is pageView and most commonly used is cm

Include the tag library in the pages
<%@ taglib uri="http://commerce.ibm.com/coremetrics"  prefix="cm" %>

PageView Tag: Most commonly used tag to track pages.

    <flow:ifEnabled feature="Analytics">
        <c:choose>
            <c:when test="${isCSR}">
                <cm:pageview pagename="${storeCountryCode}:SEARCH PAGE" extraparms="YES" />
            </c:when>
            <c:otherwise>
                <cm:pageview pagename="${storeCountryCode}:SEARCH PAGE" extraparms="NO" />
            </c:otherwise>
        </c:choose>
    </flow:ifEnabled>

Conversion tag: For Ajax calls

<cm:conversion eventId="${WCParam.eventId}" category="WISHLIST" actionType="2" points="10" returnAsJSON="true"/>

campurl Tag: Mostly for static pages

                              <cm:campurl espotData="${marketingSpotDatas}" id="clickInfoCommand" url="${clickInfoURLForAnalytics}"

References: More tags
http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.Coremetrics.doc/refs/rmttagsinstorejsps.htm
Web 2.0 integration:
http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.Coremetrics.doc/concepts/cmtweb20intro.htm

CMN8413E: The messaging system cannot find an appropriate profile | Enable transports in V7

If you see an error similar to the one below, most likely your transport is inactive.

[6/25/14 14:05:41:279 PDT] 00000047 CommerceSrvr  A com.ibm.commerce.messaging.outboundservice.Messaging loadAdminData(Integer,Integer,Integer) CMN8413E: The messaging system cannot find an appropriate profile for MSGTYPE_ID "101" to match an active transport of the STORE_ID "10051" in table PROFILE. Ensure that the MSGTYPE has been assigned to an active transport under the store or site.

Login into Admin console and activate Websphere MQ by changing status as show in the image on the right.

If the transports are not visible enable them from wc-server.xml. In V7 by default they are not enabled so

Search for the string below in wc-server.xml and make enable true and similarly you can do it for other transports: A couple of enabled transport xml snippets from wc-server.xml below.
<OutboundConnector
        default="true"
        enabled="true" id="3"
        name="JMS" retries="3">
...
  <OutboundConnector
        default="true"
        enabled="true" id="1"
        name="JavaMail" retries="3">
        <JNDI
          JndiName="eis/JCAEmail" display="false"/>
...

Catalog assets and merchandising associations - Part-4

Catalog groups and catalog entries are related by merchandising associations.


Here are some examples:
Cross-sell: 
                 For example, “If you liked this book, you may like these other books in the same genre.”
Up-sell: 
              For example, “If you like this computer, here is a slightly more expensive model with these extra   features.”
Accessory: 
               For example, “If you order this camera, you may be interested in these batteries, cases, and tripods”.
For each association, additional semantics can be used to refine the relationship.
Here are some examples.

Required: 
               For example, “You must have this game program in order to use this game expansion accessory.” 
Comes with: 
           For example, “This case is included with this laptop package and you can follow this link to see a detailed display.”
Optional: 
              For example, “This power supply is an optional accessory for this battery-powered MP3 player.”
It is important to note that the business objects used to represent the merchandising associations do not include language-dependent text suitable for building a shopper's view. The assumption is that a store developer, while adding logic to the Java server pages used to build the shopper's view, searches for a particular type of association. Since the association is precisely chosen, the store developer can easily pull descriptive text from the store's language-dependent properties file if a matching association is found.
The following tables includes catalog assets and merchandising associations.
  • Merchandising association (MASSOC)
  • Merchandising association type (MASSOCTYPE)
  • Related catalog group (MASSOCGPGP)
  • Related catalog entry (MASSOCCECE)
Merchandising association (MASSOC)
The merchandising association object defines a value used to refine the meaning of a type of merchandising association. Default values include REQUIRED, COMES_WITH, and TEMP. Note that the description field in this object is not language dependent and should not be used when building a shopper's view.

Merchandising association type (MASSOCTYPE)
The merchandising association type object defines a value used to associate different catalog groups or different catalog entries. Default values include X-SELL, UPSELL, and ACCESSORY. Note that the description field in this object is not language dependent and should not be used when building a shopper's
view.
Related catalog group (MASSOCGPGP)
The related catalog group, held in the MASSOCGPGP table, contains the merchandising associations that exist between CatalogGroups.
Related catalog entry (MASSOCCECE)
The related catalog entry, held in the MASSOCCECE table, contains the merchandising associations that exist between catalog entries.

Catalog assets and Store assets- Part-3

A catalog may be used by multiple stores, but a store is not required to sell every item or display every group in the catalog. Neither are stores required to display each item identically. These relationships allow store developers to customize how a catalog is used by a store without changing the catalog assets and, therefore, without affecting other stores that use the same catalog.
The following tables includes on catalog assets and store assets:
  • Store catalog (STORECAT)
  • Store catalog group (STORECGRP)
  • Store catalog entry (STOREENT)
  • Display catalog group relation (DISPCGPREL)
  • Display catalog entry relation (DISPENTREL)


Store catalog (STORECAT)
A store catalog object is used to link catalog objects to a store. Collectively, the set of all objects that contain the same storeent_id value are used to find the catalogs supported by the store.
Remember, a single store may use more than one catalog. For example, in addition to a master catalog, there
may be sales catalogs that organize the products differently, each optimized for a different type of shopper. The storeent_id is used to find the appropriate set of catalogs to display. For each object found, the catalog_id is used to find the appropriate catalog object. sales catalog proceeds as shoppers choose the catalog they would like to view.
Store catalog group (STORECGRP)
A store catalog group object is used to link catalog group objects to a store. Collectively, the set of all objects that contain the same storeent_id value are used to find the catalog groups supported by the store.
 
Store catalog entry (STOREENT)
A store catalog entry object is used to link catalog entry objects to a store. Collectively, the set of all objects that contain the same storeent_id value are used to find the catalog entries supported by the store.
The storeent_id is used to find the set of supported catalog entries. Given a list of catalog entries in the
catalog, the catalog entry is displayed only if it is also in the set of supported catalog entries.
Display catalog group relation (DISPCGPREL)
The display catalog group relation object is used to link a JSP name to a catalog group. Usually, a default page is provided for an entire site and, when necessary, an override is provided when the default page is inadequate. Overrides can be selected based on store, member group, device type, catalog group, language or any combination of these properties. A weighted best-fit technique is used when more than one relation satisfies the selection criteria.
Display catalog entry relation (DISPENTREL)
The display catalog entry relation object is used to link a JSP name to a catalog entry. Usually, a default page is provided for an entire site and, when necessary, an override is provided when the default page is inadequate. Overrides can be selected based on store, member group, device type, catalog entry type, catalog entry, language or any combination of these properties. A weighted best-fit technique is used when more than one relation satisfies the selection criteria.

Catalog and offering assets (price) Part-2

Every catalog entry object must have a base price (commonly called a default price) there may be any number of prices associated with a given item.
Example:
  1. There may be a price for each supported currency.
  2. There may be a special discounted price for senior citizens or for employees.
  3. There may be special business customer contracts with terms and conditions (minimum quantity, maximum quantity, shipping restrictions, and so on) that justify special prices.
This following tables includes catalog and offering assets.
  • Trading position container (TRADEPOSCN)
  • Catalog group trading position relation (CATGRPTPC)
  • Offer (OFFER)
  • Offer description (OFFERDESC)
  • Offer price (OFFERPRICE)
Trading position container (TRADEPOSCN)
A trading position container object organizes, by reference, a group of offers. The reference identifier tradeposcn_id value is used in all the offers in the container.
Typically, a developer begins by creating a single trade position container to contain the base prices for the entire catalog. In this container, the productset_id property is set to NULL and the type property is set to indicate that it represents the standard prices. When special prices need to be offered, the store developer creates additional trading position containers, one for each collection of related prices.
Catalog group trading position relation (CATGRPTPC)
The catalog group trading position relation is used to link a catalog or a catalog group to a trading position. If the catgroup_id property is set to 0, the relation applies to the entire catalog. Otherwise, the relation applies to the specified catalog group.
Offer (OFFER)
The offer object is used as a relation to link a particular catalog entry object and its related prices to a trading position container. It may also contain properties that qualify the applicability of the specified prices. Once a trading position container has been chosen, its reference identifier plus the reference identifier of the catalog entry can be used to find the appropriate offer object.
By convention, every catalog entry, regardless of type, has an offer in the base price list. You could argue that items could inherit their price from the related product, or all the item prices in a bundle could be summed to provide the price for the bundle. However, for convenience and performance while building a view, an invoice, or an order, each catalog entry is given its own offer, even when information appears to be redundant.
Offer description (OFFERDESC)
An offer description object contains language-dependent information about an offer. Although this object is not used in the sample stores, it can be used to add descriptive text about the price for viewing by a shopper. For example, it may say “For a limited time, only”, “While supplies last”, or “Special price because you are
an eligible Senior Citizen”.
Note the granularity of these descriptions, one per offer object. In most cases, all the offers in the same trading position container are there for the same reason. To have this information recorded for each offer can be very redundant. You may be tempted to use the description in the trading position container instead, but
that description is not language dependent and is therefore unsuitable for stores that support more than one language.
Offer price (OFFERPRICE)
The offer price object links a currency-dependent price to an offer. Once an offer has been chosen, its reference identifier and the desired currency identifier can be used to find the price to be displayed. Optionally, this object may contain a comparison price, in the same currency. When special prices need to be added to the site, additional offer price objects are created for each offer in the new trading position container.

Catalog assets (tables) and Description (useful info)-Part-1

  • Catalog (CATALOG)
  • Catalog description (CATALOGDSC)
  • Catalog group (CATGROUP)
  • Catalog group attribute (CATGRPATTR)
  • Catalog group description (CATGRPDESC)
  • Catalog catalog group relation (CATTOGRP)
  • Catalog group relation (CATGRPREL)
  • Catalog entry (CATENTRY)
  • Catalog entry attribute (CATENTATTR)
  • Catalog entry description (CATENTDESC)
  • Attribute (ATTRIBUTE)
  • Attribute value (ATTRVALUE)
  • Catalog entry relation (CATENTREL)
  • Catalog group catalog entry relation (CATGPENREL)
Catalog (CATALOG)

A catalog object is the root of each catalog, whether it is a master catalog or a sales catalog. The catalog contains all of the hierarchical and navigational information for the catalog. The primary key catalog_id is used in all the business objects that define the relationship of catalog groups or catalog entries.The description in this object is only for administrative purposes.


Catalog description (CATALOGDSC)

A catalog description object contains language-dependent information and an image suitable for viewing by a shopper. if store support more than one catalog, the information from the CATALOGDSC object is used to describe the shopper's choices.

Catalog group (CATGROUP)

The catalog group object is used to organize a catalog and help the shopper navigate to the products they wish to view. A category group may contain other category groups. Alternatively, a category group may contain catalog entries.
 The relationships are not kept in this object.

  • The catalog catalog group relation object, which links the foremost catalog groups to a catalog.
  • The catalog group relation object, which links child category groups with their parent group.
  • The catalog group catalog entry relation object, which links catalog entries with their parent catalog group.
When creating catalog groups, you must first arrange your catalog in a hierarchy. The root category branches out into increasingly specific subcategories. For example, Accessories is a grouping of the Men’s Fashions category, while the catalog groups wallets and hats are found nested within the Men’s Fashions
Accessories group.


Catalog group attribute (CATGRPATTR)

The catalog group attribute object contains descriptive attributes for catalog groups. Although the WebSphere Commerce sample stores do not use these objects, they are useful objects when a catalog group needs structured information to be included in its description. Unlike the catalog group description object, each descriptive attribute has both a name and a sequence property, necessary properties for building structured descriptions or comparison tables.
Typically, developer will use descriptive attributes when they wish to present a view that the shopper uses to compare similar catalog groups. The view would include a table with a row for each attribute name and a column for each catalog group to be compared. The sequence value is used to order the attribute names. The description from each catalog group attribute object would be displayed in the appropriate cell under the column assigned to its catalog group. The result is a side-by-side comparison table that the shopper can use to determine which catalog group they wish to view.
Sizes, styles, and other product features or functions can easily be used to organize the catalog into different groups. The name of the feature or function can then be used as attribute names. The values used to delimit the group can be used as attribute descriptions. Color could be defined to be an attribute; blue might be the assigned attribute value of a specific item.

Catalog group description (CATGRPDESC)

A catalog group description object contains language-dependent information and an image suitable for viewing by a shopper.
Typically, a developer will use the information in this object to produce a view that describes the catalog group for a commerce site. When the shopper navigates to a catalog group that contains other catalog groups, the information from this object is used to describe the shopper's choices for each child. The
shopper's preferred language or the current browser language are converted into the proper identifier together with the catgroup_id, to find the appropriate object.
Catalog catalog group relation (CATTOGRP)
The catalog catalog group relation object is used to link a catalog group to a catalog. Collectively, the set of objects that contain the same catalog_id value are used to find the root categories.
Typically, a developer will use the information in this object to select the catalog groups to be displayed initially. The catalog_id is used to find the appropriate set of objects to display.
Catalog group relation (CATGRPREL)
The catalog group relation object is used to link a child catalog group to its parent catalog group. Note that the relationship is qualified by a catalog_id. This gives the store developer the freedom to build different relationships for different catalogs, usually to optimize the navigation.
Typically, the store developer will use the information in this object to select the next set of catalog groups to display when the shopper selects a parent catalog group. The catalog_id and the parent's catgroup_id are used to find the appropriate set of objects to display. For each object in the set, the catgroup_id_child is used to find the appropriate catalog group object. Catalog navigation proceeds as shoppers choose the next catalog group to view.
Catalog entry (CATENTRY)
 The catalog entry object represents merchandise that can be ordered from the catalog. There are four different types of catalog entries, which can be identified by the value of the catenttype_id found in the CATENTRY element.
  • Item
  • Product
  • Package
  • Bundle
Item
An item represents a tangible unit of merchandise that can be purchased by a shopper. Viewed another way, an item represents anything that can be shipped to a shopper.
Product
A product represents a collection of items that have identical attributes but can be distinguished by their attribute values. For example, a catalog may contain a shirt that comes in different sizes and colors. Each unique combination of size and color values must be defined in an item, but, collectively, they can be displayed as a single product with a single price, description, and image as long as a shopper is given an opportunity to select the size and color values necessary to resolve the selection to a single item.
Package
A package is a collection of items that must be purchased as a single atomic unit. Even if its items can be ordered separately, a package has its own attributes, even its own price and part number, and cannot be  decomposed into individual items during navigation or after its placed in the shopping cart.
Bundle
A bundle is a collection of items that can be added to the shopping cart with a single selection. It is typically just a convenience for the shopper, a way to order a collection of items without navigating to each item individually. Unlike a package, each item in the collection is placed in the shopping cart and the shopper can modify (for example, change the quantity) or remove each item.
Catalog entry attribute (CATENTATTR)
The catalog entry attribute object contains descriptive attributes for catalog entries. Although the WebSphere Commerce sample stores do not use these objects, they are useful objects when a catalog entry needs structured information to be included in its description. Unlike the catalog entry description object, each descriptive attribute has both a name and a sequence property, which are necessary properties for building structured descriptions or comparison tables. The sequence value is used to order the attribute names.
Catalog entry description (CATENTDESC)
A catalog entry description object contains language-dependent information and an image suitable for viewing by a shopper.
Typically, regardless of the type of catalog entry, the developer will use the information in this object to produce a view that describes the catalog entry. When the shopper navigates to a catalog group that contains catalog entries, the information from this object is used to describe the shopper's choices for each child. The shopper's preferred language or the current browser language are converted into the proper identifier together with the catentry_id, to find the appropriate object.
Attribute (ATTRIBUTE)
The attribute object defines the name, qualified by language, of each attribute used to resolve a product catalog entry into an item that can be added to the shopping cart. Notice that its catentry_id is a reference back to a product catalog entry. Compare that to an attribute value object where the catentry_id refers back
to the item catalog entry that is identified with that value.
The product's catentry_id together with the shoppers preferred language (or the browser's current language converted into an identifier) is used to select all the attributes that are used to resolve the product to a particular item. The value of sequence is used to order the attributes on the view. If the selection criteria are not obvious from the attribute name, the values from description and description2 can be used to provide additional information to aid the shopper.
The attrtype_id can be used to dynamically choose the correct structure for the shopper's selection. The choices in each selection structure will be taken from the matching attribute value objects.
Attribute value (ATTRVALUE)
The attribute value object defines the value, qualified by language, of each attribute used to resolve a product catalog entry into an item that can be added to the shopping cart. Notice that its catentry_id is a reference back to an item catalog entry. Compare that to an attribute object where the catentry_id refers back to the product catalog entry that is identified with that value.
Catalog entry relation (CATENTREL)
The catalog entry relation object is used to link a catalog entry to its parent. To date, only three relationships are possible
  • PRODUCT_ITEM: An item can be linked to its product.
  • PACKAGE_COMPONENT: A catalog entry of any type can be linked to a package.
  • BUNDLE_COMPONENT: A catalog entry of any type can be linked to a bundle.
Notice that, unlike catalog groups, these relations are not qualified by catalog_id. These relations are the same no matter which catalog includes the catalog entry.
Typically, the developer will use the information in this object to determine what collection of merchandise is represented by a catalog entry collection, either to provide a more granular display or to determine what merchandise is to be added to the shopping cart. Once a collection is selected, the related objects are found by finding the objects where the catentry_id_parent value matches the collection's catentry_id value. For each catalog entry relation found, the catentry_id_child is used to find the appropriate catalog entry object to display or to include in the shopping cart. The value of sequence can be used to order the children in the view.
Special care must be exercised when a package or bundle contains a product. If this combination exists, the store developer must also include the means to resolve the product to a single item.
Catalog group catalog entry relation (CATGPENREL)
The catalog group catalog entry relation object is used to link a catalog entry to a catalog group. Note that the relationship is qualified by a catalog_id. This gives the store developer the freedom to build different relationships for different catalogs, usually to optimize the navigation.
Typically, the developer will use the information in this object to select the set of catalog entries to display when the shopper selects a parent catalog group. The catalog_id and the selected catgroup_id are used to find the appropriate set of objects to display. For each object in the set, the catentry_id is used to find the
appropriate catalog entry object. Catalog navigation proceeds as the shopper chooses a catalog entry to view, optionally adding the merchandise to the shopping cart in the process.

WCS | Catalog Subsystem | Queries

Here are few queries, which are used commonly in Catalog Subsystem tables,

Query to fetch all the Categories in a Sales Catalog:
----------------------------------------------------------------------------
SELECT c.catgroup_id, c.identifier, cd.name
FROM catgroup c, catgrpdesc cd, catgrprel cr
WHERE c.catgroup_id = cd.catgroup_id
and (cr.catgroup_id_parent = c.catgroup_id or cr.catgroup_id_child = c.catgroup_id)
AND cr.catalog_id = (select s.catalog_id from storedefcat s, storeent se where s.storeent_id = se.storeent_id and se.identifier = 'aurora')
union
SELECT c.catgroup_id, c.identifier, cd.name
FROM catgroup c, cattogrp ctg, catgrpdesc cd
WHERE c.catgroup_id = ctg.catgroup_id
and C.CATGROUP_ID = cd.catgroup_id
AND ctg.catalog_id = (select s.catalog_id from storedefcat s, storeent se where s.storeent_id = se.storeent_id and se.identifier = 'aurora');

Query to fetch Catentry, their Categories  in a Sales Catalog:
------------------------------------------------------------------------------
SELECT ce.catentry_id,
         ce.partnumber,
         CE.CATENTTYPE_ID,      
         ced.published published,
         ce.buyable,
         ce.mfname,
         ce.mfpartnumber,
         ced.name,
         ced.shortdescription shortdescription,
         cg.catgroup_id,
         cg.identifier,
         cger.catalog_id      
FROM catentry ce, catentdesc ced, catgpenrel cger, catgroup cg
WHERE ce.catentry_id = ced.catentry_id
       AND ce.catentry_id = cger.catentry_id
       and ce.catentry_id = ced.catentry_id
       AND cger.catgroup_id = cg.catgroup_id    
       AND cg.markfordelete = 0
       AND ced.language_id = -1
       --AND ced.published = 1
       AND ce.markfordelete = 0
       AND cger.catalog_id =
              (SELECT sdc.catalog_id
                 FROM storeent se,  storedefcat sdc
                WHERE se.storeent_id = sdc.storeent_id
                     AND se.identifier = 'aurora')
order by ce.catentry_id;

Query to fetch attributes of a given Partnumber:
--------------------------------------------------------------------------------------
SELECT ce.catentry_id,
         ce.partnumber,
         a.identifier AS attridentifier,
         (select name from attrdesc ad where ad.attr_id = cea.attr_id AND ad.attr_id = cea.attr_id) as attrname,
         (SELECT DECODE (a.attrtype_id, 'STRING', stringvalue, 'INTEGER', integervalue) FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS attrvalue
FROM catentry ce, catentryattr cea, attr a
WHERE ce.catentry_id = cea.catentry_id
       AND cea.attr_id = a.attr_id
       AND ce.partnumber = 'MotoG'
       ORDER BY a.identifier;

Fetch Attributes and its values for a given Partnumber:
------------------------------------------------------
SELECT ce.catentry_id,
         ce.partnumber,
         a.identifier AS attridentifier,
         (select name from attrdesc ad where ad.attr_id = cea.attr_id AND ad.attr_id = cea.attr_id) as attrname,
         (SELECT DECODE (a.attrtype_id, 'STRING', stringvalue, 'INTEGER', integervalue) FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS attrvalue,
         (SELECT identifier FROM attrval WHERE attr_id = a.attr_id AND attrval_id = cea.attrval_id) AS attrvalidentifier,
         ce.optcounter AS catentry_counter,
         cea.optcounter AS catentryattr_optcounter,
         a.optcounter AS attr_counter,
        (SELECT optcounter FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS attrvalue_optcounter
FROM catentry ce, catentryattr cea, attr a
WHERE ce.catentry_id = cea.catentry_id
       AND cea.attr_id = a.attr_id
       AND ce.partnumber = 'ReebokABC'
       ORDER BY a.identifier;

Query to fetch catentry, their categories, attributes and their values in the sales catalog:
---------------------------------------------------------------------------------------------------
SELECT ce.catentry_id,
         ce.partnumber,
         ced.name,
         ced.published published,
         ce.mfname,
         ce.mfpartnumber,
         ced.shortdescription shortdescription,
         cger.catalog_id,
         a.identifier AS attridentifier,
         (SELECT DECODE (a.attrtype_id, 'STRING', stringvalue, 'INTEGER', integervalue) FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS  attrvalue
FROM catentry ce, catentdesc ced, catgpenrel cger, catgroup cg, catentryattr cea, attr a
WHERE ce.catentry_id = ced.catentry_id
       AND ce.catentry_id = cea.catentry_id
       AND ce.catentry_id = cger.catentry_id
       AND cger.catgroup_id = cg.catgroup_id
       AND cea.attr_id = a.attr_id
       AND cg.markfordelete = 0
       AND ced.language_id = -1
       AND ced.published = 1
       AND ce.markfordelete = 0
       AND cger.catalog_id =
              (SELECT sdc.catalog_id
                 FROM storeent se,  storedefcat sdc
                WHERE se.storeent_id = sdc.storeent_id
                     AND se.identifier = 'aurora')
order by ce.catentry_id;

Query to fetch merchandising association of a given Partnumber:
--------------------------------------------------------------------------------------------
SELECT c1.partnumber AS sourcepartnumber, c2.partnumber AS targetpartnumber, massoctype_id, m.Rank, store_id
FROM massoccece m, catentry c1, catentry c2
WHERE m.catentry_id_from = c1.catentry_id
AND m.catentry_id_to = c2.catentry_id
AND c1.partnumber = 'MotoG';

Query to fetch attachments associated with for a given Partnumber:
----------------------------------------------------------------------------------------------
SELECT c.partnumber, atg.identifier AS attachmentId,
(SELECT atchastpath FROM atchast WHERE atchtgt_id = atg.atchtgt_id) AS attachmentPath
FROM atchrel a, atchtgt atg, catentry c
WHERE a.bigintobject_id = c.catentry_id
AND a.atchtgt_id = atg.atchtgt_id
AND c.partnumber = 'MotoG';

WCS | WebSphere Commerce Search and Apache Solr - Setup

1. Make sure you have enabled the Feature pack 2 features
2. Through Management Center, enable the search-based navigation store function
3. Get the Master Catalog Id for your store,
        select * from catalog where IDENTIFIER='YOUR_STORE_IDENTIFIER'; --10001
4. Setup the Search Index (Preparing the WebSphere Commerce machine)
    This utility ensures that your index is built successfully using your WebSphere Commerce master catalog data.
    a.    First stop the server and as non-root user go to
        Server: WC_installdir\components\foundation\subcomponents\search\bin
        Developer: WCDE_installdir\components\foundation\subcomponents\search\bin
    b.    Run the search index setup utility,
        Server: ./setupSearchIndex.sh -instance demo -action configWCforSolr -masterCatalogId 10001 -dbuser db2inst1 -dbuserpwd inst1pas -searchServerName linux-530s -searchServerPort 3737 -searchServiceContextRoot /solr
        Developer:    
        setupSearchIndex.bat -masterCatalogId 10001 -instance WCDE_ENT70 -dbuser db2admin -dbuserpwd db2admin [-solrhome C:\IBM\WCDE_ENT70\search\solr\home]
    c.    Check the logs,
        WCDE_ENT70\components\foundation\subcomponents\search\log\wc-search-index-setup.log
    d.    This utility update the following information,
        i.    wc-search.xml is updated with appropriate Solr configuration information.
        ii.    One record was inserted into the database table SRCHCONF

    e.    Verify the following assets are created,
        Server: cd /opt/IBM/WebSphere/CommerceServer70/instances/demo/search/solr/home
        Developer: IBM\Toolkit\search\solr\home

5. Preprocessing the WebSphere Commerce search index data
    The preprocess utility extracted WebSphere Commerce data and put the data into a set of temporary tables inside the WebSphere Commerce database.
    a.    Server should be stopped
    b.    As non-root user, go to
        WCDE_installdir\bin
    c.    Run the preprocessing utility,
        Server: ./di-preprocess.sh /opt/IBM/WebSphere/CommerceServer70/instances/demo/search/pre-processConfig/MC_10001/DB2 -instance demo -dbuser db2inst1 -dbuserpwd inst1pas -fullbuild true
        Developer: di-preprocess.bat C:\IBM\WCDE_ENT70\search\pre-processConfig\MC_10001\DB2\ [-fullbuild true] [-localenameen_US]
    d.    Check the logs,
        Server: WC_installdir\logs\wc-dataimport-preprocess.log
        Developer: WCDE_installdir\logs\wc-dataimport-preprocess.log
    e.    Verify that the TI_<NAME>_0 tables were created and populated

6. Building the WebSphere Commerce search index   
    The index building utility is a wrapping utility that updates the information in the master index using the Data Import Handler (DIH) service to build the index, either partially through delta index updates or completely through full-index builds. When there are multiple indexes, for example, each language using its own separate index, the index is built multiple times.
    a.    Start the server
    b.    Check whether Solr server is working or not,
        http://localhost/solr/Default/select?q=*%3A*
    c.    Run search index building utility,
        Server: ./di-buildindex.sh -instance demo -masterCatalogId 10001 -dbuser db2inst1 -dbuserpwd inst1pas
        Developer: di-buildindex.bat -masterCatalogId 10001
    d.    Check the logs,
        Note: The di-buildindex.bat utility overwrites the preprocess log file. Before running di-buildindex.bat, back up wc-dataimport-preprocess.log.
        Server: WC_installdir\logs\wc-dataimport-preprocess.log
        Developer: WCDE_installdir\logs\wc-dataimport-preprocess.log
    e.    Check the Solr search after building indexes,
        http://localhost/solr/MC_10001_CatalogEntry_en_US/select?q=catentry_id:10251

Default contract

A default contract defines the default customer entitlement for a store or a group of stores. The default contract applies to all customers shopping at a store who do not have any other contract with the store, for example, guest customers.

A typical default contract for a store specifies the following information:


  • Customers can purchase all products available in the master catalog for the store at standard prices set in the master catalog (no discounts or mark-ups).
  • Any shipping charges are paid to the seller (store).
  • Payment is specified during order processing. The customer can use any payment method accepted by the store.
  • The customer can select any shipping provider supported by the store to ship an order.
  • The ship-to address for an order is specified by the customer during order processing.
  • Customers can return purchases without penalty charges within a certain number of days.
  • Customers can receive refunds using the same payment method used for the original purchase.

In order to create a default contract, you must define the contract, the contract owner, the contract description, the contract participants, and the terms and conditions of the contracts. Contract information is stored in four tables: CONTRACT, PARTICIPNT, TRADING, and TERMCOND.
About this task

The default contract is associated with a store using the STOREDEF database table. For WebSphere Commerce Enterprise users, contracts other than the default contract are associated with a store using the STORECNTR database table.

You can create a default contract in XML, based on one of two formats: XSD or DTD.

What is password invalidation? How to Enable?

Password invalidation, when enabled, requires WebSphere Commerce users to change their password if the user's password has expired. In this case, the user is redirected to a page where they are required to change their password. Users are not able to access any secure pages on the site until they have changed their password.

Enable password invalidation

There are two way can enable password invalidation.

First Step: Directly modify wc-server.xml file.

  1. Locate wc-server.xml file
  2. Update strPasswordInvalidationSetting = WcsApp.configProperties.getValue("PasswordInvalidation/enabled", "false");
  3. Restart your WebSphere Commerce instance.

Second Step: Using Configuration Manager.

  1. Open the Configuration Manager.
  2. Traverse to the Password Invalidation node for your instance as follows: WebSphere Commerce > node_name > Commerce > Instance List > instance_name > Instance Properties > Password Invalidation
  3. To activate the password invalidation feature, click the Enable check box.
  4. To apply your changes to Configuration Manager, click Apply. Upon successfully updating the configuration for your instance, you will receive a message indicating a successful update.
  5. Restart your WebSphere Commerce instance.
Note: From the above, Websphere best practice is Second Step: (Using Configuration Manager)

How do enable web service(Webservices over HTTP) in Websphere commerce?

1. Open the Administration Console and select Site or Store.
2.
Click Configuration > Transports.
3.
The Webservices over HTTP transports should appear in the list and as inactive.

If the transport want to configure is active

  1. Select the check box next to the inactive transport.
  2. Click Change Status.
  3. The transport status should change from Inactive to active.

Configuring tracing and logging for the loading utilities

you can configure an additional message log file, messages.txt, and a trace file, traces.txt, for the loading utilities. This can be helpful in getting more information to help determine any problems you may encounter.

1. Open the following in a text editor:

WC_installdir/xml/loader/WCALoggerConfig.xml

Note: There are two locations of the WCALoggerConfig.xml file. WCDE_installdir/xml/loader/WCALoggerConfig.xml is used by the loader utilities when run from the command line, and the file WC_installdir/instances/ instance_name/xml/loader/WCALoggerConfig.xml is used by the loader utilities in the server context, for example during the store publish process.


The component tags define the components for which trace is enabled.

Disable Access Control Policy WCS (Development)

Developer while creating new Views commands controllers is to define the access control policy, Although it is a must have step for security reasons, I have always wondered why we need to do this during development time, Just to test and views without having to create the policies during development. Well yes, It's possible with a simple configuration.

In toolkit, navigate to your instance.xml file, on WCS 7 , this will be typically located in
D:\IBM\WCDE_ENT70\workspace\WC\xml\config\wc-server.xml



In toolkit, navigate to your instance.xml file, on WCS 7 , this will be typically located in
D:\IBM\WCDE_ENT70\workspace\WC\xml\config\wc-server.xml

you have to find xml elemented named "Instance

AccessControlUnitTest="true"

and place above attribute with value immediately after "Instance" Elements

Save and restart your test server, from now on any controller and view command that you create will not require a acppolicy to be loaded.

Deleting order items and requisition lists

When a user deletes an order item or requisition list item the table rows representing those items are removed from the database. If enabled, these rows are updated to belong to the trashcan. You can clear the trashcan by running the OrderTrashCanClear scheduled job which will delete the rows, or by using the Database Clean up utility. This feature is disabled by default.

It is highly recommended that you clean your database of order items both before you enable the OrderTrashCan feature, and periodically afterwards. Order item deletion is a common operation, and as a result, a large number of deleted order items can build up in the ORDERITEMS table.

1. Open wc-sever.xml configuration file
2. Within the file, the text can be put next to "ExpectedInventorySynchronize" element.


TrashCanRequisitionListSegmentLen="30"
TrashCanOrderItemSegmentLen="50"
TrashCanEnabled="true"/>
This setting deletes order item entries from the ORDERITEMS table. If TrashCanEnabled is set to "true", when a user deletes an order item or requisition list item, the table rows representing those items are not deleted from the database. Instead, the items are updated to belong to the trashcan.

massextract utility example

Mass extract (massextract) is an OOB utility which can be used to extract data from any tables in IBM WCS. Use the massextract utility to extract selective subsets of data from the WebSphere Commerce database in the form of XML files.
You can extract data on products related to an upcoming holiday, for example, or you can extract information from a consolidated database for use with other systems.

Step-1

Pre-Request for before running this utility

i. Assume that environment variable setting done.
ii. JVM Setting (optional)
iii. (Oracle) Optional: By default, the massextract utility uses the Oracle thick JDBC client. To change the massextract utility to use the Oracle thin JDBC client.

Step-2

Create an extraction filter: An extraction filter is an XML file that defines the data want to extract from the WebSphere Commerce database. Must create an extraction filter before run the massextract utility.

Procedure to extraction filter

1. Develop SQL queries for the data you want to extract from the WebSphere
Commerce database
.

Extraction filter format


This tag and its closing tag define the extraction filter. All of the text in the extraction
file must be contained within this tag.

This tag defines the SQL query used to extract data from the database.
Every tag requires one or more tags.

This tag is responsible for the running of the SQL query. In this tag, you can also
specify for any values required for variable parameter defined in the tag.
Multiple tags can be associated with one tag.

Example:

1. Create file called CatentryFilter.xml
2. Add the following content to CatentryFilter.xml




3. Finally execute the below extract utility

./massextract.sh -filter /opt/IBM/WebSphere/CommerceServer60/bin/CatentryFilter.xml -outfile /opt/IBM/WebSphere/CommerceServer60/bin/CatentryFilterExtracted.xml -dbname PPSTGDB -dbuser MIPRODSTA -dbpwd MIPRODSTA -customizer OracleConnectionCustomizer


Note: we can query multiple tables using join conditions.

Enabling JDBCAccessBean for Management Center

Use WebSphere Application Server administrative console to trace queries serviced by the data service layer.

a. Set the log level setting to 'all' for the com.ibm.commerce.foundation.server.services.dataaccess* component in the WebSphere Application Server Administrative console.

Note: To trace the UPDATE and INSERT queries, set the log level setting to 'all' for all classes under component com.ibm.ws.sdo.mediator.jdbc (com.ibm.ws.sdo.mediator.jdbc*=all). You must restart the server after enabling this trace.

Understanding Access Control Policy

Access control in WCS is composed of the following elements:

1. Users - Users are the people that use the System. so users must be grouped into relevent access group.
2. Actions - Actions are the activities that users can perform on the resource. so action must also be grouped into relevent action groups.
3. Resources - Resources are the entities that are protected. Ex: if the action is a view, the resource to be protected is the command that invoked the view.
4. relationships - are the relationship between the user and the resource.

There are two level/type ACP.

1. Command-level ACP - The first level of ACP is performed by the WAS. In this respect, WC uses WAS to protect Enterprise beans and servlets.
2. Resource-level ACP - The Second level ACP is the fine-grained access control system of WCS.

Note: A command-level ACP alwasy has the ExecuteCommandActionGroup as the action group for Controller command. for views, the resource group is always ViewCommandResourceGroup. (ACPOLICY, MBRGRP, MBRGRPCOND, ACACTGRP, ACRESGRP)

ACP are enforced by the access control policy manager. when a user attempts to access a protected resource, ACP manager determines what ACP are applicable for that protected resource, and based upon the applicable ACP, it determines if the user is allowed to access the requested resources.

An ACP is a 4-element that is stored in the ACPOLICY table. Each access control policy takes the following form:

AccessControlPolicy [UserGroup, ActionGroup, ResourceGroup, Relationship]

The above elements say, a user belonging to a specific user group is permitted to perfrom action in the specified ActionGroup on resource belonging to the specified ResourceGroup, as long as the user satisfies the conditions specified in the Relationship or relationship group, with respect to the resource in question.

Ex: [AllUsers, UpdateDoc, doc, creator] specifies that all users can update a document, if they are the create of the document.

  • The user group is a specific type of member group that is defined in the MBRGRP table.
  • A user group must be associated with member group type of -2. the value of -2 represent an access group and is defined in the MBRGRPTYPE table.
  • Association between the usergroup and membergroup type is stored in the MBRGRPUSG table.
The membership of a user into a particular user group may be stated explicitly or implicitly. An explicit specification occurs if the MBRGRPMBR tables states that user belongs to a particular member group. An implicit specification occurs fi the user satisfies a condition (all users that fulfill the role of Product Manager) that is stated in the MBRGRPCOND table.

Most conditions to include a user in a user group are based upon the user fulfilling a particular role. Ex: there could be ACP that allows all users that fulfill the Product Manager role to perform catalog management operations. in this case, any user that has been assigned the Product Manager role in the MBRROLE table is implicitly included in the user group.

The ActionGroup elements comes from the ACACTGRP table. An action group refers to an explicitly specified group of actions. The list of actions is stored in the ACACTION table and the relationship of each action to its action group(or group) is stored in the ACACTACTGP table.

An example of an action group is the "OrderWriteCommands" action group. this action group includes the following action that are used to update orders

  • OrderDeleteCmd
  • OrderCancelCmd
  • OrderAProfileUpdateCmd
  • OrderUnlockCmd
  • OrderScheduleCmd
  • ScheduledOrderCancelCmd
  • ScheduledOrderProcessCmd
  • OrderItemAddCmd
  • OrderItemDeleteCmd
  • OrderItemUpdateCmd
  • PayResetPMCmd

The resource group is mechanism to group together particular types of resources. membership of a resource in a ResourceGroup can be specified in one of two ways.

using the conditions column in the ACRESGRP table
using the ACRESGPRES table.

in most case, it is sufficient to use the ACRESGPRES table for associating resources to resource group. using this method, resources are defined in the ACRESCGRY table suing their java class name.

These resources are associated with appropriate resource group(ACRESGRP) using the ACRESGPRES association table. in this case where the java class name alone is not sufficient to define the members a resource group.

The ACP can optionally include either a Relationship or RelationshipGroup element as its fourth element.

if ACP uses a Relationship element, this come from the ACRELATION table. if on other hand, it includes a RelationshipGroup Element, that comes from the ACRELGRP table. A ReletionshipGroup specification from the ACRELGRP table takes precedeence over the Relationship information from the ACRELATION table.

Generate useful WCS password

Resetting the wcsadmin user password (Runtime)

Steps:

1. Enter the following SQL statement:

select salt from userreg where logonid='wcsadmin';

2. Run the following command line script from the WC_installdir/bin directory:

$ wcs_password <password> <salt> <merchant key>;

For example:

$ wcs_password password hsdbacehyoyn 0123456789abcdef

3. Enter the following SQL statement:

update userreg set logonpassword=<hex encrypted version of password output from the wcs_password script> where logonid = 'wcsadmin';

For example:

update userreg set logonpassword=x'74434f61354f51593862415655304d5268424e54723865685653356151374a2b353163 where logonid = 'wcsadmin';



Generate WebSphere Commerce encrypted password (wcs_password)

The wcs_password utility is used for encrypting user passwords to be stored in the WebSphere Commerce database.

Passwords are encrypted with a merchant key. The value of the merchant key could be specified through command line arguments or retrieved from calling out to the Key Locator Framework using the "-k" parameter.

Example 1

Encrypt password when merchant key is not retrieved from the Key Locator Framework: wcs_password pass1word rfKtuve 1234567890abcdef

Example 2

Encrypt password when merchant key is retrieved from the Key Locator Framework:

wcs_password pass1word rfKtuve -k WC_installdir\instances\xml\CustomKeys.xml


Changing the Configuration Manager password

You can change the password to access the Configuration Manager client. Two methods are available to change the password.

1. Change to the following directory
     WC_installdir/bin
2. Issue the following command
     ./wcs_encrypt.sh new_password

Two encrypted versions of the new password will be generated:

    ASCII encrypted string
    HEX encrypted string
 

3. Open the PwdMgr.xml file in a text editor
    WC_installdir/instances
4. Modify LoginPassword in the PwdMgr.xml file with the ASCII encrypted password from step 2 and save your changes.


Generating WebSphere Commerce encrypted passwords

You can generate encrypted passwords in order to manually reset the password of a user from a command line. There are other tools (such as the ResetPassword command) that accomplish the same task. To manually reset the password, the administrator would take the encrypted password that is output by the utilities and update the LOGONPASSWORD field of the USERREG table. The administrator would also update the SALT field of the USERREG table with the chosen salt.


1. Change to the following directory
     WC_installdir/bin
2. Run the wcs_password to generate the encrypted password.

3. To change the encrypted password for customers, use the chgwcspwd.sh command.

    Navigate to the following directory: WC_installdir/bin
    Run the following script: chgwcspwd.sh (The usage parameters will be displayed.)
    Run the command again using the appropriate parameters.

How to find enabled WebSphere Commerce features

You can list the features that have been enabled on WebSphere Commerce.

1. Navigate to the following directory
    
    WCSDIR/bin

2. Run the check enablement status script
    
   checkEnablementStatus.bat


3. Confirm the feature enablement status from the following location.

   WCSDIR/logs\enableFeature.log



Sample Screen




How WCS selects the correct price from OFFER table?

Offers for catalog entries are stored in the OFFER table. When setting pricing for catalog entries, WC uses specific criteria to find valid offers from the OFFER table and then select the correct price.
To set the price for a catalog entry, WC must first determine all the valid offers for each applicable price list. To be valid, the offer must meet the following criteria according to data in the OFFER table: 
  1. The offer must be published, that is, the PUBLISHED value must be 1.
  2. The quantity of the catalog entry being purchased must be within the MINIMUMQUANTITY and MAXIMUMQUANTITY values for the offer.
  3. The purchase date must be within the defined STARDATE and ENDDATE values for the offer.
If you load pricing data into WC using the data loader utility or SQL statements, it is possible to have more than one valid offer for a catalog entry. If there is more than one valid offer, WC sets the price using the offer with the highest value in the PRECEDENCE column of the OFFER table. If multiple offers exist with that same highest precedence value, the offer with the lowest price is used.
Price rules and offer price selection
If your store uses price rules for storefront pricing, the price rule also retrieves offers from the OFFER table, but then the price rule might perform additional calculations on the offer price.
In this price rule, the Get Price from Price List action retrieves the offer from the OFFER table and then performs the mark down and rounding on the offer price. To evaluate and select the correct offer to retrieve from the OFFER table, WC uses the same criteria described earlier in this topic.