How To
Summary
Column compression can significantly reduce the storage requirements of tables by compressing data in individual columns. This feature is useful for tables with large data volumes, especially for character large object (CLOB) column types where the string length exceeds 500 bytes.
Steps
To enable column compression in OMS, we can use properties such as db.force.compression.<table name>.columns. For more information, refer to https://www.ibm.com/docs/en/order-management-sw/10.0?topic=yfsproperties-order-management-file
For checking whether compression is enabled for a particular column, we can run the following query:
SELECT COUNT(1) COMP_REC_COUNT, MAX(LENGTH(COLUMN_NAME)) MAX_MSG_BYTES, MIN(MODIFYTS) COMPRESSED_FROM, MAX(MODIFYTS) COMPRESSED_TILL FROM TABLE_NAME WHERE COLUMN_NAME LIKE '|C0|%';
Example :-
SELECT COUNT(1) COMP_REC_COUNT, MAX(LENGTH(MESSAGE)) MAX_MSG_BYTES, MIN(MODIFYTS) COMPRESSED_FROM, MAX(MODIFYTS) COMPRESSED_TILL FROM OMDB.YFS_EXPORT WHERE MESSAGE LIKE '|C0|%';
This query provides information on the number of rows where the data in the respective column is compressed, along with the maximum size of the compressed column. If the query returns zero records, it indicates that either compression is not enabled or the length of strings in the column never exceeds 500 bytes.
2) DB2 Query to retrieve the count of records that are not compressed and have a size greater than 500 bytes:
SELECT COUNT(1) NON_COMPRESSED_REC_COUNT, MAX(LENGTH(COLUMN_NAME)) MAX_MSG_BYTES, MIN(MODIFYTS) NOT_COMPRESSED_FROM, MAX(MODIFYTS) NOT_COMPRESSED_TILL FROM TABLE_NAME WHERE COLUMN_NAME NOT LIKE '|C0|%' AND LENGTH(COLUMN_NAME) > 500;
Example
SELECT COUNT(1) NON_COMPRESSED_REC_COUNT, MAX(LENGTH(MESSAGE)) MAX_MSG_BYTES, MIN(MODIFYTS) NOT_COMPRESSED_FROM, MAX(MODIFYTS) NOT_COMPRESSED_TILL FROM OMDB.YFS_EXPORT WHERE MESSAGE NOT LIKE '|C0|%' AND LENGTH(MESSAGE) > 500;
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
08 May 2023
UID
ibm16982835