WebSphere Commerce 7 | Dataload Jobs | Dataload Summary in the Logs | Feature Pack 6 Vs Feature Pack 8

With Feature Pack 6:

===============================================================================

WebSphere Commerce Data Load

===============================================================================

Load started at: Thu Sep 05 09:05:59 EDT 2013

Initialization completed in 2.229 seconds.

Processing CatalogEntry...

----------------------------------------------------------------------------------

Load summary for load item: CatalogEntry.

----------------------------------------------------------------------------------

Business Object Configuration: wc-loader-catalog-entry.xml

Data loader mode: Replace.

Batch size: 1.

Commit count: 100.

Error Tolerance Level: 1.

Error Count: 0.

Amount of business objects processed: 13.

Amount of business objects committed: 13.

Data loader initialization time: 0 seconds.

Data loader execution began: Thu Sep 05 09:06:07 EDT 2013

Data loader execution ended: Thu Sep 05 09:06:13 EDT 2013

Data loader completed in 5.765 seconds.

Total flush time: 0 seconds.

Total commit time: 0.003 seconds.

CSV file location: C:\IBM\WCDE_ENT70\bin\..\samples\DataLoad\Catalog\CatalogEntries.csv.

Affected tables (13):

Table name: BASEITEM, Affected number of rows: 5.

Table name: BASEITMDSC, Affected number of rows: 5.

Table name: STOREITEM, Affected number of rows: 5.

Table name: ITEMVERSN, Affected number of rows: 5.

Table name: DISTARRANG, Affected number of rows: 5.

Table name: CATENTRY, Affected number of rows: 13.

Table name: STORECENT, Affected number of rows: 13.

Table name: CATENTDESC, Affected number of rows: 13.

Table name: CATGPENREL, Affected number of rows: 13.

Table name: LISTPRICE, Affected number of rows: 13.

Table name: ITEMSPC, Affected number of rows: 8.

Table name: VERSIONSPC, Affected number of rows: 8

Table name: CATENTREL, Affected number of rows: 8

----------------------------------------------------------------------------------

Program exiting with exit code: 0.

Load completed successfully with no errors.

Load ended at: Thu Sep 05 09:06:13 EDT 2013

Load completed in 13.901 seconds.

With Feature Pack 8:

===============================================================================

WebSphere Commerce Data Load

===============================================================================

Load started at: Thu Sep 05 09:05:59 EDT 2013

Initialization completed in 2.229 seconds.

Processing CatalogEntry...

----------------------------------------------------------------------------------

Load summary for load item: CatalogEntry.

----------------------------------------------------------------------------------

Business Object Configuration: wc-loader-catalog-entry.xml

Data loader mode: Replace.

Batch size: 1.

Commit count: 100.

Error Tolerance Level: 1.

Error Count: 0.

Amount of business objects processed: 13.

Amount of business objects committed: 13.

Data loader initialization time: 0 seconds.

Data loader execution began: Thu Sep 05 09:06:07 EDT 2013

Data loader execution ended: Thu Sep 05 09:06:13 EDT 2013

Data loader completed in 5.765 seconds.

Total flush time: 0 seconds.

Total commit time: 0.003 seconds.

Total ID resolver time: 1.138 seconds.

CSV file location: C:\IBM\WCDE_ENT70\bin\..\samples\DataLoad\Catalog\CatalogEntries.csv.

Affected tables (13):

Table                |     Total |    Insert |    Update |    Delete

---------------------|-----------|-----------|-----------|-----------

BASEITEM             |         5 |         0 |         5 |         0

BASEITMDSC           |         5 |         0 |         5 |         0

STOREITEM            |         5 |         0 |         5 |         0

ITEMVERSN            |         5 |         0 |         5 |         0

DISTARRANG           |         5 |         0 |         5 |         0

CATENTRY             |        13 |         0 |        13 |         0

STORECENT            |        13 |         0 |        13 |         0

CATENTDESC           |        13 |         0 |        13 |         0

CATGPENREL           |        13 |         0 |        13 |         0

LISTPRICE            |        13 |         0 |        13 |         0

ITEMSPC              |         8 |         0 |         8 |         0

VERSIONSPC           |         8 |         0 |         8 |         0

