Concept of DB2 V9.7 index compression and correct enable index compression

This article mainly describes the new feature of DB2 V9.7 index compression, Which is used correctly. DB2 V9.1 database introduces the row compression technology. When the data volume in our system is large, when I/O requirements exceed the capacity provided by the disk system, that is, I/O becomes the bottleneck of the system), row compression can effectively reduce the number of disk reads and writes.

DB2 9.7 further proposed the index compression technology to reduce the disk space occupation of the index and reduce the number of I/O reads, thus improving the performance.

DB2 Compression not only helps reduce online database storage requirements, but also helps reduce the amount of storage required for backup and disaster recovery. In addition, because the data volume involved in the backup and recovery process becomes smaller, the backup and recovery operations will take a shorter time. All of these factors have virtually reduced IT costs.

Introduction

Objects that occupy physical storage space in the database are mainly tables and indexes. The size of these two types of objects directly affects the disk space usage and also determines the database performance. In the current database system, tables will become larger and larger over time, and the corresponding indexes will become larger and larger. This is also the reason why our system is getting slower and slower.

DB2 V9.1 introduces the new row-depth compression deep compression technology to address this challenge. Although the main purpose of deep compression is to save storage space, it can also greatly save disk I/O and increase the buffer pool hit rate. Therefore, the performance can be improved without the cost-data compression and decompression require additional CPU cycles. The storage savings and performance impact of deep compression are related to data, database design, database tuning, and application load.

In DB2 V9.7, IBM puts forward DB2 V9.7 index Compression Based on Row compression, which aims to reduce disk space usage like row compression, this applies to both large OLTP and data warehouse environments. DB2 V9.7 uses multiple compression algorithms to automatically compress indexes. This article will not discuss specific compression algorithms, but will focus on the application scenarios of index compression, that is, how to start index compression and what data distribution is suitable for index compression, which data is not suitable for index compression.

How to enable index compression

By default, index compression is also enabled after the table is compressed. For unzipped table INDEX compression disabled, you 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 DB2 V9.7 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   
 
 

The above content is an introduction to the use of the new features of DB2 V9.7 index compression. I hope you will gain some benefits.

For details, visit:

Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0907luohq2/