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.

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.

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;
   

   Show more
2. Execute the SELECT statement.

    SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(‘OMSDB’,’’)
   

   Show more

A report similar to the following is displayed.

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.