Upgrading Database Schemas from Prior Releases

 

If you are upgrading your WebLogic Commerce Server and WebLogic Personalization Server installation from a prior release, you must also upgrade your database to the corresponding schema. This chapter describes the following tasks:

• Upgrading WebLogic Personalization Server Database Schemas from 2.0.1 to 3.1.1

• Upgrading WebLogic Personalization Server Database Schemas from 2.0.1 to 3.2

• Upgrading Database Schemas from 3.1.1 to 3.2

• Upgrading Database Schemas from 3.2 to 3.5

You must upgrade databases in the sequence of the preceding list; you cannot skip a Release in the migration path.

Note: For information on upgrading a WebLogic Commerce Server 2.0.1 database to the WebLogic Commerce Server 3.1.1 or 3.2 database schemas, contact BEA Customer Support.

 

---

Upgrading WebLogic Personalization Server Database Schemas from 2.0.1 to 3.1.1

In WebLogic Personalization Server Release 3.1, a new column called PROFILE_TYPE was added to the WLCS_USER table. This column contains the name of the Unified Profile Type of which the User is an instance. (For more information about Unified Profile Types, see the chapter "Creating and Managing Users"in the Guide to Building Personalized Applications (in the release 3.5 documentation set.))

The PROFILE_TYPE column can be added to existing WLCS_USER tables with the following statement:

ALTER TABLE WLCS_USER ADD PROFILE_TYPE VARCHAR2(100);

For User objects that are of the standard type com.beasys.commerce.axiom.contact.User, this should be left as null. If the User is an extended User type, such as the 'Unified Profile Example', the column should be set to that type name. The example user for the Unified Profile Example should be updated with the following statement:

UPDATE WLCS_USER SET PROFILE_TYPE = 'Unified Profile Example' WHERE IDENTIFIER = 'unifieduser_bob';

Note: In this document, $WL_COMMERCE_HOME refers to the directory into which you installed WebLogic Commerce Server and/or WebLogic Personalization Server and database-type refers to the type and version of RDBMS that you installed.

To upgrade a WebLogic Personalization Server database from release 2.0.1 to release 3.1:

1. Make a backup copy of the following file: $WL_COMMERCE_HOME/db/database-type/staging /upgrade-to-310.sql

2. Open upgrade-to-310.sql in a text editor.

3. Move the cursor immediately below the following statement:

   ALTER TABLE WLCS_USER ADD (
   PROFILE_TYPE VARCHAR2(100)
   );

4. For each user that is of an extended User type, add the following statement on a single line:

   UPDATE WLCS_USER SET PROFILE_TYPE = '<profile-type>' WHERE IDENTIFIER = '<user-name>';

   For example:

   UPDATE WLCS_USER SET PROFILE_TYPE = 'Unified Profile Example' WHERE IDENTIFIER = 'unifieduser_bob';

5. Save your modifications and close update-to-310.sql.

6. Run the following SQL command:
   @ $WL_COMMERCE_HOME/db/database-type/update-to-310.sql

   For example, if you installed WebLogic Commerce Server in ~/WebLogicCommerceServer3.2, enter the following from SQL*Plus:
   @ ~/WebLogicCommerceServer3.2/db/database-type/update-to-310.sql

When the command successfully completes upgrading the database, it prints the following message:

************ SCRIPT HAS FINISHED EXECUTING *************

****** PLEASE REVIEW UPDATE-TO-310.LOG FILE *****

 

---

Upgrading WebLogic Personalization Server Database Schemas from 2.0.1 to 3.2

This section describes scripts that do the following:

• Update tables that were created for the Personalization Server 2.0.1 schema to the Personalization Server 3.2 schema.

• Copy existing Personalization Server data to the new 3.2 tables.

• Create empty Personalization Server tables that are new for 3.1.1 and 3.2.

• Create empty Commerce Server tables that the release 3.2 schema defines. The scripts do not copy existing Commerce Server data into the tables.

The following instructions also assume that you are working with an Oracle database and will be using the scripts in either of the following:

• WL_COMMERCE_HOME/db/oracle (for Oracle versions 8.1.5 and below)

• WL_COMMERCE_HOME/db/oracle816 (for Oracle version 8.1.6)

The upgrade your Personalization Server database from 2.0.1, do the following:

1. In WL_COMMERCE_HOME/db/<oracle directory>/migration/v201, log into SQL*Plus and execute the following:

   SQL> @upgrade-to-310.sql

2. In WL_COMMERCE_HOME/db/<oracle directory>, log into SQL*Plus and execute the following:

   SQL> @create-wlcs-oracle.sql

   At this point the database format should be WLCS 3.1.1 compliant.

3. Follow the steps in Upgrading Database Schemas from 3.1.1 to 3.2. Then return to these instructions and continue with Step #4.

