The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

Shrink your database using DB2 9 for Linux, UNIX, and Windows row compression

New capabilities for reducing your disk storage in DB2 9

Paul Read (paul_read@uk.ibm.com), Product Introduction Manager, EMC

Paul Read is a Product Introduction Manager in the Global BetaWorks team based in the IBM Lab in Hursley, Hampshire, United Kingdom. He is responsible for Beta and Early Support Programs for Information Management products on Linux, UNIX and Windows.

Michel Steinhauer, Software Engineer, EMC

Michel Steinhauer is a Software Engineer based in IBM Belgium. He is responsible for Technical Support for Information Management products in Belgium on Linux, UNIX, and Windows.

Summary: IBM® DB2® 9 for Linux®, UNIX®, and Windows® includes a new dictionary-based row compression feature you can use to compress data objects. When you compress data, you achieve disk storage space savings by representing the same data using fewer database pages. Large tables with rows that contain repetitive patterns will benefit from this feature. Familiarize yourself with this new feature with a series of exercises in this tutorial. Gain an understanding of the benefits this feature, and implement row compression in your own environment.

Date: 02 Dec 2006
Level: Intermediate
PDF: A4 and Letter (68 KB | 19 pages)Get Adobe® Reader®

Activity: 7917 views
Comments:

Before you start

About this tutorial

Data row compression replaces common byte patterns in a data row with shorter symbol strings. The storage savings are greater than the savings provided with value compression. However, there is an added cost in implementing row compression as well as processing costs associated with every time a row must be compressed or decompressed. Data row compression in DB2 9 is based on the Lempel-Ziv ( LZ ) algorithm. The LZ algorithm uses a static dictionary to store the needed information. Details about the algorithm can be found at Data-compression.com. The dictionary is stored in the table itself, implying a small amount of overhead (approximately 74KB). DB2 9 does not compress rows when no storage saving can be realized for those rows.

Further description of the feature can be found in the article "Data compression in DB2 9" (developerWorks, May 2006).

This tutorial takes you through a series of exercises to familiarize you with this new dictionary-based row compression feature. The tutorial is intended for DB2 technical specialists, database administrators, and programmers. You should have a good understanding of DB2 on Linux, UNIX, and Windows (hereafter called DB2 LUW). You should also be familiar with the DB2 Command Window and running DB2 LUW scripts.

Objectives

This tutorial will help familiarize you with concepts and capabilities of data compression in DB2 9. In these exercises you will learn how to:

Enable/disable row compression
Use the new option in Tablespace creation to enable more rows in a page
Use various SQL queries and tools to analyze the benefits of compression

Prerequisites

This tutorial is written for Linux or UNIX programmers whose skills and experience are at a beginning to intermediate level. You should have a general familiarity with using a UNIX command-line shell and a working knowledge of the C language.

System requirements

To run the examples in this tutorial, you will need the following:

DB2 9 Data Server
Microsoft Windows 2000 or later and an account with administrator privileges, or Linux (Validated edition) with root access
Java Runtime Environment 1.4.2 or later

Refer to the DB2 9 system requirements page to ensure that your hardware meets the requirements.

DB2 9 Express-C is available from the above link. See the tutorial "DB2 XML Evaluation Guide" (developerWorks, June 2006) for steps on installing DB2. Unless the DB2 configuration is altered, then DB2 will automatically start after installation.

Use the sample scripts and data provided in the compression.zip file (see Downloads) to demonstrate the concepts in this tutorial. Extract the contents into a subdirectory called DB2compression ( C:\DB2compression or home/userid/DB2compression). This directory will be referred to simply as DB2compression throughout the tutorial. This tutorial assumes that you have used the default directories for the DB2 installation and all the DB2 exercises are performed by a database administrator id.

Comments