Guidelines for adding custom columns to a default table

You can add custom columns to a default table by following a set of guidelines.

  • You can add columns to tables only as specified in the ERDs.
  • You cannot remove or modify any default columns.
  • You can add columns either before or after installing the application.
  • For all columns that are added to an application database table, it is recommended to use Nullable=true except for the following fields:
    • Primary Key Attributes
    • Entity Relationships
    If column is nullable, you do not need to provide a default value. However, if column is not nullable, you must provide a default value that is relevant to the column.
  • You cannot add columns with a data type of Long.
  • When you use application components (such as events and user exits) that read in a map or publish a map (such as the GetOrderNoUE user exit), extended fields in the maps are prefixed with Extn_.
  • When using non-ASCII characters, ensure that the Name and XMLName attributes are passed where appropriate.
Note: In DB2® database, the Date data type is generated as TIMESTAMP.

Validations on extended large and LOB columns

  • When extending the database, these validations prevent creation of extended columns with a datatype CLOB or BLOB or if its size exceeds 1000 characters both for default and custom tables. This validation applies only to new columns, while existing columns remain unaffected.
  • If your requirement involves storing large amounts of data or using LOB columns, the recommended approach is to store this data in a cloud object storage service of your choice, owned by you. In the Sterling™ Order Management System, you can store the related metadata such as the URL, bucket name, and file path, and use this information to perform CRUD operations on the data through your custom code.
  • For DTK testing, any existing large or LOB columns can be explicitly allowed by adding them to the following properties in om-functional.properties.
    • yfs.customvalidation.allowedLobColumns for existing custom CLOB or BLOB columns
    • yfs.customvalidation.allowedVarcharColumns for existing custom VARCHAR columns with a size greater than 1000 characters. These properties should be configured in the following format. 
      yfs.customvalidation.allowedLobColumns=tablename1:colname1,colname2|tablename2:colname3,colname4