row compression test : 9.5 vs 9.7

mitrofun · #1 (**permalink**) 07-16-09, 08:03

Hi, All

On both DB2 version i create one compressed table (in it's own tablespace/DMS container).

After LOADding 67M rows i check what DMS-container size is :

On 9.5.3 - 12 Gb
On 9.7.0 - 12.6 Gb

Why 5% difference (600Mb) is presented on 9.7 ?
Is it some change in compression algorithm on 9.7 ?

The test table contain only VARCHAR, INTEGER and DECIMAL columns.
No other tables are presented in databases (pure test)

Also, i LOAD the same 67M rows in regular table on 9.7 and see what container size for its' tablespace is 14.9 Gb

I check view SYSIBMADM.ADMINTABCOMPRESSINFO for compressed table and see what PAGES_SAVED_PERCENT and BYTES_SAVED_PERCENT is 77%

But the real size is of tables (compressed vs noncompressed) is 12.6 Gb vs 14.9 Gb

Where is this 77% difference ?

WBR, Dmitry

db2topgun · #2 (**permalink**) 07-16-09, 12:35

This probably has something to do with the way you measure compression. If you are looking at container sizes, be sure to set tablespace to AUTORESIZE YES with very small INCREASESIZE, e.g. 5M. Allocate tablespace at initial size - make that small too. Create one table in this tablespace and load it.

stolze · #3 (**permalink**) 07-17-09, 10:11

And reorganize the tablespace to reclaim any free space.

mitrofun · #4 (**permalink**) 07-20-09, 02:09

Quote:

Originally Posted by db2topgun

This probably has something to do with the way you measure compression. If you are looking at container sizes, be sure to set tablespace to AUTORESIZE YES with very small INCREASESIZE, e.g. 5M. Allocate tablespace at initial size - make that small too. Create one table in this tablespace and load it.

Hello, db2topgun

The test tablespaces is created with AUTORESIZE YES, INCREASESIZE 10Mb and initial size of tablespace container with 100Mb

For both cases (9.5 and 9.7) this is ONE database with ONE tablespace with ONE table

Also, i check next query :

Code:

SELECT COMPRESSION, 
AVGROWSIZE, 
PCTPAGESSAVED, 
PCTROWSCOMPRESSED,
AVGROWCOMPRESSIONRATIO,
AVGCOMPRESSEDROWSIZE
FROM SYSIBM.SYSTABLES 
WHERE NAME='ODSTAB_C'
completed successfully.

COMPRESSION  AVGROWSIZE  PCTPAGESSAVED  PCTROWSCOMPRESSED  AVGROWCOMPRESSIONRATIO  AVGCOMPRESSEDROWSIZE  
-----------  ----------  -------------  -----------------  ----------------------  --------------------  
R                   183             14                  5                       1                   158

And see what PCTPAGESSAVED (14%) and PCTROWSCOMPRESSED (5%)completely different (and see as true) of results (77%) showed by ADMINTABCOMPRESSINFO view and INSPECT ROWCOMPESTIMATE command.

Why it may be so different ?