4. Start WebLogic Personalization Server and WebLogic Commerce Server if it is not already running.

5. In WL_COMMERCE_HOME\bin\win32\ (Windows) or in WL_COMMERCE_HOME/bin/unix/ (UNIX), run the following on a command line:

   loadrules

6. Finally, in WL_COMMERCE_HOME\bin\win32\ (Windows) or in WL_COMMERCE_HOME/bin/unix/ (UNIX), run the following on a command line:

   loaddocs -delete -cleanup

 

---

Upgrading Database Schemas
from 3.1.1 to 3.2

Release 3.2 of WebLogic Commerce Server and WebLogic Personalization Server introduces schema changes and restrictions for the length of data allowed in various columns. To upgrade databases from Release 3.1.1 to Release 3.2, complete the following tasks:

• Upgrade the WebLogic Personalization Server Schema

• Upgrade the WebLogic Commerce Server Schema (only if you use WebLogic Commerce Server)

• Verify the Upgrade

• Remove Temporary Tables

Upgrade the WebLogic Personalization Server Schema

If you are upgrading WebLogic Personalization Server from Release 3.1.1 to Release 3.2, complete the tasks described in this section. The following diagram illustrates the process for upgrading the WebLogic Personalization Server schema, and subsequent topics provide more information about the process.

Figure 4-1 Upgrading the Personalization Server Schema from 3.1.1 to 3.2

 

Step 1: Determine if Data Exceeds New Column Lengths
and Modify When Necessary

Start the migration process by finding and correcting any columns in your existing databases that contain data exceeding the new column length in Release 3.2.

To start the migration, do the following:

1. Make a backup copy of your database.

2. Run the following SQL command:
   @ $WL_COMMERCE_HOME/db/database-type/check-wlps-lengths.sql

   For example, if you installed WebLogic Commerce Server in ~/WebLogicCommerceServer3.2, enter the following command in SQL*Plus:
   @ ~/WebLogicCommerceServer3.2/db/database-type/check-wlps-lengths.sql

3. To see the results of the script, open the following log file in a text editor: $WL_COMMERCE_HOME/db/database-type/check-wlps-lengths.log

   The log file lists each table for which the maximum number of characters has changed. As Listing 4-1 illustrates, the log file states no rows selected for tables that meet the new maximum-length requirements. For tables that exceed requirements, the log file lists each row and describes the error condition.

   Listing 4-1 Output of check-wlps-lengths.sql

   *****  WLCS_DOCUMENT.ID  *****
   no rows selected

   *****  WLCS_DOCUMENT_METADATA.ID  *****
   no rows selected

4. For any table containing data that exceeds a row's maximum length requirement:

   1. For information on length requirements for the table, see the schema chapters in the WebLogic Commerce Server and WebLogic Personalization Server documentation.

   2. Modify the data in the row to meet the new requirements.

5. Repeat steps 2-4 until the log file reports "no rows selected" for all tables.

Step 2: Upgrade the Database Schema

After correcting any rows that do not conform to new column length requirements, you must upgrade the Release 3.1.1 schema to the Release 3.2 schema by doing the following:

1. Make backup copies of your database and the following file:
   $WL_COMMERCE_HOME/db/database-type/upgrade-wlps-to-320.sql

2. Open upgrade-wlps-to-320.sql in a text editor.

3. Make sure that the following lines assign values that match your tablespace:

   define DATA_TABLESPACE=WLCS_DATA
   define INDEX_TABLESPACE=WLCS_INDEX

   By default, WebLogic Commerce Server and WebLogic Personalization Server place data in WLCS_DATA and indexes in WLCS_INDEX. If you are using other tablespaces, you must modify upgrade-wlps-to-320.sql to specify your tablespaces instead.

4. Save your modifications to upgrade-wlps-to-320.sql.

5. Run the following SQL command:
   @ $WL_COMMERCE_HOME/db/database-type/upgrade-wlps-to-320.sql

   Note: Enter this command only once and only after you have modified all rows that contain data exceeding new length requirements.

When the command successfully completes updating tables, it prints the following message:

************ SCRIPT HAS FINISHED EXECUTING *************

****** PLEASE REVIEW UPDATE-TO-320.LOG FILE *****

Upgrade the WebLogic Commerce Server Schema

To upgrade WebLogic Commerce Server from Release 3.1.1 to Release 3.2, complete the tasks described in this section. The following diagram illustrates the process for upgrading the WebLogic Commerce Server schema, and subsequent topics provide more information about the process.

Figure 4-2 Upgrading the Commerce Server Schema from 3.1.1 to 3.2

 

Step 1: Determine if Data Exceeds New Column Lengths
and Modify When Necessary

Start the migration process by finding and correcting any columns in your existing databases that contain data exceeding the new column length in Release 3.2.

To start the migration, do the following:

1. Make a backup copy of your database.

2. Run the following SQL command:
   @ $WL_COMMERCE_HOME/db/database-type/check-wlcs-lengths.sql

3. To see the results of the script, open the following log file in a text editor: $WL_COMMERCE_HOME/db/database-type/check-wlcs-lengths.log

   The log file lists each table for which the maximum number of characters has changed. As Listing 4-2 illustrates, the log file states no rows selected for tables that meet the new maximum-length requirements. For tables that exceed requirements, the log file lists each row and describes the error condition.

   Listing 4-2 Output of check-wlcs-lengths.sql

   *****  WLCS_CATEGORY  *****
   no rows selected

   *****  WLCS_PRODUCT  *****
   no rows selected

4. For any table containing data that exceeds a row's maximum length requirement:

   1. For information on length requirements for the table, see the schema chapters in the WebLogic Commerce Server and WebLogic Personalization Server documentation.

   2. Modify the data in the row to meet the new requirements.

5. Repeat steps 2-4 until the log file reports no rows selected for all tables.

Step 2: Upgrade the Database Schema

After correcting any rows that do not conform to new column length requirements, you must upgrade the Release 3.1.1 schema to the Release 3.2 schema by doing the following:

1. Make backup copies of your database and the following file:
   $WL_COMMERCE_HOME/db/database-type/upgrade-wlcs-to-320.sql

2. Open upgrade-wlcs-to-320.sql in a text editor.

3. Make sure that the following lines assign values that match your tablespace:

   define DATA_TABLESPACE=WLCS_DATA
   define INDEX_TABLESPACE=WLCS_INDEX

   By default, WebLogic Commerce Server and WebLogic Personalization Server place data in WLCS_DATA and indexes in WLCS_INDEX. If you are using other tablespaces, you must modify upgrade-wlps-to-320.sql to specify your tablespaces instead.

4. Save your modifications to upgrade-wlcs-to-320.sql.

5. Run the following SQL command:
   @ $WL_COMMERCE_HOME/db/database-type/upgrade-wlcs-to-320.sql

   Note: Enter this command only once and only after you have modified all rows that contain data exceeding new length requirements.

When the command successfully completes updating tables, it prints the following message:

************ SCRIPT HAS FINISHED EXECUTING *************

****** PLEASE REVIEW UPDATE-TO-320.LOG FILE *****

Verify the Upgrade

After you upgrade the schema for each server that you are using, verify the upgrade by starting the server and Administration Tool and testing the application. For example, if you use both WebLogic Commerce Server and WebLogic Personalization Server, open the Administration Tool to verify that the users and groups you upgraded are available under User Administration, and all items and categories that you upgraded are available under Catalog Administration. Then access the server through a Web browser to verify that data transferred successfully.

To Start the Server

To start WebLogic Commerce Server and/or WebLogic Personalization Server on UNIX, enter the following command from a WebLogic Commerce Server and WebLogic Personalization Server host:
$WL_COMMERCE_HOME/StartCommerce.sh

To start WebLogic Commerce Server and/or WebLogic Personalization Server on Windows, on a WebLogic Commerce Server and WebLogic Personalization Server host, do one of the following:

• Click Start --> Programs --> WebLogic Commerce Server 3.2 --> Start Commerce Server.

• From a command prompt, enter the following command:
  %WL_COMMERCE_HOME%\StartCommerce.bat

Remove Temporary Tables

Note: Do not complete this step until you have successfully upgraded the schema for both servers (if you use both servers) to Release 3.2 and started the application and verified the data migration.

After you have verified that WebLogic Commerce Server and WebLogic Personalization Server function properly with the imported data, remove the temporary BEA_table-name tables by running the following SQL command:
@ $WL_COMMERCE_HOME/db/database-type/drop_bea_tables.sql

When the command successfully completes removing BEA_ tables, it prints the following message:

************ SCRIPT HAS FINISHED EXECUTING *************

****** PLEASE REVIEW DROP_BEA_TABLES.LOG FILE *****

 

---

Upgrading Database Schemas from 3.2 to 3.5

Release 3.5 of WebLogic Commerce Server and WebLogic Personalization Server provides enhancements and changes that require you to update the schemas and migrate the data.

Note: If you are using the Oracle 8.0.5 or 8.1.5 database, you must upgrade to Oracle 8.1.6 or greater before migrating to Release 3.5 of WebLogic Commerce Server and WebLogic Personalization Server. Release 3.5 uses CLOBs and BLOBs instead of LONG RAW characters. Oracle 8.0.5 and 8.1.5 do not support CLOBs and BLOBs.

To upgrade databases from Release 3.2 to Release 3.5, complete the following tasks:

• Make a Backup

• Validate Data

• Upgrade Current Tables to New Schema

• Drop Any Backup Tables (Optional)