CATENTREL            |         8 |         0 |         8 |         0

----------------------------------------------------------------------------------

Program exiting with exit code: 0.

Load completed successfully with no errors.

Load ended at: Thu Sep 05 09:06:13 EDT 2013

Load completed in 13.901 seconds.

WebSphere Commerce 7 | Dataload Jobs | Running & Debugging from RAD

Here is the way, you can run your run/debug your WebSphere Commerce Dataload Utilities from your RAD,

• Click on “Run Configurations”

 

• In “Main” tab, Provide Main class as “com.ibm.commerce.foundation.dataload.DataLoaderMain”

•  In the “Arguments” tab,
• In “Program arguments”, provide Dataload command with all the required parameters '
• In “VM arguments”, provide the value as “-Dj2se=true”
• In “Working directory”  --> other, provide bin folder path

 

• In the “Classpath” tab, add ojdb5.jar as External Jar (If you using Oracle as backend DB)

• While running the job, run it as the configuration, you have created

 

WebSphere Commerce 7 | SetDBType is failing | Issues Faced & Resolution | No data found in OOB Tables

After WebSphere Commerce Installation, we have migrated from Cloudscape to Oracle Database. After running "setdbtype" command with "createdb" option for the Oracle Database Schema, we have noticed SITE Table is not updated with the feature pack details and it was totally empty. And also, we have noticed most of the OOB tables hasn't populated (Tables are there, but data is not available in that). After running "updatedb" command also, we have the same result.

SETDBTYPE Command with CREATEDB Option,
setdbtype oracle C:\oracle\product\11.2.0\dbhome_1 orcl system Passw0rd wcsb2bdev Passw0rd localhost 1521 createdb

Later, we could able to notice the following error in messages log,
Exception in thread "main" java.lang.UnsatisfiedLinkError: ocijdbc11 (.\ocijdbc11.dll is not a valid Win32 application. )
 at java.lang.ClassLoader.loadLibraryWithPath(ClassLoader.java:1011)
 at java.lang.ClassLoader.loadLibraryWithClassLoader(ClassLoader.java:975)
 at java.lang.System.loadLibrary(System.java:469)
 at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3506)
 at java.security.AccessController.doPrivileged(AccessController.java:202)
 at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3502)
 at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:252)
 at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
 at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:148)
 at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
 at java.sql.DriverManager.getConnection(DriverManager.java:322)
 at java.sql.DriverManager.getConnection(DriverManager.java:358)
 at com.ibm.wca.dbconnect.DbConnectionImpl.connect(DbConnectionImpl.java:243)
 at com.ibm.wca.MassLoader.MassLoad.loadDbConnection(MassLoad.java:1176)
 at com.ibm.wca.MassLoader.MassLoad.execute(MassLoad.java:181)
 at com.ibm.wca.MassLoader.MassLoad.<init>(MassLoad.java:114)
 at com.ibm.wca.MassLoader.MassLoad.main(MassLoad.java:101)
Infocenter does provide some options to change it to a thin driver, but then I hit one error after another, and finally decided to give up and go with 32 bit version of Oracle software.\
.............
.............

As part of the resolution, we have download "ocijdbc11.dll" of Windows 32 and replaced it with one in the oracle folder "C:\oracle\product\11.2.0\dbhome_1\BIN". After we ran the process again and it started working fine as expected.

WebSphere Commerce 7 | Feature Pack 8 Installation | Issues Faced & Resolutions | Enable "content-version" Feature

When we were trying to enable "content-version" feature after installing WebSphere Commerce Feature Pack 8 with the following command,
enableFeature.bat -DfeatureName=content-version

