WCS

WebSphere tools port. WebSphere Commerce tool Field Default port number WebSphere Commerce Accelerator WC Accelerator Port 8000 Administration Console WC Admin Port 8002 Organization Administration Console WC OrgAdmin Port 8004 WebSphere Commerce Preview WC Preview port 8006 WebSphere Commerce Preview WC Preview port 8007 Search Preview Servlet Search preview servlet port 3738 (SSL)

CSV worksheets for catalog data http://www-01.ibm.com/support/knowledgecenter/api/content/SSZLC2_7.0.0/com.ibm.commerce.user.doc/refs/rcacsvdata.htm?locale=en Sample worksheets attribute.csv attributeValue.csv category.csv categoryDescription.csv categoryProductRelation.csv categoryRelation.csv product.csv productDescription.csv price.csv 1. attribute.csv The attribute worksheet information maps to the ATTRIBUTE database table and contains the following fields: parentPartNumber The foreign key to the product's part number. languageId The language that this attribute pertains to. Available language components are: -1 = US English -2 = French -3 = German -4 = Italian -5 = Spanish -6 = Brazilian Portuguese -7 = Simplified Chinese -8 = Traditional Chinese -9 = Korean -10 = Japanese attributeType The type of attribute: FLOAT, INTEGER, or STRING. attributeName The name of the attribute. description A description of the attribute. descriptio...

Websphere Commerce Data Load using CSV \Websphere Commerce Data Load using XML Data can be loaded into WCS tables using data load utility. Data load utility works in the steps as mentioned below. 1.Data Reader The data reader reads the input file, and transforms the input into a data object. Data objects are typically map objects. Eg: CSV data reader \XML Data Reader 2.Business Object Builder The business object builder instantiates the business object and populates it from the data in the data object. Eg:The table object builder 3. Business Object Mediator The business object mediator converts the business object into physical object. The table object mediator is always used with the table object builder. Eg: table-based mediator 4. Data Writer The data writer writes the physical object into the database. This tutorial uses the default data writer to write the generated physical o...

  Websphere Commerce Catalog Subsystem The Catalog Subsystem contains all logic and data relevant to an online catalog, including catalog groups (or categories), catalog entries, and any associations or relationships among them.    Master Catalog It is the single catalog that contains all products, items, relationships, and standard prices for everything that is for sale in your store. Every store in the Web Sphere Commerce system must have a master catalog. It is possible to share the master catalog across stores. Master Catalog Restrictions 1)The master catalog must be a proper tree, which means that there are no cycles. e.g.The parent category "Women" has a subcategory "WomenDress". It is important that WomenDress and any of WomenDress's subcategories like tops,jeans etc are not the parent category of "Women" category. 2)A catalog entry or category cannot belong to multiple parent categories in the master...

Character Verification on Registration\Forgot Password | Captcha Recaptcha is a free solution hosted and owned by Google. Pros: Widely used in the industry, services based solution really easy to impelemnt Cons:The biggest minus on this is, if you have a strong firewall policy where the outgoing IP/Ports for app servers are blocked. This solution will not work as the IP addresses will change some times and hence it might not be feasible to dynamically open up the firewalls for the new addresses. Kaptcha is a free Apache license based java solution works with JDK 1.4.2, Pros: Really easy to implement and drop the jar files, no external webservices required. if you are using V6 and it also works with later versions of JDK hence this would work for V7. Kaptcha uses pixels and previous versions image libraries to refresh the image. Cons: Does not support audio. How to use in commerce? 1. Entry in web.xml for the servlet mapping, make this entry...

Cart Merge while User Logon in Websphere Commerce MigrateUserEntriesCmdImpl MigrateUserEntriesCmd will not do merge. It just migrates guest user details to current logged in user. We will have to use OrderItemMoveCmd for merge. OrderitemMoveCmd can only merge different orders of same customer only. That’s the reason we call MigrateUserEntriesCmd from LogonCmd first, followed by OrderItemMoveCmd. Consider the below order flow. Register a new user "testuser" in the site and logout before adding any items to cart.Come back to the site as a guest user.Add an item to cart .During checkout   try to login as "testuser" registered before.Order\OrderItems memberId will be changed from guest to that of registered user. There willn't be any order left for the Guest user.This can be achived with the help of MigrateUserEntriesCmdImpl . This task command is used to migrate resources owned by one user to another. The mandatory resources that are migrated are Address...