• Add New Tables to Bring the Schema Current

• Verify the Upgrade

Make a Backup

We strongly recommend that you make a complete backup of the WebLogic Personalization Server/WebLogic Commerce Server database before beginning the process to migrate the database to a more current schema.

Validate Data

To validate the data before beginning the upgrade, run the following scripts. These scripts are used to check which WebLogic Commerce Server tables have column values exceeding 254 characters, or whatever the new length for the specific column in question is.

1. From a command prompt, type the following:

   @ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/check_common_lengths.sql

   @ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/check_wlps_lengths.sql

   @ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/check_wlcs_lengths.sql

For example, if you are using Oracle 8.1.6 and installed WebLogic Commerce Server in ~/WebLogicCommerceServer3.5, enter the following command in SQL*Plus:

@ $WL_COMMERCE_HOME/db/oracle/8.1.6/migration/v320/check_common_lengths.sql

1. To see the results of the script, open the following log file in a text editor:

   @ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/check_common_lengths.log  

   There will also be check_wlps_lengths.log and check_wlcs_lengths.log.

   The log file lists each table for which the maximum number of characters has changed. As Listing 4-2 illustrates, the log file states no rows selected for tables that meet the new maximum-length requirements. For tables that exceed requirements, the log file lists each row and describes the error condition.

   Listing 4-3 Output of check-wlcs-lengths.sql

   *****  WLCS_CATEGORY  *****
   no rows selected

   *****  WLCS_PRODUCT  *****
   no rows selected

2. For any table containing data that exceeds a row's maximum length requirement:

   1. For information on length requirements for the table, see the schema chapters in the WebLogic Commerce Server and WebLogic Personalization Server documentation.

   2. Modify the data in the row to meet the new requirements.

3. Repeat the previous steps until the log file for each script reports no rows selected for all tables.

Upgrade Current Tables to New Schema

Once you have successfully verified the current tables, you can run the upgrade scripts. These scripts make backup copies of current tables, drop the existing table, re-create each table with current column definitions, and populate the new table with data from the backup table.

From a command prompt, type the following:

@ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/upgrade_common_to_350.sql

@ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/upgrade_wlps_to_350.sql

@ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/upgrade_wlcs_to_350.sql

Drop Any Backup Tables

Note: This step is optional and should not be executed until you are certain that all data has been retained. Only when you are comfortable with the state of your data should you consider running this script.

The script in this step is used to drop the temporary tables that held your original data. During the upgrade process, the data contained in the original table (named with the WLCS_ prefix) was copied to a backup table using the BEA_ prefix and renamed with BEA_ prefix.

1. Restart the WebLogic Commerce Server before executing this script, to test data accuracy and to ensure the application(s) are working as intended.

2. From a command prompt, type the following:

   @ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/drop_bea_tables.sql 

Add New Tables to Bring the Schema Current

Run the following scripts to create the necessary new tables.

• insert_event_properties.sql is used to populate the property management tables with records of various Behavior Tracking events.

• upgrade_wlcs_add_tables_350.sql is used to create new tables used by the WebLogic Commerce Server. It is involved in making changes to the existing database objects: adding columns, dropping columns, resizing columns, and so on.

• create_campaign.sql and create_mail_ad.sql are used in the new WebLogic Campaign Manager component. Each script is involved in creating new database objects such as tables and indexes.

From a command prompt, type the following:

@ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/
upgrade_wlcs_add_tables_350.sql

@ $WL_COMMERCE_HOME/db/db-vendor/db-version/migration/v320/
insert_event_properties.sql

Note: The path from which to run the next two scripts is different from where you ran previous scripts.

@ $WL_COMMERCE_HOME/db/db-vendor/db-version/create_campaign.sql

@ $WL_COMMERCE_HOME/db/db-vendor/db-version/create_mail_ad.sql

Verify the Upgrade

After you upgrade the schema for each server that you are using, verify the upgrade by starting the server and Administration Tool and testing the application. For example, if you use both WebLogic Commerce Server and WebLogic Personalization Server, open the Administration Tool to verify that the users and groups you upgraded are available under User Administration, and all items and categories that you upgraded are available under Catalog Administration. Then access the server through a Web browser to verify that data transferred successfully.

To Start the Server

To start WebLogic Commerce Server and/or WebLogic Personalization Server on UNIX, enter the following command from a WebLogic Commerce Server and WebLogic Personalization Server host:
$WL_COMMERCE_HOME/StartCommerce.sh

To start WebLogic Commerce Server and/or WebLogic Personalization Server on Windows, on a WebLogic Commerce Server and WebLogic Personalization Server host, do one of the following:

• Click Start --> Programs --> WebLogic Commerce Server 3.5 --> Start Commerce Server.

• From a command prompt, enter the following command:
  %WL_COMMERCE_HOME%\StartCommerce.bat