Feature enablement has been failed with the following exception,
CV_enable:
com.ibm.commerce.context.version.ant.tasks.CreateVersionSchemaTask performTask() autoCommit: false
com.ibm.commerce.content.exception.ContentApplicationException: An error has occurred.  If this problem persists, contact product support.
    at com.ibm.commerce.context.version.objects.VersionSchemaUtility.createSchema(VersionSchemaUtility.java:487)
    at com.ibm.commerce.context.version.ant.tasks.CreateVersionSchemaTask.performTask(CreateVersionSchemaTask.java:298)
    at com.ibm.commerce.context.version.ant.tasks.CreateVersionSchemaTask.execute(CreateVersionSchemaTask.java:168)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
    at org.apache.tools.ant.Task.perform(Task.java:364)
    at org.apache.tools.ant.Target.execute(Target.java:341)
    at org.apache.tools.ant.Target.performTasks(Target.java:369)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
    at org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:37)
    at org.eclipse.ant.internal.core.ant.EclipseSingleCheckExecutor.executeTargets(EclipseSingleCheckExecutor.java:30)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
    at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:382)
    at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:107)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
    at org.apache.tools.ant.Task.perform(Task.java:364)
    at 
.................
.................
.................
.................
.................
  
BUILD FAILED

C:\IBM\WCDE_E~1\components\common\xml\enableFeatureForToolkit.xml:59: The following error occurred while executing this line:
C:\IBM\WCDE_E~1\components\common\xml\enableFeatureForToolkit.xml:126: The following error occurred while executing this line:
C:\IBM\WCDE_E~1\components\common\xml\databaseTasks.xml:50: The following error occurred while executing this line:
C:\IBM\WCDE_E~1\components\common\xml\databaseTasks.xml:344: The following error occurred while executing this line:
C:\IBM\WCDE_E~1\components\common\xml\databaseTasks.xml:357: The following error occurred while executing this line:
C:\IBM\WCDE_E~1\components\content-version\schema\xml\custom\fep2\customAction.xml:23: The following error occurred while executing this line:
C:\IBM\WCDE_E~1\components\content-version\schema\xml\custom\fep2\configureContentVersionComponent.xml:140: The following error occurred while executing this line:
C:\IBM\WCDE_E~1\components\content-version\schema\xml\custom\fep2\configureContentVersionComponent.xml:195: com.ibm.commerce.content.exception.ContentApplicationException: An error has occurred.  If this problem persists, contact product support.
    at com.ibm.commerce.context.version.objects.VersionSchemaUtility.createSchema(VersionSchemaUtility.java:487)
    at com.ibm.commerce.context.version.ant.tasks.CreateVersionSchemaTask.performTask(CreateVersionSchemaTask.java:298)
    at com.ibm.commerce.context.version.ant.tasks.CreateVersionSchemaTask.execute(CreateVersionSchemaTask.java:168)

We have fixed this issue with the following steps,

• Logged into DB with SYSTEM user credentials and noticed one schema has been created with the name VCW_XXX,

           SELECT * FROM dba_users WHERE USERNAME LIKE 'VCW_%'; 

 Deleted this schema with the following SQL Query,
           DROP USER VCW_DEMO CASCADE;

• Ran the enable "content-version" feature command and it worked as expected.

WebSphere Commerce 7 | Installation Procedure | Pointing from Cloudscape/Derby to Oracle

While doing the installation of WebSphere Commerce, it comes with Cloudscape/Derby DB. If you wanted to point to your Oracle Database, here is the procedure,

• Install Oracle Database
• Login with System/Sys User and create new User (Here I have created "wcsdev" User)

    a. create tablespace WCSADMIN datafile 'C:\oracle\oradata\ORCL\WCSADMIN.dbf' size 500M autoextend on;
    b. ALTER DATABASE DATAFILE 'C:\oracle\oradata\ORCL\WCSADMIN.dbf' RESIZE 1000M;
    c. create user wcsb2bdev identified by Passw0rd default tablespace WCSADMIN;
    d. grant connect, resource to wcsdev;
    f. grant create procedure, create sequence, create session, create synonym, create table, create trigger, create view, create materialized view to wcsdev;
    g. ALTER USER wcsdev QUOTA UNLIMITED ON WCSADMIN;

• Run SetDBType Command with "createdb" option, so that it creates WCS Schema for the newly created user,

    setdbtype oracle C:\oracle\product\11.2.0\dbhome_1 orcl system Passw0rd wcsdev Passw0rd localhost 1521 createdb

