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
- The database CPU, memory and disk read/writes performance had negligible (<3%) differences before and after compression.
- 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:
- Formulate a
SELECT
statement that uses theADMIN_GET_TAB_COMPRESS_INFO
table function.SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SVT_V95', 'YFS_INVENTORY_SUPPLY')) with ur;
- 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.