Example: Loading store data

This section provides examples of how to load store data into your WebSphere Commerce Server database using the Loader package command-line utilities.

Notes:

  1. The examples in this section are performed in a Windows environment.
  2. The commands and options for DB2 are used in the following examples. If you are using a database other than DB2, make sure that you modify your customizer properties files.

Refer to Loading WebSphere Commerce database asset groups and Loading database asset groups for information on the loading process for WebSphere Commerce database asset groups.

Using the Loader package utilities

To run the Loader package utilities, use the scripts provided in the WC_installdir/bin directory within WebSphere Commerce.

Examples of resolving identifiers

The examples of identifier resolution described in this section use the store-asset files from the Business Edition onlyAdvanced B2B direct starter store.

Because this example is based on loading new data into the WebSphere Commerce Server database, we will use the load method.

If you later need to modify certain elements within the XML document, you can do so using the update method. The update method should run faster than the load method because no new identifiers are allocated with the update method. With the update method, a database query is performed to locate the identifier and an error is reported if the identifier is not found.

If your input XML file contains elements that already exist in the database as well as elements that do not, use the mixed method. With the mixed method, a database lookup is done first and an identifier is assigned to the element if the record is not found. When in doubt, use the mixed method. Although the load and update methods provide faster performance than the mixed method, the resolved XML file produced by using the mixed method has a greater likelihood of loading without errors.

Resolving identifiers in XML files with internal aliases

To resolve identifiers using internal aliases before loading the data into your WebSphere Commerce Server database, run the ID Resolver utility as shown in the following example.

