How to enable index compression in DB2 V9.7

Source: Internet
Author: User

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

 
 
  1. db2 "create table t1 (col1 int) compress yes"   
  2. db2 "create index idx_col1 on t1(col1) "   
  3. db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  4. from syscat.indexes where tabname='T1'"   
  5. 1 2   
  6. COMPRESSION PCTPAGESSAVED   
  7. 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.

 
 
  1. db2 "create table t2 (col1 int)   
  2. db2 "create index idx_col2 on t2(col1) "   
  3. db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  4. from syscat.indexes where tabname='T2'"   
  5. 1 2 COMPRESSION PCTPAGESSAVED    
  6. IDX_COL2 T2 N   
  7. -1   
  8. db2 "alter index idx_col2 compress yes"   
  9. db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  10. from syscat.indexes where tabname='T2'"   
  11. 1 2 COMPRESSION PCTPAGESSAVED   
  12. 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:

 
 
  1. INSERT INTO t2 (col1)   
  2. WITH TEMP (COUNTER, col1) AS   
  3. (   
  4. VALUES (0, INT(RAND() * 1000))   
  5. UNION ALL   
  6. SELECT   
  7. (COUNTER + 1), INT(RAND() * 1000)   
  8. FROM   
  9. TEMP   
  10. WHERE   
  11. (COUNTER + 1) < 10000   
  12. )   
  13. SELECT   
  14. col1   
  15. FROM   
  16. TEMP   
  17. ;   

Then we collect statistics on tables and indexes.

 
 
  1. db2 "runstats on table db2admin.t2 and indexes all"   
  2. db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED   
  3. from syscat.indexes where tabname='T2'"   
  4. 1 2 COMPRESSION PCTPAGESSAVED   
  5. 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.

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

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.