IBM Developer

Article

Optimize the IBM Sterling Order Management database size

Reduce the IBM Sterling Order Management database size with entity level compression and leverage enhanced purges

By Shoeb Bihari

On this page

Sterling Order Management uses database to store transaction, configuration, and master data flows. The transaction tables continue to grow until orders are purged from the database. For enterprise clients, the continuous database growth might result in space constraints. Typically, database updates of Sterling Order Management are audited, including custom tables, if enabled. These audits are stored as XML files within the CLOB column types. Besides audits, errors and JMS messages are also stored in a similar manner. On an average, the CLOB data contributes to more than 75% of the database size. In a recent release, IBM has rolled out platform-level enhancements to optimize the database size, particularly enhancement on reducing the CLOB space.

Learn how Sterling Order Management administrators can leverage platform-level application enhancement for entity compression, and purge to reduce the overall database size. The application also supports entity-level data compression of CLOB columns by using the GZip algorithm.

Note: This article is applicable to versions later than Release 22.3 - 10.0.2209.0 (26 August 2022).

Available features

Data compression

Data compression for the application-provided and custom CLOB columns

Sterling Order Management is enhanced that provides a configuration to compress data of compression supported columns. By using the configuration, you can compress the column data without customizing, rebuilding, and redeploying Sterling Order Management. For more information about the updated compression guidelines, see Guidelines for adding support for data compression for columns.

Support for History data compression

The db.force.compression.history.records property enables data compression on all the compression supported columns when data is written to history tables. The db.force.compression.history.records property also ensures that the data written to history tables is compressed, even if compression is not enabled for the compression supported columns in the transaction table. For more information about configuring the db.force.compression.history.records property, see history compression property.

Custom data compression logic

The data for compressible columns is compressed by using the GZip algorithm. You can override the default data compression logic and provide your custom compression logic by implementing the SCICustomDataCompressor interface. In the customer_overrides.properties file, configure the yfs.db.compression.class=class name property with the name of the custom class that implements the SCICustomDataCompressor interface.

YFS_IMPORT and YFS_EXPORT tables compression support

Sterling Order Management is enhanced to support compression for the MESSAGE column of the YFS_IMPORT and YFS_EXPORT tables.

Note: After you enable the properties, the entity level compression logic is run only if the CLOB value is more than 500 bytes, which is the default value. This approach minimizes performance degradation and maximizes gain around performance and stability of the database.

Purge enhancement

Order Audit Delete purge agent

The Order Audit Delete Purge agent extends the capability of Order Purge agent by deleting the order audit records from Sterling Order Management even before the Order Purge agent purges orders.

The behavior of the Order Purge agent is enhanced to support the configuration for deleting the order audit data instead of moving the data to history tables, and publishes the order audit data through a user exit. For more information about the configuration to enable deletion of the order audit data, see the order audit delete property.

User exit to publish the order audit data to an external system

For Order Audit Delete Purge and Order Purge agents, use the YFSBeforeOrderAuditPurgeUE user exit that publishes the order audit records that are deleted from the application on purge of an order. Implement the YFSBeforeOrderAuditPurgeUE user exit only if you want to store the order audit records outside of Sterling Order Management, for future reference. For more information about the YFSBeforeOrderAuditPurgeUE user exit, see Javadoc.

Export purge enhancements

A new criteria parameter is added to the YFS_EXPORT purge agent to ignore the status value and purge all records. For more information about the IgnoreStatus parameter, see Export table purge.

Optimization

A combination of data compression and purge enhancement features significantly reduces the database space. In simple words, the important application-provided tables are identified, and the configuration is split into multiple scenarios, which are moderate gains and maximum gains.

Moderate gains

For moderate gains, enable the entity compression for history data, and skip the order audit data to history tables by configuring the following properties:

db.force.compression.history.records=true

To enable force data compression on all the compression supported columns when data is written to history tables, set this property to true. The default value is set to false. For more information about configuring the db.force.compression.history.records property, see history compression property.

yfs.purge.order.orderaudit.delete=true

To skip the order audit data purge to order audit history tables, set the value of the yfs.purge.order.orderaudit.delete property to true. The order audit data is deleted from the transaction table without moving to the history table. If you want to store the order audit records outside of Sterling Order Management, implement the YFSBeforeOrderAuditPurgeUE user exit.

Maximum gains

In addition to configuring the mentioned properties, for maximum gains, you can implement compression along with enhanced purges. The configuration for the following tables are split as follows:

YFS_EXPORT

Enable compression on the MESSAGE column of the YFS_EXPORT table by configuring the following properties:

  • db.force.compression.tables=YFS_EXPORT
  • db.force.compression.YFS_EXPORT.columns=MESSAGE

Then, run the export table purge purge service.

To purge records without processing, set the IgnoreStatus flag to Y. For more information about the IgnoreStatus parameter, see Export table purge.

To publish data to an external system, such as COS for longer retention, implement YFSBeforePurgeUE.

YFS_ORDER_AUDIT_DETAIL

To enable compression on the AUDIT_XML column of the YFS_ORDER_AUDIT_DETAIL table, configure the following properties and run Audit purge.

  • db.force.compression.tables=YFS_ORDER_AUDIT_DETAIL
  • db.force.compression.YFS_ORDER_AUDIT_DETAIL.columns=AUDIT_XML

To optimize, you can set AdditionalPurgeCode value to ORDERAUDITDEL and ORDER_RELEASE_STATUS_PURGE.