Note: This example assumes that WebSphere Commerce is installed on a Windows machine. If you have WebSphere Commerce installed on a different operating system, please substitute the appropriate values for your operating system.

  1. Create a working directory.

    For this example, create the WC_installdir/test directory.

    Note: If you do not use WC_installdir/test as your working directory, substitute the name and path of the working directory that you do use for WC_installdir/test in the examples shown in the remainder of this topic.

  2. Make sure that your input XML file as well as any referenced DTD files are in a location where the ID Resolver can find them.

    For this example:

    1. From the Windows command prompt, enter the following utility:
      copy WC_installdir\starterstores\
      BusinessDirect\BusinessDirect.sar WC_installdir\test
      
      This copies the BusinessDirect.sar file into WC_installdir\test.
    2. From a Windows command prompt, enter the following utility:
      cd to WC_installdir\test
      
    3. Do one of the following:
      • If you have Java installed, enter the following utility from the Windows command prompt:
        jar -xvf BusinessDirect.sar
        
        This extracts the B2B direct starter store XML files into WC_installdir\test.
      • Use any up-to-date unzipper product (such as WinZip or PKZIP) to extract the entire contents of WC_installdir\starterstores\BusinessDirect\BusinessDirect.sar into WC_installdir\test.
      This extracts the Advanced B2B direct starter store XML files into WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data.
    4. From the Windows command prompt, enter the following command:
      copy WC_installdir\xml\sar\store.dtd
      WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data
      
      This copies the store.dtd file into WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data.
    5. From the Windows command prompt, enter the following command:
      copy WC_installdir\xml\sar\DBLoadMacros.dtd
      WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data
      
      This copies the DBLoadMacros.dtd file into WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data.
    6. From the Windows command prompt, enter the following command:
      copy WC_installdir\xml\sar\fulfillment.dtd
      WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data
      
      This copies the fulfillment.dtd file into WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data.
  3. Make sure that the WebSphere Commerce schema is loaded into your database along with the necessary bootstrap data by creating an appropriate WebSphere Commerce Server database instance.

    Note: For information on creating an instance, refer to the WebSphere Commerce Installation Guide for your operating system.

    The WebSphere Commerce Server database instance that this example uses is called mall. Primary and foreign keys will be obtained from the KEYS and SUBKEYS tables of this database; therefore, the ID Resolver will be unable to resolve the identifiers if the database is not loaded properly.

  4. To resolve identifiers for the fulfillment.xml file:
    1. Edit the fulfillment.xmlfile to include the following:
      <?xml version="1.0" encoding="UTF--8"?>
      <!DOCTYPE  fulfillment--asset SYSTEM "fulfillment.dtd">
      <fulfillment--asset>
      </fulfillment--asset>
      
      The fulfillment.xml should look like the following:
      <?xml version="1.0" encoding="UTF--8"?>
      <!DOCTYPE  fulfillment--asset SYSTEM "fulfillment.dtd">
      <fulfillment--asset>
      <!--defaultshipoffset can be overridden in the STORITMFFC table.-->
      <!--Now in ToolTech STORITMFFC.shippingoffset is set to 86400 
        seconds which is one day--<
      <ffmcenter
         ffmcenter_id="@ffmcenter_id_1"
         member_id="&MEMBER_ID;"
          name="ToolTech Home"
          defaultshipoffset="0"
          markfordelete="0"
       />
      </fulfillment--asset>
      
    2. Edit the DBLoadMacros.dtd file to include the following, if it is missing:
      <!ENTITY MEMBER_ID "-2001">
      
    3. Enter the following command to run the ID Resolver against the fulfillment.xml file (where the FFMCENTER table is defined):
      idresgen -dbname mall -dbuser db2admin -dbpwd db2admin
      -infile WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data\
      fulfillment.xml
      -outfile WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data
      \fulfillment1.xml
      -method load
      
      where
      • mall should be changed to the name of the target database if you are not using mall
      • The first db2admin should be changed to the name of the user connecting to the database if you are not using db2admin
      • The second db2admin should be changed to the password of the user connecting to the database if you are not using db2admin

      The resolved element in the fulfillment1.xml output file looks like this:

      <fulfillment-asset>
      <ffmcenter
         FFMCENTER_ID="10001"
         MEMBER_ID="-2001"
         NAME="ToolTech Home"
         DEFAULTSHIPOFFSET="0"
         MARKFORDELETE="0"
      />
      </fulfillment-asset>
      

      Notes:

      1. This is an example. Your output file may contain different values.
      2. Before proceeding with the next step make a copy of store.xml.
  5. To resolve identifiers for the store.xml file, do the following:
    1. Edit the store.xmlfile to include the following:
      <?xml version="1.0" encoding="UTF--8"?>
      <!DOCTYPE store--asset SYSTEM "store.dtd">
      <store--asset>
      </store--asset>
      
    2. Get the FFMCENTER_ID key from the resulting output file (fulfillment1.xml) and substitute that key for all occurrences of @ffmcenter_id_1 in your working copy of store.xml in WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data
    3. Edit the DBLoadMacros.dtd file to include the following, if it is missing:
      <!ENTITY MEMBER_ID "-2001">
      <!ENTITY STORE_IDENTIFIER "ToolTech">
      <!ENTITY STORE_DIR "ToolTech">
      
    4. Enter the following command:
      idresgen -dbname mall -dbuser db2admin -dbpwd db2admin
      -infile WC_installdir\test\WEB-INF\stores\
      BusinessDirect\data\BusinessDirect\data\store.xml-outfile 
      WC_installdir\test\WEB-INF\stores\
      BusinessDirect\data\BusinessDirect\data\store1.xml -method load
      
      where
      • mall should be changed to the name of the target database if you are not using mall
      • the first db2admin should be changed to the name of the user connecting to the database if you are not using db2admin
      • the second db2admin should be changed to the password of the user connecting to the database if you are not using db2admin

      The fully resolved elements in the store1.xml output file look like this:

      <store-asset>
      <storeent
          STOREENT_ID="10151"
          MEMBER_ID="-2001"
          TYPE="S"
          IDENTIFIER="ToolTech"
          SETCCURR="USD"
       />
      <store
          STORE_ID="10151"
          DIRECTORY="ToolTech"
          FFMCENTER_ID="10001"
          LANGUAGE_ID="-1"
          STOREGRP_ID="-1"
          ALLOCATIONGOODFOR="43200"
          BOPMPADFACTOR="0"
          DEFAULTBOOFFSET="2592000"
          FFMCSELECTIONFLAGS="0"
          MAXBOOFFSET="7776000"
          REJECTEDORDEXPIRY="259200"
          RTNFFMCTR_ID="10001"
          PRICEREFFLAGS="0"
          STORETYPE="B2B"
       />
      <vendor
          VENDOR_ID="10001"
          STOREENT_ID="10151"
          VENDORNAME="Tooltech Vendor"
          MARKFORDELETE="0"
       />
      <dispentrel
          AUCTIONSTATE="0"
          CATENTRY_ID="0"
          CATENTTYPE_ID="ProductBean"
          DEVICEFMT_ID="-1"
          DISPENTREL_ID="10001"
          MBRGRP_ID="0"
          PAGENAME="CatalogProductDisplay.jsp"
          STOREENT_ID="10151"
          RANK="0"
       />
      <dispentrel
         AUCTIONSTATE="0"
          CATENTRY_ID="0"
          CATENTTYPE_ID="ItemBean"
          DEVICEFMT_ID="-1"
          DISPENTREL_ID="10002"
          MBRGRP_ID="0"
          PAGENAME="CatalogItemDisplay.jsp"
          STOREENT_ID="10151"
          RANK="0"
       />
      <dispcgprel
          CATGROUP_ID="0"
          DEVICEFMT_ID="-1"
          DISPCGPREL_ID="10001"
          MBRGRP_ID="0"
          PAGENAME="CatalogCategories.jsp"
          STOREENT_ID="10151"
          RANK="0"
       />
      <invadjcode
          ADJUSTCODE="PCNT"
          INVADJCODE_ID="10001"
          MARKFORDELETE="0"
          STOREENT_ID="10151"
       />
      <invadjcode
          ADJUSTCODE="SPLG"
          INVADJCODE_ID="10002"
          MARKFORDELETE="0"
          STOREENT_ID="10151"
       />
      <invadjcode
          ADJUSTCODE="DISC"
          INVADJCODE_ID="10003"
          MARKFORDELETE="0"
          STOREENT_ID="10151"
       />
      <rtnreason
          REASONTYPE="C"
          RTNREASON_ID="10001"
          STOREENT_ID="10151"
          MARKFORDELETE="0"
          CODE="WPR"
       />
      <rtnreason
          REASONTYPE="B"
          RTNREASON_ID="10002"
          STOREENT_ID="10151"
          MARKFORDELETE="0"
          CODE="DEF"
       />
      <rtnreason
          REASONTYPE="M"
          RTNREASON_ID="10003"
          STOREENT_ID="10151"
          MARKFORDELETE="0"
          CODE="ERR"
       />
      <rtnreason
          REASONTYPE="M"
          RTNREASON_ID="10004"
          STOREENT_ID="10151"
          MARKFORDELETE="0"
          CODE="WPS"
       />
      </store-asset>
      

      Note: This is an example. Your output file may contain different values.

  6. In the store.xml file, you will find the following element:
    <storeent
       STOREENT_ID="@storeent_id_1"
       MEMBER_ID="@seller_b2b_mbr_id"
       TYPE="S"
       IDENTIFIER="&STORE_IDENTIFIER"
       SETCURR="USD"
    />
    

    This element of store.xml maps to the storeeent table in the database; and its STOREENT_ID MEMBER_ID, TYPE, IDENTIFIER, and SETCURR attributes map to columns in that table. The @storeent_id_1 specification is an internal alias for the value of the STOREENT_ID attribute; and &MEMBER_ID; is an entity parameter. The value of the entity &MEMBER_ID; has to be substituted before it can be loaded using the Loader. The value of &MEMBER_ID; is defined in the DBLoadMacros.dtd macro file; and the value is substituted from that file. When the ID Resolver encounters @storeent_id_1, it looks in its cache of primary tables to see if storeent is present. Because it is a primary table, storeeent is present. The ID Resolver fetches the counter for that table, increments it, and replaces the internal alias with the result. All other such entries in the store.xml file are processed in the same way.

  7. Make sure that your path includes the directory containing the appropriate ID Resolver utility or script as listed in Using the Loader package commands and scripts.

    For this example, enter the following command from a Windows command prompt:

    cd WC_installdir\bin
    

    where WC_installdir\bin should be changed to the name of the directory containing the ID Resolver utility, idresgen.cmd, if it is not located in WC_installdir\bin on your system.

  8. From the Windows command prompt, enter the following command:
    idresgen -dbname mall -dbuser wcs -dbpwd wcs1 -infile
    WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\
    data\store.xml -outfile
    WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\
    data\store1.xml-method load
    
    where
    • mall should be changed to the name of the target database if you are not using mall
    • the first db2admin should be changed to the name of the user connecting to the database if you are not using db2admin
    • the second db2admin should be changed to the password of the user connecting to the database if you are not using db2admin
    The first output XML fragment in store1.xml looks like this:
    <storeent
       STOREENT_ID="10001"
       MEMBER_ID="-2001"
       TYPE="S"
       IDENTIFIER="ToolTech"
       SETCCURR="USD"
    />
    

    Note: This is an example. Your output file may contain different values.

    The second XML fragment in store1.xml looks like this:

    <store
       STORE_ID="10001"
       DIRECTORY="ToolTech"
       FFMCENTER_ID=""
       LANGUAGE_ID="-1"
       STOREGRP_ID="-1"
       ALLOCATIONGOODFOR="43200"
       BOPMPADFACTOR="0"
       DEFAULTBOOFFSET="2592000"
       FFMCSELECTIONFLAGS="0"
       MAXBOOFFSET="7776000"
       REJECTEDORDEXPIRY="259200"
       RTNFFMCTR_ID=""
       PRICEREFFLAGS="0"
       STORETYPE="B2B"
    />
    

    Note This is an example. Your output file may contain different values.

    You can resolve identifiers using one of the following options:

    • Option 1:
      1. Merge the fulfillment.xml and store.xml files by adding any content that is unique in fulfillment.xml (including the reference to fulfillment.dtd) to store.xml, making sure that the ffmcenter element shown below precedes the store element.
        <ffmcenter
           FFMCENTER_ID="@ffmcenter_id_1"
           MEMBER_ID="&MEMBER_ID;"
           NAME="ToolTech Home"
           DEFAULTBOOFFSET="0"
           MARKFORDELETE="0"
        />
        
      2. Run the ID Resolver against the merged file.
    • Option 2: Load the store-assets data group using the process described in Loading database asset groups.

