Tutorial: Migrating members super class references
Overview
The member subsystem is a component of the WCS 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). Before beginning download the zip file containing all the code related to this article.
Extract 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> <!--Begin functionDef elements for assetGroup "users"--> <functionDef 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 /> </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 can prepare extracted data for tables such as MEMBER, which is used as a "super class" for the ORGENTITY, MBRGRP, and USER tables, creating an "is-a" pattern useful for maintaining referential integrity when tables have foreign-key constraints to the subtypes of the MEMBER table.
MEMBER subtypes share a common base type. The identifier must be unique among the subtypes. 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.
idresgen syntax
<ORGENTITY ORGENTITY_ID="@orgAlias" ORGENTITYNAME="Test Org" ORGENTITYTYPE="O"> <ISA><MEMBER TYPE="O" STATE="1" /></ISA> </ORGENTITY>
idresgen output
<MEMBER MEMBER_ID="12345" TYPE="O" STATE="1" /> <ORGENTITY ORGENTITY_ID="12345" Synchronized with member element ORGENTITYNAME="Test Org" ORGENTITYTYPE="O" />
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.
The XSL style sheet example below shows 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.
Load 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
The sqlimport method does not put the tables into check-pending state in case of load errors, but it has the slowest performance of all methods.
The commitcount was set to 20. To improve performance, you can increase this value to 1000 or greater. There is an option to limit the number of errors to ignore.
Implement member migration
- 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
Procedure
- 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 the WCS 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
vi 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:
vi 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