(Tutorial) Tutorial: Migrating members super class references

Tutorial: Migrating members super class references

Introduction

The member subsystem is a component of the WebSphere Commerce Server which includes data for participants of the WebSphere Commerce system. A member can be a user, a group of users (also known as a member group), or an organizational entity (which can be an organization, such as IBM or an organizational unit within an organization, such as the Electronic Commerce Division).

Extracting data from the database

The following is an example shows the command syntax used to run a data extraction.
 massextract -dbname %DBNAME% -dbuser %DBUSER% -dbpwd %DBPWD% 
 -filter extract.xml -outfile extractedData.xml
When you run this command, the output will be sent to two or three files. The extractedData.xml file holds the raw data output in XML format. Each row in a table is represented as attributes of an element referenced by its associated table name. Another output file is called trace.txt. This file holds the tracing of the executed statements performed during the extraction process. A third file, messages.txt, is generated only if there are errors during the extraction process.

The file that is used as the input for the filter, extract.xml, contains the following lines:
 <?xml version="1.0" encoding="UTF-8"?>
 <sqlx>
 
 <functionDef id="Users" description="Users" schemaentity="users">
   <body>
             select distinct users.* from users, userreg
             where users.users_id > 0
      AND users.users_id = userreg.users_id
             AND userreg.status = 1
          </body>
 </functionDef>
 <execute id="Users"/>
 </sqlx>
The attributes of the functionDef element are used to describe the SQL statement within the child body element. The attribute named id is used as a reference from the execute element. The attribute named schemaentity is used to cross-reference the DTD file that contains the schema information. This value would be a table that exists in the WebSphere Commerce database. The child body element of the functionDef element consists of an SQL statement. This SQL statement can be designed as a static SQL statement that accepts parameters. For this example above, a static SQL statement is used without parameters. Some characters are not permissable in this SQL statement such as the greater then and less than signs. These characters must be replaced with Unicode codes as referenced in the encoding section of the same document (for example, encoding="UTF-8"). In order to avoid ambiguities, > is used as an escape for the > character in this SQL statement.

Transforming member assets

The process of transforming data assets prepares the extracted output (extractedData.xml) for the identification resolving step. In order to make this preparation successful, the extracted data has to be translated into a format that can be understood by any database instance of WebSphere Commerce. The following example shows the command syntax that accomplishes this:
 xmltransform -outfile transformedOutput.xml -infile extractedData.xml 
 -transform %IDENTTRANSFORM% -param "dtdfile=%DTDFILE%" 
 -param "rootelement=import" -param "member_id=%MEMBERID%"
As shown in the example above, the IDENTTRANFORM parameter passed from env.bat is the value of the actual transformation XSL file. This file contains the logic to transform the extracted data. The -param key-value pairs will be used as parameter values that are used to hard-code references within the transformed file.

The same extracted output file may have to go through different types of transformation depending on the requirements imposed by the target data state. idresgen utility handles resolution for tables that have identifiers generated for them by the system. The transform process must prepare table elements in the XML files by imposing a special reference in primary and foreign keys. Also, parameters can be passed into the transform command that can be used as special values while transforming the extracted XML file.

Super Class References

Transformation may have to occur in order to prepare extracted data for special table relationships in the database such as a table represented as a 'super class'. For example, the MEMBER table is used as a "super class" for the ORGENTITY, MBRGRP, and USER tables. This creates an "is-a" pattern that is useful for maintaining referential integrity when tables have foreign-key constraints to the subtypes of the MEMBER table. All MEMBER subtypes share a common base type; however, the identifier must be unique among the subtypes. This means that an ORGENTITY_ID must be unique in the MBRGRP_ID and USER_ID set. To accomplish this, the KEYS table refers only to the ORGENTITY, MBRGRP, and USER tables and specifies mutually exclusive ranges for their identifiers. Each of the subtypes has a primary key; each of these primary keys is also a foreign key to the MEMBER table primary key. The constraints between MEMBER and its subtypes create a situation where a MEMBER and subtype cannot have a synchronized ID. In order to load the ORGENTITY, MBRGRP, and USER tables into the system using the loading utilities, the idresgen utility recognizes the "is-a" pattern and deals with it appropriately.

The following example shows the XML syntax for the idresgen utility:
  
   <ORGENTITY
      ORGENTITY_ID="@orgAlias"
      ORGENTITYNAME="Test Org"
      ORGENTITYTYPE="O">
      <ISA><MEMBER TYPE="O" STATE="1" /></ISA>
  </ORGENTITY>