-- IBM documentation on WCS Scheduler data model -- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmjobs.htm -- Validate if a job is in active status, replace sccpathinfo with the job name select * from schconfig where sccpathinfo like '%DynaCacheInvalidation%' and sccactive='A'; -- Get the status of a scheduler job, replace sccpathinfo with the job name select * from schstatus where scsjobnbr in (select scsjobnbr from schconfig where sccpathinfo like '%DynaCacheInvalidation%' and sccactive='A') order by scsprefstart desc; -- Get the status of a scheduler job within a time range, replace sccpathinfo with the job name select * from schstatus where scsjobnbr in (select scsjobnbr from schconfig where sccpathinfo like '%DynaCacheInvalidation%' and sccactive='A') and scsprefstart between to_timestamp('02.01.14 08:00:00.000000','MM.DD.YY HH24:MI:SS:FF') and to_timestamp('02.01.14 ...

-- IBM documentation on WCS Promotions data model -- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmpromo.htm -- Total Order level promotion by order id SELECT amount as "total order level promotion" FROM ORDADJUST a, ORDERS b where a.orders_id = b.orders_id and orders_id ='<ORDER_ID>'; -- Get cart item, description and associated promotions at order item level SELECT d.code as "Promotion Name" , c.amount as "Order item promotion Amount", (SELECT partnum from orderitems where orderitems_id in (c.orderitems_id)) as "Part Number", (select name from catentdesc where catentry_id in (SELECT catentry_id from orderitems where orderitems_id in (c.orderitems_id)) and language_id='-1') as "Item description", (SELECT catentry_id from orderitems where orderitems_id in (c.orderitems_id)) as "Catentry ID" FROM ORDADJUST a, ORDERS b, ordiadjust c, calcode d where a.orders_i...

-- IBM documentation on WCS Payments data model -- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmedpaymnt.htm -- Get list of payments associated by SUBMITTED order id SELECT C.PPCPAYINST_ID,A.RESPONSECODE,A.TRACKINGID,A.REFERENCENUMBER,E.EDPPAYINST_ID FROM PPCPAYTRAN A,PPCPAYMENT B,PPCPAYINST C,EDPORDER D,EDPPAYINST E WHERE B.PPCPAYINST_ID = C.PPCPAYINST_ID and A.PPCPAYTRAN_ID = B.PPCPAYTRAN_ID AND C.STATE = 1 AND C.ORDER_ID = '<ORDER_ID>' AND D.ORDER_ID = '<ORDER_ID>' AND E.MARKFORDELETE = 0 AND D.EDPORDER_ID = E.EDPORDER_ID; -- Get a list of payments data associated with the current pending cart by login id SELECT * FROM PPCEXTDATA a, PPCPAYINST b, ORDERS c WHERE c.orders_id = b.order_id AND a.PPCPAYINST_id = b.PPCPAYINST_id AND c.orders_id in (select orders_id from orders where member_id in (select users_id from userreg where logonid like '%<LOGIN_ID>%') and status='P'); -- Get a ...

-- Query records that have been stage propogated from staging to runtime database select * from staglong where stgprocessed = 1 -- Query various adaptor properties SELECT K3.DESCRIPTION,INTERSPECATTNAME,INTERSPECATTVALUE FROM iseditatt K1, PROFILE K2, MSGTYPES K3 WHERE K1.PROFILE_ID = K2.PROFILE_ID AND K2.MSGTYPE_ID = K3.MSGTYPE_ID AND K1.PROFILE_ID IN (SELECT PROFILE_ID FROM PROFILE WHERE MSGTYPE_ID IN (SELECT MSGTYPE_ID FROM msgtypes WHERE name LIKE '%%')); -- Create ACP Policy entry for a new View, this is a quick and dirty approach -- alternate option is to make use of acpload utility -- replace MyNewView with the actual view name insert into acaction values ((Select max(acaction_id) from acaction)+1, 'MyNewView', null); insert into acactgrp values (10196, Select acaction_id from acaction where action='MyNewView', null, null); -- Generate update sql select concat(concat(concat('update keys set counter=',concat(concat( concat(concat('(select...

-- IBM documentation on WCS Order data model -- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmorder.htm -- Get user cart by the login id, replace USER_LOGIN_ID with the actual user login id select * from orders where member_id in (select USERS_ID from userreg where lower(logonid) = lower('USER_LOGIN_ID')) and status='P'; -- Modified version of the previous query for LDAP enabled WCS instance select * from orders where member_id in (select USERS_ID from userreg where lower(logonid) like lower('%USER_LOGIN_ID%')) and status='P'; -- Delete pending cart by user id, replace USER_LOGIN_ID with the login id -- Delete on Orders does not delete any of the EDPORDER related tables and hence we need to delete from both ORDERS and EDPORDER. delete from orders o where member_id in (select users_id from userreg where logonid like '%USER_LOGIN_ID%') and o.hstatus='P'; -- Check user cart with atleast one or m...

-- IBM documentation on WCS Member data model -- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmmember.htm -- Get total count of registered and Guest user count select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as "Report as of", (select count(*) from users where registertype='G') as "Guest User Count", (select count(*) from users where registertype='R') as "Registered User Count" from dual; -- Order or cart count site wider based on user registration type select u.registertype, o.status, count(*) as "Order/Cart Count" from orders o, users u where o.member_id = u.users_id group by u.registertype, o.status order by registertype, status; -- Get address details by login id select * from ADDRESS where member_id in (select users_id from userreg where logonid like '%<LOGIN_ID>%') and status='P'; -- Copying password in case of a locked out account -- This is a s...

-- Get Master Catalog associated to a Store, Query by Store Name SELECT * FROM storecat WHERE (storeent_id = (select storeent_id from storeent where identifier='AuroraESite' ) or storeent_id in (SELECT relatedstore_id FROM storerel WHERE store_id = (select storeent_id from storeent where identifier='AuroraESite' ) AND streltyp_id = -4)) AND mastercatalog = '1'; -- Get top categories of a master catalog select * from catgrpdesc where catgroup_id in (select catgroup_id from CATTOGRP where catalog_id in (select catalog_id from catalog where identifier='Extended Sites Catalog Asset Store') ) and language_id=-1; -- Get second level category of top level category Select * from catgrpdesc where catgroup_id in (select catgroup_id_child from catgrprel where catgroup_id_parent in (select catgroup_id from CATTOGRP where catalog_id in (select catalog_id from catalog where identifier='Extended Sites Catalog Asset Store'))) and language_id=-...