•  Enable all the features in the following sequence,

    enableFeature.bat -DfeatureName=foundation (Takes almost 1 Hr)
    enableFeature.bat -DfeatureName=management-center (Takes around 25 Min)
    enableFeature.bat -DfeatureName=store-enhancements (Takes around 10 Min) 
    enableFeature.bat -DfeatureName=social-commerce (Takes around 15 Min)
    enableFeature.bat -DfeatureName=content-version (Takes around 5 Min)
    enableFeature.bat -DfeatureName=location-services (Takes around 5 Min) 

•  Publish the required store

WebSphere Commerce 7 | Management Center | Failed to update/insert Catgroup information from Management Centre

I did some data cleanup activity in my local database for catgroups and after that, I was not able to perform any Catgroup data updates from Management Center. It is throwing the following exception in the console,

[2/5/14 17:11:46:498 IST] 00000039 servlet       E com.ibm.ws.webcontainer.servlet.ServletWrapper service SRVE0068E: Uncaught exception created in one of the service methods of the servlet /jsp/commerce/catalog/restricted/GetCatalogEntryOfferPrice.jsp in application WC. Exception created : com.ibm.websphere.servlet.error.ServletErrorReport: com.ibm.commerce.price.facade.client.PriceListException
    at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:693)
    at com.ibm._jsp._GetCatalogEntryOfferPrice._jspService(_GetCatalogEntryOfferPrice.java:371)
    at com.ibm.ws.jsp.runtime.HttpJspBase.service(HttpJspBase.java:99)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
    .........
    .........
   
Later, realized few Out of Box entries of CATGROUP & CATGRPTPC was also got deleted as part of cleanup activity. After inserting the missing entries, It started working fine,   
insert into catgroup(catgroup_id, member_id, identifier, markfordelete) values (0, 0, 'group0', 0);
insert into catgrptpc(catalog_id, catgroup_id, tradeposcn_id, store_id) values (10001, 0, 10001, 10051);
insert into catgrptpc(catalog_id, catgroup_id, tradeposcn_id, store_id) values (10001, 0, 10101, 10151);

WebSphere Commerce 7 | Storfront | HTTPS pages are showing blank page

I was playing around WebSphere Commerce Feature Pack 8 features and as part of it, I have published Aurora B2B Store. On the day, I have published it, StoreFront used to work fine and later, only pages related to HTTP are coming up fine and for HTTPS pages, it just shows the blank page. I have noticed, the requests are not reaching the Webserver too.

Later, noticed the HTTPS Port (443) is getting used by another application and it is the reason for my HTTPS WebSphere Commerce Storefront pages are not coming. I have updated the other application's port number instead of 443. After WCS Server restart, it is working fine.

In my case, after WebSphere Commerce installation, I have installed VMWare Workstation and it hijacked Port 443. After updating the Port number of VMWare Workstation, my application is coming up fine.

Windows command to check the applications running on a specific port number,
netstat -aon | findstr :443

Refer the following link to update VMWare Workstation port number,
https://www.computersnyou.com/266/how-to-solve-vmware-is-using-port-443/  

Thanks Rajesh Sripathi for your idea in resolving this issue or else, I might have beat my head looking into multiple areas to find the root cause.

Thought of posting this simple issue & resolution so that, you might help you and your way is WCS :)

WebSphere Commerce 7 | Custom Dataload | Check error count in the middle of the dataload

While doing dataloads, the following snippet will be used in wc-dataload.xml's load sequence,

<_config:LoadItem commitCount="20" batchSize="20" dataLoadMode="Replace" maxError="25" name="Sample_CatentryAttributesDataLoad" businessObjectConfigFile="wc-loader-catalog-entry-AD-attribute-relationship.xml">
         <_config:DataSourceLocation location="${dataloadParams}"/>
</_config:LoadItem>

If you are doing custom dataload, some of the records might fail to insert/update because of various reasons like parent key not found, integrity constraint etc

The "maxError" attribute is used specify after how many number of errors, the dataload job has to be aborted.

If you wanted to find out what is error count in the middle of the dataload, you can use the following statement in your custom dataload,

Integer errorCount = (Integer) getConfigProperties().getParentConfigProperties().getAttributeMap().get(DataLoadConstants.DL_ERROR_COUNT);

WebSphere Commerce 7 | Catalog Subsystem | Queries | Set 1

Long time pending post :)

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';