Specifying a properties file with the ID Resolver

You can modify the way in which the ID Resolver resolves identifiers by using the -propfile parameter. The default properties file is IdResolveKeys.properties. To modify and use IdResolveKeys.properties, copy this file into a user defined directory, make the required changes and then specify this new file when invoking the ID Resolver utility. The default IdResolveKeys.properties file is located in the WC_installdir/properties directory:

The property-file specification takes precedence over the use of internal aliases.

Here is a sample XML fragment from the store.xml file:

<store
   STORE_ID="@storeent_id_1"
   DIRECTORY="ToolTech"
   FFMCENTER_ID="@ffmcenter_id_1"
   LANGUAGE_ID="&en_US;"
   STOREGRP_ID="-1"
   ALLOCATIONGOODFOR="43200"
   BOPMPADFACTORr="0"
   DEFAULTBOOFFSET="2592000"
   FFMCSELECTIONFLAGS="0"
   MAXBOOFFSET="7776000"
   REJECTEDORDEXPIRY="259200"
   RTNFFMCTR_ID="@ffmcenter_id_1"
   PRICEREFFLAGS="0"
   STORETYPE="B2B"
/>

If you run the ID Resolver with the -propfile specified as WC_installdir\test\WEB-INF\stores\ BusinessDirect\data\BusinessDirect\data\myPropFile and the specified file, myPropFile.properties, contains the following entries:

