Sterling Order Management System Software schema

You create and configure the Sterling™ Order Management System Software schema. You will also modify some statements for production.

You create the Sterling Order Management System Software database, the Sterling Order Management System Software tables, and their associated indexes. Additionally, the following DDL statements allow you to create a simple schema that is suitable for general use. You need to review and possibly modify these statements for production. Specifically:

The DDL statements create a minimal set of indexes for general Sterling Order Management System Software use. You may need to create additional or modify existing indexes for your business practice.
The DDL statements create a single tablespace with a large data file. You may want to create additional tablespaces for manageability as well as additional data files for I/O load balancing.

Indexes

Most customers use a subset of the broad functionality in Sterling Order Management System Software. As a result, the base Sterling Order Management System Software database schema with the default or starting set of indexes may have to be adjusted for your specific use. Therefore, you should validate the starting index set to see if they support your workloads.

As a suggestion, prior to production, you should conduct a system test where all the key screens, agents and APIs run against a copy of the production database. During this test, you can check if additional indexes are required and if there are any unused indexes you can disable or drop.

Please see the following sections on how to enable index monitoring:

For Oracle, see Oracle Index Monitor.
For Db2®, see Index Monitor.

Custom indexes

Please follow the following convention when you create a new index:

First, make sure the index name does not end with the following suffix:

_PK" - This suffix is reserved for indexes that are the primary key for the underlying table. For example, the index, yfs_order_header_pk, is the primary key index for the yfs_order_header table.
_Inn" where nn is an integer value from 0 to 99 - This suffix is reserved for secondary or alternate indexes for the underlying table. For example, the index, yfs_order_header_i1, is a secondary index for the yfs_order_header table.

The convention above prevents situations where new base Sterling Order Management System Software indexes have the same name as one of your custom index.

Secondly, to further differentiate custom indexes from the base, the custom index should start with EXTN_ as a prefix and Xnn in the index name. For example, if you add two indexes to the YFS_ORDER_HEADER table, the index names should be EXTN_ORDER_HEADER_X1 and EXTN_ORDER_HEADER_X2.