![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Table compression
You can use less disk space for your tables by taking advantage of the DB2 table compression capabilities. Compression saves disk storage space by using fewer database pages to store data. You can use compression with new and existing tables. Temporary tables are also compressed automatically, if the database manager deems it to be advantageous to do so. Row compression uses a dictionary-based compression algorithm to replace recurring strings with shorter symbols within data rows.
There are two types of row compression you can choose from:
- Classic row compression
- Adaptive compression
Data stored within data rows, including inlined LOB or XML values, can be compressed with adaptive and classic row compression. XML storage objects can be compressed using static compression. However, storage objects for long data objects stored outside table rows is not compressed.
Classic row compression, sometimes referred to as static compression, uses a table-level compression dictionary to compress data by row. The dictionary is used to map repeated byte patterns from table rows to much smaller symbols; these symbols then replace the longer byte patterns in the table rows. The compression dictionary is stored with the table data rows in the data object portions of the table.
To use row compression, you must first set the table to be compression eligible,
then you must generate the dictionary that contains the common strings from
within the table. To set the table to be eligible for compression, use either of
the following commands: create table table_name
... compress yes static or alter table table name compress yes
static.
Creating the row compression dictionary
Creating the compression dictionary allows the table to be compressed. DB2 then
needs to scan the data in the table to find the common strings it can
compress out of the table and put in the dictionary. To do this, you use the
reorg command. The first time you compress a table (or to rebuild
the compression dictionary) you must run the command reorg table table_name resetdictionary.
This will scan the table, create the dictionary, and perform the actual
table reorganization, compressing the data as it goes. From this point onward,
any insert into this table or subsequent load of data will honor the compression
dictionary and compress all new data. If in the future you want to run a normal
table reorg and not rebuild the dictionary, you can run reorg table table_name keepdictionary.
Each table has its own dictionary, meaning that a partitioned table will have a separate dictionary for each partition. This is good because it allows DB2 to adapt to changes in the data as you roll in a new partition.
To disable compression for a table, use the ALTER
TABLE statement with the
COMPRESS NO option; rows you later add are not compressed. Existing rows
remain compressed. To extract the entire table after you turn off compression,
you must perform table reorganization with the REORG TABLE command:
alter table tablename compress no
reorg table table_name
|
Adaptive compression actually uses two compression approaches. The first employs the same table-level compression dictionary used in classic row compression to compress data based on repetition within a sampling of data from the table as a whole. The second approach uses a page-level dictionary-based compression algorithm to compress data based on data repetition within each page of data. The dictionaries map repeated byte patterns to much smaller symbols; these symbols then replace the longer byte patterns in the table. The table-level compression dictionary is stored within the table object for which it is created and is used to compress data throughout the table. The page-level compression dictionary is stored with the data in the data page and is used to compression only the data within that page.
You compress table data using adaptive compression by setting the COMPRESS attribute of the table to YES
ADAPTIVE or to YES, which by default
enables adaptive compression. You can set this attribute when you create the
table by running the command create table
tablename .. compress yes adaptive or create table tablename ..
compress yes.
You can also alter an existing table to use compression by using the same options
for the ALTER TABLE statement alter table tablename compress yes adaptive or alter table tablename compress yes.
HINT: If you have scripts or applications that issue
the ALTER TABLE or CREATE TABLE
statements with the COMPRESS YES clause, make sure
you add the STATIC or ADAPTIVE keyword to explicitly indicate the table compression method
you want.
After you enable compression, operations that add data to the table, such as an
INSERT, LOAD INSERT, or IMPORT INSERT command
operation, can use adaptive compression. In addition, index
compression is enabled for the table. Indices are created as
compressed indices unless you specify otherwise and if they are the types that can be compressed.
To disable compression for a table, use the ALTER
TABLE statement with the COMPRESS NO
option; rows that you later add are not compressed. Existing rows remain
compressed. To extract the entire table after you turn off compression, you must
perform a table reorganization with the REORG TABLE
command.
Compression for temporary tables is enabled automatically with the DB2 Storage Optimization Feature. Only classic row compression is used for temporary tables. When executing queries, the DB2 optimizer considers the storage savings and the impact on query performance that compression of temporary tables offers to determine whether it is worthwhile to use compression. If it is worthwhile, compression is used automatically.
The ADMIN_GET_TAB_COMPRESS_INFO table function
estimates the compression savings that can be gained for the table, assuming a
REORG with RESETDICTIONARY option will be performed. The following example uses
the ADMIN_GET_TAB_COMPRESS_INFO table function to estimate percentage
saved for classic and adaptive compression if enabled on the
DB2INST1.CUSTOMERS table:
SELECT TABNAME, OBJECT_TYPE, ROWCOMPMODE, PCTPAGESSAVED_CURRENT current,
PCTPAGESSAVED_STATIC with_static, PCTPAGESSAVED_ADAPTIVE with_adaptive
FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('DB2INST1','CUSTOMERS')) AS T
|
Figure 8. Result for the example using ADMIN_GET_TAB_COMPRESS_INFO table function
