Compress the IBM Sterling Order Management database

IBM Sterling™ Order Management uses either an Oracle or Db2® database to store transaction, configuration, and master data flows. Typically, Sterling Order Management transactions spend from 5 to 30 percent of time performing select, update, insert, and delete operations. Transaction tables continue to grow until orders are purged from the database. For enterprise clients, this continuous database growth might cause storage concerns. To optimize storage space, enable data compression.

This article describes how Sterling Order Management admins and database admins can enable data compression in Db2. For information about compression techniques on an Oracle database, see Oracle’s product documentation.

Prerequisites

  • Knowledge of Db2 database, preferably as a database administrator.
  • Knowledge of Sterling Order Management, utilities and the table growth pattern.
  • Ability to construct SQL scripts for compressing tables, indexes and database maintenance.

Estimated time

Expect to spend about 45 minutes to understand how to modify table and index definitions.

Db2 data compression

Sterling Order Management tables and indexes can contain repeated information, for example, the INVENTORY_ITEM_KEY column is indexed multiple times in inventory tables such as YFS_INVENTORY_DEMAND, YFS_INVENTORY_SUPPLY and YFS_INVENTORY_ALERTS. This repetition allows for easier fetches, but also increases storage needs.

Db2 provides two main compression techniques, as follows.

Compression type Description Db2 edition
Value Optimizes space usage of data representation by removing duplicate entries for a value. All
Table Table compression aids in storing more rows in fewer pages there by reducing the input/output operations involved in accessing the data. In addition, the buffer pool hit ratio is improved due to more rows of data in a page thereby increasing the likelihood of identifying the data. There are two types of table compression, classic row compression and adaptive compression. Adaptive compression improves upon the compression rates achieved by the classic row compression by not only working on table-level but also on a page-page basis to further compress the data. In comparison with classic compression, adaptive compression further compresses the data. Compared to all other options offered by Db2, the adaptive compression technique offers the most storage savings. Since rows are compared, the volume of log data written as a result of insert, update or delete operations is reduced. Db2 Storage Optimization

Test

To determine the storage savings using adaptive compression on a IBM Sterling Order Management database, we tested a typical order fulfillment load, without database compression and with adaptive database compression. After database compression, the database was optimized by using the REORG and RUNSTATS commands. Overall, we observed ~50-percent savings in disk space for active databases and backups, as shown in the following table.

Transaction table Without compression (MB) With adaptive compression (MB) Savings (%)
YFS_ORDER_AUDIT_DETAIL 236.5 210.0 11.21
YFS_ORDER_RELEASE_STATUS 439.0 176.5 59.79
YFS_INVENTORY_ALERTS 506.0 205.0 59.49
YFS_SHIPMENT_LINE 173.5 74.7 56.96
YFS_TASK_Q 89.5 62.5 30.17
YFS_INVENTORY_AUDIT 94.5 53.5 43.39
YFS_ORDER_LINE_SCHEDULE 240.5 42.3 82.40
YFS_ORDER_HEADER 70.5 28.5 59.57
YFS_ORDER_LINE 362.5 34.0 90.62
YFS_ORDER_RELEASE 57.5 22.3 61.16
YFS_SHIPMENT 86.5 22.3 74.18
YFS_ORDER_AUDIT_LEVEL 58.5 19.3 66.95
YFS_SHIPMENT_STATUS_AUDIT 26.0 11.0 57.69
YFS_ORDER_AUDIT 36.5 8.3 77.17
YFS_PAYMENT 21.5 6.0 72.09

Observations

  1. The database CPU, memory and disk read/writes performance had negligible (<3%) differences before and after compression.
  2. The API response times showed a +/- 0.5% difference before and after compression. The maximum impact was 25 ms.

Steps

Before you begin

The estimated savings that adaptive row compression offers depend on the statistics generated by running the RUNSTATS command. To get the most accurate estimate of the savings that can be achieved, run the RUNSTATS command.

Estimate compression savings

You can view an estimate of the storage savings adaptive row compression can provide for a table by using the ADMIN_GET_TAB_COMPRESS_INFO table function:

  1. Formulate a SELECT statement that uses the ADMIN_GET_TAB_COMPRESS_INFO table function.
     SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SVT_V95', 'YFS_INVENTORY_SUPPLY'))  with ur;
    
  2. Execute the SELECT statement.
     SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(‘OMSDB’,’’)
    

A report similar to the following is displayed.

TABNAME PCTPAGESSAVED_CURRENT AVGROWSIZ PCTPAGESAVED_STATIC AVGROWSIZ PCTPAGESSAVED_ADAPTIVE AVGROWSIZE_ADAPTIVE
YFS_INVENTORY_SUPPLY 0 369 80 70 86 39

The current row size in this table is 369, and when adaptive compression is enabled the row size decreases to 39, an 86-percent improvement. Typically, this table contains thousands of millions of records so by enabling adaptive compression multiple gigabytes (and backup) could be saved.

Enable adaptive compression

By using the ALTER TABLE statement, you can modify an existing table to take advantage of the storage-saving benefits of compression.

ALTER TABLE <TABLE NAME> COMPRESS YES ADAPTIVE

All insert, update, load, or import operations compress table data and new indexes are created with compression enabled.

To reclaim space freed by compressing data, use this command:

ALTER TABLESPACE <TBSP_NAME> REDUCE MAX

Optimize tables

After compressing tables and indexes run the REORG and RUNSTATS commands:

Offline database

REORG TABLE <TABLENAME>
REORG INDEXES ALL FOR TABLE <TABLENAME>
RUNSTATS ON TABLE <TABLENAME> WITH DISTRIBUTION ON KEY COLUMNS AND SAMPLED DETAILED INDEXES ALL

Online database

REORG TABLE <TABLENAME> INPLACE ALLOW WRITE ACCESS
RUNSTATS ON TABLE <TABLENAME> WITH DISTRIBUTION ON KEY COLUMNS AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS

Run a load test

To ensure that there is no significant performance degradation, run a load test before and after compression.

To reduce the storage space occupied by key transaction tables that have frequent read and write access, enable adaptive compression. To review the tables that offer the best benefit, calculate the estimated compression rate. Then, validate in the staging environment before you change the production database.

Summary

You have learned how Sterling Order Management can enable data compression in Db2. For more supply-chain management information, check out IBM Sterling.

Legend