How to enable index compression in DB2 V9.7

This article describes how to enable index compression in DB2 V9.7. We all know that by default, when compression is enabled for the relevant table, in general, index compression is also in the starting state. Compression of uncompressed table indexes is disabled.

We can use the compress yes option of the create index statement to change this default behavior. After creating an INDEX, you can use the alter index statement to enable or disable the INDEX compression function. Then, you must execute index reorg to recreate the INDEX.

After the index compression function is enabled, DB2 modifies the format of the index page on disk and Memory Based on the compression algorithm selected by the database manager to minimize storage space consumption. Depending on the type of the created index and the data contained in the index, the degree of compression implemented by DB2 also changes.

For example, the database manager can compress an index that contains a large number of duplicate keys by storing the record id rid of duplicate keys. In an index with a public index key prefix, the database manager can compress data based on the similarity of the index key prefix.

Index compression uses the idle cycle of the CPU or the cycle when the CPU is waiting for IO to compress and decompress the index data. Therefore, the index compression technology increases the CPU burden of the system while saving I/O costs. If our system is not subject to CPU constraints, we can feel the performance improvement brought by index compression technology when performing Select, Insert, and Update operations on data.

If the CPU of our system is busy, enabling index compression may have some negative effects.

Listing 1. Specify table compression when creating a table

 
 

  
  
db2 "create table t1 (col1 int) compress yes"   
  
  
db2 "create index idx_col1 on t1(col1) "   
  
  
db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  
  
from syscat.indexes where tabname='T1'"   
  
  
1 2   
  
  
COMPRESSION PCTPAGESSAVED   
  
  
IDX_COL1 T1 Y -1   
 
 

In the preceding statement, a table T1 is created and row compression is enabled for the table. When the index idx_col1 is created, DB2 V9.7 index compression is also automatically started because the base table is compressed. The third sentence of the above Code is to verify that the index idx_col1 does enable compression, and because no statistics are collected, the current compression ratio is-1. When we Insert or Update data to a table, the index is automatically compressed and maintained to physical storage.

If we do not specify a table for compression when creating a table, the indexes created on this table are not compressed by default. If you want to compress the indexes, perform the following steps.

 
 

  
  
db2 "create table t2 (col1 int)   
  
  
db2 "create index idx_col2 on t2(col1) "   
  
  
db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  
  
from syscat.indexes where tabname='T2'"   
  
  
1 2 COMPRESSION PCTPAGESSAVED    
  
  
IDX_COL2 T2 N   
  
  
-1   
  
  
db2 "alter index idx_col2 compress yes"   
  
  
db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  
  
from syscat.indexes where tabname='T2'"   
  
  
1 2 COMPRESSION PCTPAGESSAVED   
  
  
IDX_COL2 T2 Y -1   
 
 

When you create a table in the preceding statement, the table is not specified for compression. Indexes created subsequently are not compressed by default. If you want to enable the compression function for the index, you can use the alter statement to change it.

Note that even if we change the index to compressed, the data inserted later is not compressed until we reorg the index. DB2 considers changing the compression attribute of the index in the middle. It is necessary to maintain consistency between the inserted data before and after the change. It is impossible to have non-compressed and compressed data in the index at the same time.

Run the following script on IDX_COL2 to insert 10 thousand rows of data:

 
 

  
  
INSERT INTO t2 (col1)   
  
  
WITH TEMP (COUNTER, col1) AS   
  
  
(   
  
  
VALUES (0, INT(RAND() * 1000))   
  
  
UNION ALL   
  
  
SELECT   
  
  
(COUNTER + 1), INT(RAND() * 1000)   
  
  
FROM   
  
  
TEMP   
  
  
WHERE   
  
  
(COUNTER + 1) < 10000   
  
  
)   
  
  
SELECT   
  
  
col1   
  
  
FROM   
  
  
TEMP   
  
  
;   
 
 

Then we collect statistics on tables and indexes.

 
 

  
  
db2 "runstats on table db2admin.t2 and indexes all"   
  
  
db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  
  
from syscat.indexes where tabname='T2'"   
  
  
1 2 COMPRESSION PCTPAGESSAVED   
  
  
IDX_COL2 T2 Y 0   
 
 

We will find that the compression rate is 0, because we have not reorg the index. Of course, in addition to the preceding Select statement, we can also use the REORGCHK tool to check whether Reorg is required for the index.

 
 

  
  
db2 "reorg indexes all for table db2admin.t2"   
  
  
db2 "runstats on table db2admin.t2 and indexes all"   
  
  
db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSA   
  
  
VED from syscat.indexes where tabname='T2'"   
  
  
1 2 COMPRESSION PCTPAGESSAVED   
  
  
IDX_COL2 T2 Y 40   
  
  
TAG: db2 DB2 dB2   
 
 

The above content introduces how to enable index compression in DB2 V9.7.