NAMEDELIMETER=@
SELECTDELIMETER=:
FFMCENTER=@FFMCENTER_ID@MEMBER_ID:10051 -2001

the ID Resolver queries the database for the FFMCENTER table with a where clause of 10051 and -2001 when the store element is processed. The index that is returned for this value is then used to resolve the identifier for FFMCENTER_ID.

For more information on using this command, refer to ID Resolver utility.

Example of loading data

When you have resolved the identifiers in the XML file if necessary, you are ready to load the data into the WebSphere Commerce Server database.

Note: If you have properly resolved the identifiers in your XML data, your source XML file should not contain any of the following:

The presence of any of these is an indication that your XML file is not ready to be loaded.

The example of loading data described in this section uses the fulfillment1.xml file that was resolved in Examples of resolving identifiers.

To load data into your WebSphere Commerce Server database, run the Load command as shown in the following example:

  1. Create a working directory.

    For this example, use the directory called WC_installdir\test\WEB-INF\stores\ BusinessDirect\data\BusinessDirect\data\ that you created in Examples of resolving identifiers.

  2. Make sure that your input XML file is in a location where the Loader can find it.

    For this example, make sure that the fulfillment1.xml output file that you created in Examples of resolving identifiers is in WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect\data\.

  3. Make sure that you back up your WebSphere Commerce Server database so that you can restore the database from the backup if an unrecoverable error occurs.

    Note: See the backup and recovery documentation provided with your database product for information on backing up your database.

  4. Make sure that your path includes the directory containing the appropriate Load command or script as listed in Using the Loader package commands and scripts.

    For this example, enter the following command from a Windows command prompt:

    cd WC_installdir\bin
    

    where WC_installdir\bin should be changed to the name of the directory containing the Load command massload.cmd if it is not located in WC_installdir\bin on your system.

  5. Run the Load command against your resolved XML file to load your data into the target database.

    For this example, enter the following command from a Windows command prompt:

    massload -dbname mall -dbuser db2admin -dbpwd db2admin -infile
    WC_installdir\test\WEB-INF\stores\BusinessDirect\data\BusinessDirect
    \data\fulfillment1.xml -method sqlimport -commitcount 50
    

    where

    • mall should be changed to the name of the target database if you are not using mall
    • the first db2admin should be changed to the name of the user connecting to the database if you are not using db2admin
    • the second db2admin should be changed to the password of the user connecting to the database if you are not using db2admin
  6. To verify if the Load command is executed successfully, check if a messages.txt file is present in WC_installdir/logs. The presence of this file indicates an error. For more information about the error, refer to the messages.txt file.

