IBM WebSphere Commerce software provides powerful sell-side solutions to handle the challenges encountered in customer and trading partner environments. It can implement B2C, B2B, or private exchange business models using open, industry-accepted standards. With the introduction in WebSphere Commerce of hosted business models that handle extremely high volumes of traffic, it becomes especially important to archive sales and user-traffic data.

This article briefly describes the data back end of a WebSphere Commerce application, highlights a typical solution to archive useful information from this data, and displays that information using WebSphere Commerce.

Environment Details
A typical environment for such an application is a three-tier setup that contains an HTTP server at the front end to channel incoming requests, a middle tier powered by WebSphere Commerce, and a relational database management system, which hosts the back-end data and maintains persistence. Our solution uses IBM DB2 Universal Database.

IBM WebSphere Commerce delivers a market-leading B2B commerce solution to optimize customer and channel interactions, generate increased revenue, reduce costs and cycle times, and improve customer loyalty.

DB2 Universal Database is IBM's relational database management system for AIX, Linux, HP-UX, Sun, and Windows. DB2 database software marks the next stage in the evolution of the relational database: it is the industry's first multimedia, Web-ready relational database management system delivering leading capabilities in reliability, performance, and scalability.

IBM HTTP Server can be the foundation of any e-business application. IBM e-business software, such as the WebSphere family of products, is designed to operate with many popular Web servers.

The sample scripts were tested on WebSphere Commerce on the 2000 platform with DB2.

Overview
High-volume Web sites powered by WebSphere Commerce extract incoming user requests and store this data in a relational back end. This data is periodically cleaned with the help of scripts such as dbclean, which are supplied with the product. Although needed to optimize performance, these cleanups may cause loss of data needed for future business intelligence. We advise running simple scripts on the database before running the dbclean utility to archive historical usage records. This is valuable information that can help sales and marketing departments evaluate trends and determine business direction.

This article describes a method for extracting and archiving such data and using it to generate a WebSphere Commerce report. It provides sample SQL scripts to create and populate the archive tables and a simple report to view these archives. Though the example in this article is specific to the hosted store business models available with the latest version of WebSphere Commerce, you can easily modify it to suit other scenarios.

Process - User Traffic Logging in DB2
Enabling User Traffic Logging in a hosted WebSphere Commerce application populates the USRTRAFFIC table in the back-end database by recording user activity at a site level. For more information about logging, see the online help for WebSphere Commerce at www.ibm.com/support/docview.wss?uid=swg27001853.

For a typical site, the USRTRAFFIC table grows fairly rapidly. It needs to be cleaned periodically to maintain site performance. The dbclean utility provided with WebSphere Commerce removes all entries from the USRTRAFFIC table by default.

Solution - Data Extraction Script
Before running the dbclean script on the database, use the SQL script included in the source code (available for download from www.sys-con.com/websphere/sourcec.cfm) to easily extract the information to be archived. To have a complete and accurate set of historic user-traffic and sales data, run this script at the beginning of each month, and then run dbclean to clean up the database and optimize its performance. The script extracts user-traffic and sales data for the previous month. It is a simple example that you can easily customize for specific business scenarios.

This script first defines a table in the database called ARCHIVE_WCS. This table contains rows and columns extracted from the database schema used to store user data.

Once you have run the scripts the data will be defined as shown in Figure 1. Note: You can make changes to the script provided to modify this table according to your requirements.

Next, this script runs some predefined SQL data-extraction queries. Listing 1 creates the archive table, and Listing 2 populates the archive table. The following SQL command reorganizes the ARCHIVE_WCS table to optimize performance:

reorg table ARCHIVE_WCS inplace allow read access notruncate table resume

The following SQL command extracts records from the archive table:

select * from ARCHIVE_WCS;

This SQL command drops the archive table:

drop table ARCHIVE_WCS;

Using the Sample Report
Now that the user data has been extracted to the newly created table, we will walk you through the creation of a sample report program you can write to graphically view this archived data. The sample report program is available for download as part of the source file. To enable it on your system, take the following steps.

Note: In the following section, <INSTALL_ DIR> refers to the directory in which WebSphere has been installed; <instance_name> refers to the name of your Commerce instance.

1.  Place the report pages in the appropriate directories. XML files should be placed in <INSTALL_ DIR>\CommerceServer55\xml\tools\reporting. JSP files belong in the <install_dir>\AppServer\installedApps\studio\ <instance_name>.ear\CommerceAccelerator.war\tools\reporting directory.

2.  Register the report xml resources in the file resources.xml. Navigate to the <INSTALL_DIR>\CommerceServer55\xml\tools\reporting\ directory and modify resources.xml by pasting in the following lines:

<resourceXML name="UserTrafficPatternsReport"
file="reporting/UserTrafficPatternsReport.xml" />
<resourceXML name="UserTrafficPatternsReportDialog"
file="reporting/UserTrafficPatternsReportDialog.xml" />
<resourceXML name="UserTrafficPatternsReportOutputDialog"
file="reporting/UserTrafficPatternsReportOutputDialog.xml" />

3.  Add the text strings used in your report to the appropriate .properties file. Modify Reports_en_US.properties in the <INSTALL_DIR>\AppServer\installedApps\studio\ <instance_name>.ear\properties\com\ibm\commerce\tools\reporting\properties directory by pasting in the lines shown in Listing 3.

4.  Register the report "view" com-mands to the database by running the SQL code shown in Listing 4.

5.  Add access control. For each of the views registered into the back-end database in the previous step, insert one row into the ACACTION table. Check the current maximum acaction_id value by running the following SQL command in a DB2 command window:

db2 select max(acaction_id) from ACACTION >

Our result was 11928. Now verify the current key table value for the table ACACTION by running the following SQL command:

db2 select counter from keys where tablename='acaction' >

Our result was 11950. If you add enough rows to exceed the result obtained from the last step, you must update the keys table value for the table. For example, in our instance, if we had inserted enough rows into the ACACTION table to exceed the value of the counter in the table keys where tablename = acaction, we would have run the following SQL to update the keys table:

db2 select max(acaction_id) from ACACTION

Our result was 11950. Increment the result obtained from the previous command to maintain uniqueness in the keys table. We changed our value to 11965.

db2 update keys set counter = 11965 where tablename = 'acaction'

Run the SQL code shown in Listing 5 from a DB2 command window to add access control.

6.  Add your report to the accelerator menu to access it:

Edit <INSTALL_DIR>\CommerceServer55\xml\tools\reporting\ OperationalReportsContext.xml.

Add the text shown in Listing 6 to the appropriate context tag, then add the text for the tag above to <INSTALL_DIR>\CommerceServer55\properties\runtime\com\ibm\ commerce\tools\reporting\properties\OperationalReportsNLS_en_US.properties (see Listing 7).

7.  Restart the WebSphere Commerce instance.

8.  View the report (see Figure 2).

Summary
This article provided detailed instructions on how to consolidate and archive user traffic and sales data. Storing data in this form is more efficient and can improve the performance of your production site. This archived data was displayed in the GUI using the reporting framework provided with WebSphere Commerce. You can easily modify and extend this simple example to make it more robust.