DB2 Version 9.7 for Linux, UNIX, and Windows

Creating a table that uses compression

When you create a new table, you can use the COMPRESS attribute for the CREATE TABLE command to enable compression.

About this task

You must decide whether you want to use row compression only, value compression only, or both types of compression. Row compression will almost always yield benefits in terms of storage savings, as it attempts to replace data patterns that span multiple columns within a row with shorter symbol strings. Value compression can offer savings when you have a many rows with columns that contain the same value, or when you have columns that contain the default value for the data type of the column. When value compression is enabled, you can also specify that columns that assume the system default value for their data types can be further compressed with the COMPRESS SYSTEM DEFAULT option.

Any indexes created for compressed tables will, by default, also be compressed.

Restrictions

If you attempt to apply compression to columns that contain system default values using the COMPRESS SYSTEM DEFAULT clause, you must also specify VALUE COMPRESSION. Otherwise, a warning is returned, and system default values are not stored using minimal space.

If you are planning to enable value compression, be aware that the row size can, in some cases, grow as result of the overhead imposed by the database manager in dealing with certain data types. You can determine the impact that value compression has on row size using the information provided about this option in the documentation for the CREATE TABLE statement.

Procedure

  1. Formulate a CREATE TABLE statement.
    • If you want to use row compression, include the COMPRESS YES clause.
    • If you want to use value compression, include the VALUE COMPRESSION clause. If you want to compress system default values, include the COMPRESS SYSTEM DEFAULT clause.
  2. Run the CREATE table statement.

Results

After the table has been created, all data subsequently added to the table will be compressed. Any indexes associated with the table will also be compressed, unless you explicitly specify that they not be.

Example

Example 1: Creating a table for customer information with row compression enabled.

     CREATE TABLE CUSTOMER
          (CUSTOMERNUM      INTEGER,
           CUSTOMERNAME     VARCHAR(80),
           ADDRESS          VARCHAR(200),
           CITY             VARCHAR(50),
           COUNTRY          VARCHAR(50),
           CODE             VARCHAR(15),
           CUSTOMERNUMDIM   INTEGER)
           COMPRESS YES;

Example 2: Creating a table for employee salaries where a default of 0 is assumed for the salary field with row and system default compression enabled for the SALARY field.

     CREATE TABLE EMPLOYEE_SALARY
          (DEPTNO   CHAR(3)      NOT NULL,
           DEPTNAME VARCHAR(36)  NOT NULL,
           EMPNO    CHAR(6)      NOT NULL,
           SALARY   DECIMAL(9,2) NOT NULL WITH DEFAULT COMPRESS SYSTEM DEFAULT)
           COMPRESS YES;
Note, however, that the VALUE COMPRESSION clause has been omitted from this command. This command will create a table called EMPLOYEE_SALARY, however, a warning message is returned:
SQL20140W  COMPRESS column attribute ignored because VALUE COMPRESSION is 
deactivated for the table.  SQLSTATE=01648
In this case, COMPRESS SYSTEM DEFAULT will not actually be applied to the SALARY column.

Example 3: Creating a table for employee salaries where a default of 0 is assumed for the salary field with row and system default compression enabled for the SALARY field..

     CREATE TABLE EMPLOYEE_SALARY
          (DEPTNO   CHAR(3)      NOT NULL,
           DEPTNAME VARCHAR(36)  NOT NULL,
           EMPNO    CHAR(6)      NOT NULL,
           SALARY   DECIMAL(9,2) NOT NULL WITH DEFAULT COMPRESS SYSTEM DEFAULT)
           VALUE COMPRESSION COMPRESS YES;

In this example, VALUE COMPRESSION is included in the statement, which will allow the default value for the SALARY field to be compressed.

Parent topic: Row compression