Even though there are less than 50 elements to be loaded, this example specifies a value of 50 for -commitcount. This is for performance reasons. By default, the commit count is 1. Using this default causes a commit operation for each record written to the database. Setting the number to 50 in the preceding example ensures that database I/O occurs only once if loading is successful and that nothing is written to the database if errors occur. If you have a large amount of data to load, however, it is recommended that you do not set the commit-count value as large as the number of elements for the following reasons:

The default for the -noprimary option is error so that the tool reports errors and terminates when primary keys are missing.

Because these examples do not load the store assets in the order used by the Administration Console and described in Database asset loading sequence, the store1.xml file created in Examples of resolving identifiers may violate the integrity constraints of some tables. If you tried to load store1.xml without modification using the load method, a constraint violation would cause the database to enter pending state. For simplicity, therefore, this example of using the Load command is based on the resolved version of the fulfillment.xml file, whose only foreign key is that of the MEMBER_ID defined in the starter store. This example loads the resolved fulfillment1.xml file that was output in Examples of resolving identifiers and uses the SQL import method. When you are not sure that the contents of your XML file are clean, use the SQL import method as shown in this example with the -commitcount and -maxerror parameters set appropriately so that any database constraint violations are reported without altering the database and jeopardizing database integrity.

When you run this command, a trace text file (trace.txt) is created in the execution subdirectory (WC_installdir\bin in the preceding example) by default. For i5/OSiSeries the trace.txt is created in QIBM/Userdata/CommerceServer56/instances/instance_name/logs. by default.

If your WCALoggerConfig.xml logging configuration file has been altered to place trace.txt in a different location, go to that location to inspect the file. For more information on customizing WCALoggerConfig.xml, refer to Configuring logging for the Loader package

The trace.txt file contains a listing of the actions performed by the command and their results. If you use the SQL import method with the command as shown in the preceding example, the end of trace.txt will contain an entry indicating the number of records committed.

For more information on using this command, refer to Load command.

Feedback