Authoring server schema update tool

The content management solution in WebSphere Commerce introduces multiple database schemas on the authoring server that require each and every table to have a definition within each schema. The definition within the workspaces schemas differ depending on whether the table is considered to be one of the following resources:

  • Managed content resource
  • Operational resource
  • Unmanaged resource

These resources are specified in the resource.xml configuration files (all unspecified tables are considered unmanaged resources).

During instance creation of the authoring server, new database schemas are created based on all the tables and views defined in base schema at the time. Previously, if the base schema was modified in any way, for example new tables were added or fix packs installed, the authoring schemas were updated manually. With the introduction of the authoring server schema update tool you can now update the workspaces schema easily.

The authoring update tool makes the customization and installation process simpler, by having the workspace management schemas allocated, recalibrated or patched by running an Ant script in the server environment, and a batch file in the development environment. This means that when you change the schema, you can run the Ant script or batch file on an authoring server and determine any new tables that must be managed into the workspace schemas. As a result, you will no longer have to follow the manual steps to adjust the workspace schemas. You can also use this tool to adjust your workspace schemas when you create your own content managed resources. If you introduce new tables, you can run the Ant script or batch file through a command line API.

The following Ant scripts are used in the server environment:

  • UpdateWorkspacesSchemaTable
  • UpdateWorkspacesSchema

WebSphere Commerce Developer The following batch files are used in the development environment:

  • UpdateWorkspacesTable.bat
  • UpdateWorkspacesSchema.bat

When to use the authoring server update tool

If you deploy changes to your WebSphere Commerce database schema, such as adding a new table, these changes must also be made in the workspace schemas. This tool allows you to recalibrate the workspace schemas with the master database schema. Without recalibration, your authoring server will not work with your new changes to the base schema, and may cause data lost or inconsistency.
Note: The WebSphere Commerce Server must not be running since this tool updates the workspace schema structure.

Supported functions

The authoring server update tool supports the following functions:

  • Adjusts the workspace schemas according to the resource type of the table in the following situations:
    • You add new tables to the base schema.
      Note: Content-managed tables must have a primary key defined.
    • You increase the size of a column in any existing table in the base schema.
      Note: Only column types that are allowed to be increased by the ALTER SQL command will be adjusted. The list of column types supported for column size increase are VARCHAR and VARCHAR2.
    • You change an existing non-managed type table to a content-managed type table.
    • You change an existing non-managed type table to an operational type table.
  • Adds new columns to all of the corresponding workspace tables according to the resource type of the table.
  • Adds new indexes or deletes existing indexes for any existing table.
  • Gives users the option to generate the "update SQLs" statement to a file or have the tool directly implement the "update SQLs" statement.
Note: None of the supported functions delete tables or data from the existing workspace schemas. If a table gets deleted in the base schema, the table in the write schema of the workspace for any content-managed or operational table remains. You will not lose any data after running this tool.

Supported databases

The workspace update tool only supports the following databases:

  • DB2 database system
  • Oracle

Non-supported functions

The following functions are not provided by the workspace schema update tool:

  • Changing an existing content-managed type table to a non-managed type table. This is not supported because it requires removing tables from the existing workspaces and can result in data loss. It is recommended that you do a manual update to the workspace as described in the customization guide for this scenario.
  • Changing an existing operational type table to a non-managed type table. This is not supported because it requires removing tables from the existing workspaces and can result in data loss. It is recommended that you do a manual update to the workspace as described in the customization guide for this scenario.
  • Dropping a table in the base schema. This is not supported as the recommended way for removing a table in WebSphere Commerce is by deprecating a table and not dropping a table. If you must drop a table, it is recommended to do a manual update to the workspace as described in the customization guide.