idresgen utility generates the following output:
 
  <MEMBER 
      MEMBER_ID="12345" 
      TYPE="O"
      STATE="1"
  />
  <ORGENTITY
      ORGENTITY_ID="12345"           Synchronized with member element
      ORGENTITYNAME="Test Org"
      ORGENTITYTYPE="O"
  />
In this way, the idresgen utility handles the <isa> subelement and creates a synchronized identifier. The transformation process must be able to accommodate for this reference requirement. The transformation must be run as a second step to handle alternative foreign references, but the Super class references must be run as a second transformation. For example, the XSL style sheet example shown below illustrates how to transform extracted user data into a format that can properly reference the member table:
    <xsl:template match="users">
        <xsl:copy>
            <xsl:for-each select="@*">
                <xsl:copy-of select="." />
            </xsl:for-each>
         <xsl:element name="ISA">
           <xsl:element name="member">
        <xsl:variable name="searchReplace">
          <xsl:call-template name="search-replace">
            <xsl:with-param name="text" select="@users_id"/>
            <xsl:with-param name="replace" select="'users'"/>
            <xsl:with-param name="with" select="'member'"/>
          </xsl:call-template>
        </xsl:variable>
             <xsl:attribute name="member_id"><xsl:value-of select="$searchReplace"/></xsl:attribute>
             <xsl:attribute name="type"><xsl:value-of select="$tmpusertype" /></xsl:attribute>
             <xsl:attribute name="state"><xsl:value-of select="$tmpstate" /></xsl:attribute>
           </xsl:element>
         </xsl:element>
        </xsl:copy>
    </xsl:template> 
As shown above, the elements ISA and member are appended as children under the user's element. Also, the member_id reference has been replaced with the value of the users_id. This handles the requirement discussed earlier concerning the handling of foreign key referencing from child tables. Child tables such as mbrrole refer to the member_id instead of the user's primary key. Therefore, the user's table would need to produce an equivalent reference for the member foreign key during the idresgen utility step. This must be prepared in the transformation process.

Loading Data Assets on Target

After the idresgen utility step has succeeded, the resolved output files must be moved to the target machine (unless the local database has been cataloged to a remote database and the database name is specified in the env.bat file). Once the resolved output files are ready for importing on the target database, the massimport command can be executed. The following example shows the command syntax:
 massload -dbname %DBNAME% -dbuser %DBUSER% -dbpwd %DBPWD% 
 -infile resolvedOutput.xml -method sqlimport -commitcount 20
For this example, the method chosen was sqlimport. This method does not put the tables into check-pending state in case of load errors. On the other hand, it has the slowest performance. The commitcount was set to 20, but to improve performance, you can increase this value to 1000 or greater, depending on the number of items in the file. There is also an option that can limit the number of errors to ignore, but it was not used in this example.

This sample demonstrates how to extract and then load user data into a WebSphere Commerce database

The following example shows the XML syntax for the idresgen utility:

The following steps can be performed to implement the member migration reviewed above:

Run massextract on the extract.xml filter
Run transform using out-of-the-box transform XSL Style Sheet
Run transform using membertransform.xsl XSL Style Sheet
Run idresgen utility on the transformed XML file
Run Massload on the resolved XML file

Steps
Extract the MemberMigration.zip file into a temporary directory
Change directory into the extracted zip directory and locate the env.bat file
Update the env.bat file to match your WebSphere Commerce Server environment

Set DBNAME to represent the database user
Set DBUSER to represent the database user
Set DBPWD to represent the database password
Set RESGENFILE to represent the output resolved file
Set WCSHOME to represent the path of the WebSphee Commerce Server
Set PATH use to update the PATH to add the WCSHOME parameter
Ensure that extractActive.xml contains correct SQL for filtering data:
 cd users
 notepad extractActive.xml
From the source tower Commerce instance, extract active Users data:
 Step-extractActive.bat
Ensure that extractInActive.xml contains correct SQL for filtering data:
 notepad extractInActive.xml
Inactive Users (only required if you are migrating inactive users:
From the source tower Commerce instance, extract inactive Users data:
 Step-extractInActive.bat
From either the target tower or the source tower Commerce instance, transform Active Users data:
 Step2-transformActive.bat
From either target tower or source tower Commerce instance, transform inactive Users data:
 Step2b-transformInActive.bat
Merge both transformed files ActiveTransform.xml and InActiveTransform.xml into a new file called usersCombineTransform.xml, and then transform combined Users data:
 Step2c-transformMember.bat
From the target tower Commerce instance, resolve identifiers:
 Step3-idresgen.bat
From target tower Commerce instance, massload data:
 Step4-massload.bat
Resources

Download the zip file containing all the code related to this article.

(C) Copyright IBM Corporation 1996, 2006. All Rights Reserved.