Even though the YFS_ORDER_RELEASE_STATUS table does not contain any CLOB data, it is important that you purge this table aggressively by running the Order Release purge. If you choose to retain order audits outside of Sterling Order Management, export data at the time of purge by implementing YFSBeforeOrderAuditPurgeUE. This user exit allows you to publish order audit to an external system, such as COS.

YFS_IMPORT, YFS_ASYNC_REQ, and YFS_ASYNC_REQ_ERROR

Enable compression on the MESSAGE column of the YFS_IMPORT and YFS_ASYNC_REQ tables, and on the ERROR_MESSAGE column of the YFS_ASYNC_REQ_ERROR table.

  • db.force.compression.tables=YFS_IMPORT,YFS_ASYNC_REQ,YFS_ASYNC_REQ_ERROR
  • db.force.compression.YFS_IMPORT.columns=MESSAGE
  • db.force.compression.YFS_ASYNC_REQ.columns=MESSAGE
  • db.force.compression.YFS_ASYNC_REQ_ERROR.columns=ERROR_MESSAGE

Also, configure and run Import Table purge. For more information about the import table purge, see Import table purge.

YFS_INBOX and YFS_REPROCESS_ERROR

Both YFS_INBOX and YFS_REPROCESS_ERROR tables contain CLOB columns for which you can implement compression. However, it is recommended that data should be purged aggressively to control the retained space, and upgrade UI performance for Sterling Store Engagement and Sterling Call Center, specifically for the YFS_INBOX table. Enable the YFS_INBOX and YFS_REPROCESS_ERROR processing, and purge them aggressively. For more information about these purges, see Inbox purge and Reprocess error purge.

Set the default expiration days for alerts so that exceptions may be automatically closed. A value of zero means that exception does not expire. For more information about alerts, see Alert.

Compress custom CLOB type columns

For custom columns, enable data compression by adding CompressionSupported="true" and UseCompression="true" attributes in the entity XML as illustrated in the following sample code.

   <!--Entity xml for custom CLOB columns to enable compression-->
   <Entity TableName="YFS_ORDER_INVOICE">
      <Attributes>
            <Attribute ColumnName="EXTN_XYZ_INVOICE_DETAIL" CompressionSupported="true" UseCompression="true" Name="Extn_Xyz_Invoice_Detail"/>
      </Attributes>
   </Entity>

Alternatively, you can update the entity XML with UseCompression="true" attribute for any CLOB column as illustrated in the following sample code:

   <!--Entity xml for custom CLOB columns to enable compression-->
   <Entity TableName="YFS_ORDER_INVOICE">
      <Attributes>
         <Attribute ColumnName="EXTN_XYZ_INVOICE_DETAIL" CompressionSupported="true" Name="Extn_Xyz_Invoice_Detail"/>
      </Attributes>
   </Entity>

Then, configure the following properties:

  • db.force.compression.tables=YFS_ORDER_INVOICE
  • db.force.compression.YFS_ORDER_INVOICE.columns=EXTN_XYZ_INVOICE_DETAIL

Benefit analysis

A comparative study has been conducted to identify the benefit and impact on performance by using the entity level compression, specifically for the AUDIT_XML column of the YFS_ORDER_AUDIT_DETAIL table.

Setup

To enable compression on the AUDIT_XML column of the YFS_ORDER_AUDIT_DETAIL table, add the following properties:

  • db.force.compression.tables=YFS_ORDER_AUDIT_DETAIL
  • db.force.compression.YFS_ORDER_AUDIT_DETAIL.columns=AUDIT_XML

Run criteria

  • For this test, 3000 order lines are created and scheduled for Sterling Call Center scenarios.
  • The API response time is evaluated for changeOrder, cancelOrder, and modiftyFullfillmentOptions APIs. These APIs are selected to maximize the number of audit records.
  • Audits are enabled for all attributes and order modification.

Results

The Sterling Call Center scenarios are tested. Also, the benefits and impact on API response, CPU usage, and so on are monitored. As a result of this, 45% improvement is observed in the disk space utilization with compression, and without much of a GC overhead and VM CPU.

The following diagram illustrates the relevant statistics:

Note: The inserted records do not mean the number of compressed records. This is because, the logic provided by the application only compresses CLOB value over 500 bytes. In this test, 2052 records were compressed.

Summary

By compressing the AUDIT_XML column of the YFS_ORDER_AUDIT_DETAIL table, 45% reduction is observed in the overall CLOB space. This is a significant gain and will substantially reduce the database backup time, and helps database maintenance, such as REORG and RUNSTATS.

Note:

  • To verify that there isn't any significant performance degradation, run a load test before and after compression.
  • All the compression properties are yfs.properties. This means that if you are adding through System Management Administrator (SMA), add the select yfs category. If you are adding through customer_overrides.properties, prefix the property with yfs. For more information about configuring the compression properties, see compression properties.

Frequently asked questions

  1. If the database level compression is enabled, is it necessary to use entity level compression, or use both database and entity level compression? Yes, entity level compression reduces the database size and network bandwidth without much of a overhead to the JVM.
  2. How do I disable data compression? Is it necessary to decompress the existing compressed data? You need not decompress the existing compressed data. This is because, the enhanced entity logic reads and writes both compressed and uncompressed data. If you want to disable data compression, revert the configuration.

Additional resources

Acknowledgement

The author would like to thank Bobby Thomas for his contribution to this article. Bobby Thomas is a performance architect for IBM Sterling Order Management Software.