Informix – Compress data rows in a table

Given (IDS 11.50),
Database : testdb
table : cust_comp
owner : hello

(1) Before Compression

$ oncheck -pt testdb:hello.cust_comp

TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps

Number of pages allocated 90209
Number of pages used 87877
Number of data pages 86743
Number of rows 2949120

(2) Setup Compression, Repacking & Shrinking (unsed space)

Note: DBSA “informix” id access is required to perform following steps.

$ dbaccess sysadmin –

Database selected.

> EXECUTE FUNCTION task(“enable compression”) ;
(expression) Enable compression succeeded

> EXECUTE FUNCTION task(“table estimate_compression”,”cust_comp”,”testdb”,”hello”) ;
(expression) est curr change partnum table
—– —– —— ———- ———————————–
81.0% 0.0% +81.0 0x011008cc testdb:hello.cust_comp

Succeeded: table estimate_compression testdb:hello.cust_comp

> EXECUTE FUNCTION task(“table create_dictionary”, “cust_comp”,”testdb”,”hello”);
(expression) Succeeded: table create_dictionary testdb:hello.cust_comp

> EXECUTE FUNCTION task(“table compress repack shrink”, “cust_comp”,”testdb”,”hello”);
(expression) Succeeded: table compress repack shrink testdb:hello.cust_comp

(3) After Compression

$ oncheck -pt testdb:hello.cust_comp

TBLspace Flags 48000801 Page Locking
TBLspace use 4 bit bit-maps
TBLspace is compressed

Number of pages allocated 16481
Number of pages used 16481
Number of data pages 16476
Number of rows 2949120

Note: Allocated pages is 18.26% (16481 * 100 / 90209) of the original size. It saves 81.74% spaces.

(4) Uncompress data rows in table:

$ dbaccess sysadmin –
> execute function task(‘table uncompress’,’cust_comp’,’testdb’) ;

Tagged Under : , , ,

Make a Comment