WebSphere Commerce Developer

Completing the development database migration

After you have successfully migrated your development database, additional tasks are required to complete the database tier migration.

Procedure

  1. Restore any custom constraints that you dropped before migrating the database.
  2. If migrating from a WebSphere Commerce Version 5.6.1 environment, you must add the OPTCOUNTER column to your custom database tables after migrating the database. The OPTCOUNTER column enables you to set up optimistic locking on your system.
    1. Determine the custom tables you want to enable optimistic locking. You must create a trigger for every custom table that you want to enable optimistic locking on. This trigger increments the OPTCOUNTER value every time an update needs to be made to your table.
    2. Review the following SQL file for an example of how to define a trigger:
      • WC_installdir/schema/db_type/wcs.perf.trigger.sql
      where db_type is the database type (DB2, Oracle, Cloudscape).
      Apache DerbyNote: Use Cloudscape for Apache Derby databases.

      This file defines the WebSphere Commerce triggers for tables that are ready for you to use as is.

    3. For each custom table, issue the following SQL command:
      For IBM i OS operating systemApache DerbyDB2
      ALTER TABLE table_name ADD COLUMN optcounter SMALLINT
      Oracle
      ALTER TABLE table_name ADD optcounter SMALLINT
      where table_name is the name of the custom table.
  3. DB2Run the DB2 reorg command:
    Note: If your WebSphere Commerce node is using a remote database, catalog the remote database on the WebSphere Commerce node
    1. WindowsOpen a DB2 command window.
    2. SolarisLinuxAIXLog on to the system using the DB2 instance owner ID.
    3. Navigate to the WC_installdir\bin directory.
    4. Run the following command:
      Windows
      reorg.db2.bat db_name db_user db_password (db_schema_user)
      SolarisLinuxAIX
      ./reorg.db2.sh db_name db_user db_password (db_schema_user)
      The optional parameter db_schema_user is used in the previous commands if db_user is different from db_schema_user.
  4. DB2Set integrity check for any custom new tables that reference any of the following WebSphere Commerce tables:
    • ACCOUNT
    • ATCHAST
    • ATCHTGT
    • ATCHTGTDSC
    • CATENTDESC
    • CATENTRY
    • CATGROUP
    • CATGRPDESC
    • COLLATERAL
    • CMFILE
    • DMACTIVITY
    • DMCAMPAIGN
    • EMLMSG
    • EMSPOT
    • GRADDR
    • MBRGRP
    • PX_PROMOAUTH
    • PX_PROMOCD
    • STORE
    For every custom new table that has a foreign key that references one or more tables in the preceding list, run the following SQL command:
    db2 set integrity for table_name immediate checked
    where table_name is the name of the custom new table.
  5. You can safely delete any tables that are named with an _OLD suffix. These tables are created during the database migration process as a temporary backup.
    Note: Ensure that you keep a backup of your WebSphere Commerce Version 6.0 database.
  6. If you are migrating from WebSphere Commerce Version 7 Feature Pack 1, Feature Pack 2, Feature Pack 3, Feature Pack 4, Feature Pack 5, or Feature Pack 6, and you have configured workspaces and have already run the update workspaces schema script after migrating, run the following SQL statement in each workspace write schema:
    • Cloudscape:
      ALTER TABLE PAGELAYOUT ALTER COLUMN PAGELAYOUTTYPE_ID NULL;
    • DB2:
      ALTER TABLE PAGELAYOUT ALTER COLUMN PAGELAYOUTTYPE_ID DROP NOT NULL;

CALL SYSPROC.ADMIN_CMD ('REORG TABLE PAGELAYOUT');
    • Oracle:
      ALTER TABLE PAGELAYOUT MODIFY PAGELAYOUTTYPE_ID NULL;